469,159 Members | 1,476 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,159 developers. It's quick & easy.

Date Format query in Access for SQL database


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.

Oct 5 '11 #1
6 4667
32,167 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 5 '11 #2
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,
Oct 6 '11 #3
931 Expert 512MB
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:

Expand|Select|Wrap|Line Numbers
  1. Nz(Format([LAST_TRANS],"dd/mm/yyyy"), "")

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

Oct 6 '11 #4
32,167 Expert Mod 16PB
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 6 '11 #5
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.

Oct 6 '11 #6
931 Expert 512MB
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.
Oct 6 '11 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Savvas Tsitouridis | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.