Using Date Diff In Where Of Query

[GC2010]

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?

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

 

[theDBguy]

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

 

[GC2010]

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]

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]

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" 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]

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]

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]

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

 

[GC2010]

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:

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

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

Then add a WHERE clause onto that to say

<=DateValue(CStr(Now()))

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

 

[pbaldy]

This is jumping through hoops:

<=DateValue(CStr(Now()))

Try just

<=Date()