Date Format query in Access for SQL database

[Paul Swanton]

Hi,

I'm currently attempting to create a query in Access 2003 on a SQL database where I'm trying to format a date column, that's currently set as YYYYMMDD, to show as DD/MM/YYYY. Using that expression I want to retrieve data for all NULL records and any older than 90 days. The current Expression reformats the date column correctly but shows all NULL values as '#Error'. Adding the criteria '<Date()-90 or Is Null' only results in a 'Data type mismatch in criteria expression' error message.

The expression command is currently scripted as 'LASTTRANS: Format(CDate(Format([LAST_TRANS],"0000-00-00")),"dd/mm/yyyy")' in the Field row of the query builder.

I've tried various Date Format commands but so far have been unsuccesful. Any help anyone can offer would be greatly received.

Thanks,
Paul.

[NeoPa]

You're doing some strange stuff in there to be fair Paul. Strange even disregarding any difference between SQL Server and Access. What you have would not be expected to work with a DateTime type field. Try to treat it simply as that and see what you get first :

LASTTRANS: Format([LAST_TRANS],"dd/mm/yyyy")

Remember though, converting the result to a string (as you do here) rather than simply formatting the Date data as a string, is very rarely a good idea or necessary. More sensible in nearly all circumstances would be simply to set the format of the field to that format string while leaving the underlying data as a Date.

[Paul Swanton]

Hello NeoPa,

Thank you for your response. I had tried that command but all records just come back as '#Error' unfortunately. I know, it does seem unnecessarily complicated but it's to be used as part of a quartely systems maintenance procedure, so the idea is that it can be used whenever necessary without needing to be edited. I can only imagine the error is being caused because it's not recognised as a date column, so attempting to reformat is just confusing it? Which is why I think specifying the 0000-00-00 is working for the values but is erroring on the NULLS?

Anyway, I'll keep attempting to script it as desired but I may have to concede defeat and work around it.

Thank you,
Paul.

[patjones]

I'm confused as to whether the problem exists for all records or just null-valued date fields. If it's just for null-valued date fields then it could be handled by using something like the Nz( ) function:

Nz(Format([LAST_TRANS],"dd/mm/yyyy"), "")

This will simply give you a zero-length string in the event that LAST_TRANS is null.

Pat

[NeoPa]

I don't have enough info from you to determine what state the data comes across to you in so as to determine the best approach on that score, but if it's only the Null values causing a problem, and otherwise your original code works, then try :

LASTTRANS: Format(CDate(Format(Nz([LAST_TRANS], XXX),"0000-00-00")),"dd/mm/yyyy")

where XXX reflects what you want to be used in the case where the value is Null.

[Paul Swanton]

Thanks guys. It turns out that the NULL values aren't null at all, but a four blank spaces. So that's relieved some of the confusion and I'm able to work with the data and format it to be recognised as a date. The command I'm now using is 'LASTTRANS: IIf(([LAST_TRANS] Like " *"),"NULL",(CDate(Format([LAST_TRANS],"0000-00-00"))))' which appears to be working.

Thanks for your help.

Paul.

[patjones]

Yes, that information certainly changed the whole landscape of the situation. I'm happy that you found that out and were able to rectify it.