format a date in sql statement 1

[Lhuffst]

Hi All. I have a search utility that returns several fields, one of which is a date. The code works properly but I want to format the date so it shows the 2 digit day and month and a 4 digit year so I can line up the columns in the list box with headers.

I get an error when I try to format and understand that it thinks I have ended the line before it is actually ended but I don't know how to fix the line so it gets all of the statement and rid of the error [red]expected end of statement[/red]. I'm hoping someone can see what I need to do. The code is:

ssql = "Select DistinctRow PermitNo,Meterid,  Format([ReturnDate],"mm/dd/yyyy") , RcvdBy,PartsText,DaysLate " & _
                    "From MeterInfo " & _
                    "Where not isBlank (PermitNo) " & _
                    "Order by PartsText"

if I leave out the format portion, the sql works fine but returns 6/1/2009 and I need 06/01/2009.
thanks
Lhuffst

 

[PHV]

Use this:
[tt]Format([ReturnDate],'mm/dd/yyyy')[/tt]
ofr this:
[tt]Format([ReturnDate],""mm/dd/yyyy"")[/tt]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Lhuffst]

The second one worked like a champ. thanks much