Using Date Diff In Where Of Query (1 Viewer)

GC2010 · Jul 5, 2019

I have a short text field that I am parsing out the month from. I then want to use it as part of my where clause

I have this syntax but am getting data type mismatch...but am a i not converting it to date format so the DateDiff function should work?

Code:

Select * from teamroster 
where DateDiff(‘m’, CDate(Mid([action date],InStrRev([action date], ‘ ‘) + 1)), Now()) > 7;

theDBguy · Jul 5, 2019

What is in [action date]? Is it a date?

GC2010 · Jul 5, 2019

theDBguy said:
What is in [action date]? Is it a date?

No. Action date is short text that contains for example:

Need forms Jul

The one factor that is always the same is the last 3 characters is always the date.

GC2010 · Jul 5, 2019

In case I’m going about this the wrong way. I want to return all records where the month is current month or prior

pbaldy · Jul 5, 2019

GC2010 said:
the last 3 characters is always the date.

"Jul" is not a date, it's a month. CDate() can't convert it to a date, as you also need day and year to make a complete date. Do you have multiple year's data? How would you tell July 2018 from 2019?

GC2010 · Jul 5, 2019

pbaldy said:
"Jul" is not a date, it's a month. CDate() can't convert it to a date, as you also need day and year to make a complete date. Do you have multiple year's data? How would you tell July 2018 from 2019?

The year is stored in a separate field, called year. Lol

Yeah...the dev of this SharePoint site didn’t set it up all to well imho

pbaldy · Jul 5, 2019

I'll say. How do you name a field "ActionDate" that contains "Need forms Jul". Well, this works:

?cdate("jul 1, 2019")
7/1/2019

so you could build a string with the result of your Mid() function concatenated with ", 1," and the value from your year field.

GC2010 · Jul 6, 2019

pbaldy said:
I'll say. How do you name a field "ActionDate" that contains "Need forms Jul". Well, this works:

?cdate("jul 1, 2019")
7/1/2019

so you could build a string with the result of your Mid() function concatenated with ", 1," and the value from your year field.[/QUOTE

Does it matter if I use 1 or 01?

pbaldy · Jul 6, 2019

I doubt it, but it would be pretty easy to test.

GC2010 · Jul 6, 2019

pbaldy said:
I doubt it, but it would be pretty easy to test.

Now that I've solved my issue, I'll post as to why I was asking :banghead:

I originally thought you were telling me to build my date string in the DateDiff function like this:

Code:

Select * from teamroster 
where DateDiff(‘m’, CDate(Mid([action date],InStrRev([action date], ‘ ‘) + 1) & "/01/" & [Year]), Now()) > 7;

but that was still giving me skewed results. Then I realized I was just being stupid

And I needed to create an Alias in the query to do

Code:

Dates: DateDiff(‘m’, CDate(Mid([action date],InStrRev([action date], ‘ ‘) + 1) & "/01/" & [Year]), Now())

Then add a WHERE clause onto that to say

Code:

<=DateValue(CStr(Now()))

And of course now I'm getting my desired results.

pbaldy · Jul 6, 2019

This is jumping through hoops:

<=DateValue(CStr(Now()))

Try just

<=Date()

Using Date Diff In Where Of Query (1 Viewer)

GC2010

Registered User.

theDBguy

I’m here to help

GC2010

Registered User.

GC2010

Registered User.

pbaldy

Wino Moderator

GC2010

Registered User.

pbaldy

Wino Moderator

GC2010

Registered User.

pbaldy

Wino Moderator

GC2010

Registered User.

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread