Date format 1

[zimmer9]

I created the following in the qbe grid for a criteria and it works fine. How would I modify the 3rd parameter so that
Cdate returns 12/31/2004 instead of it's present return value of 12/31/2005.

for ex: assume the field Stocks = 3 in the following function:

?DateAdd("yyyy",Stocks*-1,CDate("12/31/" & Year(Now())))
12/31/2002

My problem is that if I modify the Cdate function to subtract -1 from the Now() function, it works fine in the Immediate Winow as follows:

?DateAdd("yyyy",Stocks*-1,CDate("12/31/" & Year(Now())-1))
12/31/2001

However, this criteria is compared to a field named "Date Lost" in the qbe grid. The Date Lost field is defined in a table with a Data Type of Date/Time.

I run the query and get a "Data type mismatch in criteria expression".

 

[PHV]

Have you tried the DateSerial function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DoubleD]

If Stocks = 3 then this calculation, with the added parenthesis, should evaluate to 12/31/2001.

?DateAdd("yyyy",Stocks*-1,CDate("12/31/" & (Year(Now())-1)))



I am what I am based on the decisions I have made.

DoubleD

 

[VBslammer]

Convert the whole thing to a Date:
[tt][Date Lost] < CDate(DateAdd("yyyy",[Stocks]*-1,"12/31/" & (Year(Now)-1)))[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[PHV]

And this ?
< DateSerial(Year(Now)-1-[Stocks], 12, 31)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244