CDATE(FORMAT [ ],'Short Date"0 problem

[DajOne]

In order to use the 'Between' criteria in a query, I had to change the long format of a date field into a short one in order to capture the last day of the query with

Input Date: CDate(Format([EB_Query_Two]![DATE ENTERED],"Short Date")) This comes from a very helpfull post in this forum...

It works well up to the point that I have to omit the zero "0" for the Jan to Sept months in order for the query to work. If I put the zero, I get no results.

The format is MM/DD/YY... example.. we would usually enter 06/30/2004 for June 30th 2004 but I have to enter 6/30/2004 for the query to work..

Any idea ?

Thanks

 

[PHV]

Something like this ?
Input Date: "#" & Format([EB_Query_Two]![DATE ENTERED], "m/dd/yyyy") & "#"

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

 

[DajOne]

GOod try but did not work.. Still comes out with no records.. But thanks for the idea.. It will be usefull for me on other things!!!

 

[SkipVought]

DajOne,

Did you notice that PHV's solution does NOT include the CDate conversion function for the Input Date???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[DajOne]

I am actually looking into this 'after the fact' in the help files and different sties to better understand why or if it would help..

WIll let you know..

Thanks!!!

 

[DajOne]

Well, I'm stymie..

Tried several combinations with CDATE and mm/dd/yyyy combinations to no avail..

Guess the users will have to learn not to put the 0 in front of the month!!!

Thanks for the ideas... will be usefull!!