Using If Then statement with dates

[cyoung]

Hi,
I'm hoping someone can help me. I'm trying to do an IF Then statement on dates. So if the Date received is before the effective date, Yes and if not, No. I can't get it to work. The Received date has been converted to a text format.
Originally it looked like this: 20110518, but I have a convert statement on it so that it reads as text: 05/18/2011

 

[raskew]

Hi -

Welcome to the forum!

With the information you've provided, you'll need to use the Iif() function, and the Datevalue() function, which will convert your text date to a true date. Suggest you look both of them up in the help file.

Here are a couple of examples from the debug (immediate) window.

**********************************************************

recdte = "5/18/2011"
efdte = #5/31/11#
? iif(datevalue(recdte)> efdte, "Yes", "No")
No

efdte = #5/17/11#
? iif(datevalue(recdte)> efdte, "Yes", "No")
Yes

**********************************************************

The one thing you haven't accounted for is the situation where the date received and the effective date are the same.

Hope that helps.

Best wishes,

Bob

 

[cyoung]

Thanks Bob, you're right I do need to account for those situations where the received date = to effective date. Also, I tried that statement, but now get Data mismatch expression error. Any thoughts?

 

[raskew]

Hi -

First, I'd suggest you try the debug window info in my original post. It should work (it worked for me).

Next, evaluate the data type of your effective date. Where's that coming from? Is it a true date or another text date? If that's the case, you'll need to use the DateValue() function, as previously described.

If none of this is helpful, please post back with more info, i.e. examples that do and don't work.

Best wishes,

Bob