CompTech810
asked on
Compare Dates
Hi Experts:
The problem that I am running into is: I'm comparing two dates and if the date field (NEWWE) is less than PASTWE date plus thirteen weeks it should display the NEWWE date else display zero. It is working for the most part but a NEWWE date of 09/18/2011 < 12/11/2011 is returning a value of 0 when it should return the NEWWE value. The following is the Iif statement I'm using.
IIf([NewWe]<DateAdd("ww",1 3,[PASTWE] ),[NewWe], 0)
What am I doing wrong? Thanks!
The problem that I am running into is: I'm comparing two dates and if the date field (NEWWE) is less than PASTWE date plus thirteen weeks it should display the NEWWE date else display zero. It is working for the most part but a NEWWE date of 09/18/2011 < 12/11/2011 is returning a value of 0 when it should return the NEWWE value. The following is the Iif statement I'm using.
IIf([NewWe]<DateAdd("ww",1
What am I doing wrong? Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes. A date with the value of 0 is that. You can, however, simply remove the last CDate. You only need it to compare. Afterwards, you can return it as a string. If you need the value as a date later, you can always use CDate then.
ASKER
Great that worked.
I do have an additional question. In the criteria of the query that we just did I only want to display dates that are greater than zero. When I use the criteria to only include records greater than zero it prompts me for NEWWE.
>0
I do have an additional question. In the criteria of the query that we just did I only want to display dates that are greater than zero. When I use the criteria to only include records greater than zero it prompts me for NEWWE.
>0
Well, without looking at your file, it's hard to know what went wrong. You can have a look at the SQL to see if anything is amiss. Without knowing what you did, I can't really help you. But if you just add:
>0
in the criteria field, I don't see a reason why it shouldn't work.
>0
in the criteria field, I don't see a reason why it shouldn't work.
ASKER
I'm not sure either. Is there a way to run debug to check what access is doing?
ASKER
I submitted a question with the criteria issue. I have tried everything I can think of.
Thanks for your help!
Thanks for your help!
Glad to help. As for the debug part, you can't really debug SQL. What you can do is check the SQL query (I assume you were doing this in the graphic structure window). Then you, or someone here, can assist you with it.
ASKER
Thanks!!
ASKER