[b]Formatting query field as a Date field[/b]

[btalon]

I have a query that I want to take another date field (Order_Overall_Start_Date) and create a new field (DisplayStart) formatting it as a Date field with the "mm/dd/yy" formatting (the current formatting is dd-Jan-yy). Here is the string in the query:

DisplayStart: IIf([Order_Overall_Start_Date]<Date()," ",Format([Order_Overall_Start_Date],"mm/dd/yy"))

The problem is that it doesn't sort it correctly. It sorts based on the first number "mm" then "dd" then "yy". So 12/01/04 will come after 01/01/05. It's like its looking at the field as a text field and not a date field.

 

[GingerR]

try putting cdate(Format.....) to force a conversion to date type.

 

[lespaul]

I'm pretty sure that the FORMAT function returns a string, if you want it to be a date, you'll probably have to convert it:

DisplayStart: IIf([Order_Overall_Start_Date]<Date()," ",CDat(Format([Order_Overall_Start_Date],"mm/dd/yy")))





Leslie

 

[btalon]

Access 97 doesn't like CDat in the SQL statement (Undefined Function).

 

[btalon]

Now CDate() runs, but it still doesn't sort it correctly. Now it's sorting like 1/01/05, 12/01/04, 2/1/05, dropping the leading zero.

 

[GingerR]

I guess cause part of your statement is setting the calculated control to a string (" "). Try this:

CDate(Format(IIf([Order_Overall_Start_Date]>Date()," ",[Order_Overall_Start_Date]),"mm/dd/yy"))

OR

if the function is true, set it to NULL instead of to " ".

 

[btalon]

This works, sorted correctly and everything, I just got rid of the Format. However, the problem now is the zero. It gives me an invalid use of Null if I use Null and I can't use the "" because of Data Type Mismatch. The 0 translates to 12:00:00 AM when the query is run. I guess I'll have to program the detail section of the report not to show the textbox if the value = 12:00:00 AM, unless anyone else comes up with a better solution.

DisplayStart: CDate(IIf([Order_Overall_Start_Date]<Date(),0,[Order_Overall_Start_Date]))

Thanks for your help.

 

[GingerR]

oops sorry i didn't test for dates that met the criteria.

IT works for me with null, using cdate(format...

IIf([DateField]>Date(),Null,CDate(Format([DateField],"mm/dd/yy")))

 

[btalon]

But how does it sort it, specifically going from 11/01/04 to 3/31/05, that was the problem.

 

[GingerR]

it sorted properly, putting blanks first, then dates in ascending order.

you can always put the raw date field in the query grid and sort ascending on that instead.

This is my data:

DateField

01-Jan-04
12-Dec-03
01-Jan-06
31-Mar-05
01-Nov-04

{blank}
{blank}
{blank}
3/31/2005
1/1/2006

 

[btalon]

Are you creating a new field? I have no problem with a current date field, the issue is creating a new field that is taking information out of a date field and formatting and sorting it correctly.

 

[lespaul]

why are you putting that in a new field instead of just doing it in the query when you need it?

Leslie

 

[GingerR]

yes it's a new calculated field in a query.
it is not a field in a table. it's in a query.

 

[lespaul]

btalon, why don't you post your SQL, that will help us to help you!


Leslie