By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,790 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

How to change Date Format in query function?

yosiro
P: 34
Here's my code in query design (column FIELD)
Expand|Select|Wrap|Line Numbers
  1. Date of Birt: 
  2. IIf(IsNull([Place of Birth]);
  3. IIf(IsNull([Date of Birth]);
  4. [Date of Birth]);
  5. IIf(IsNull([Date of Birth]);
  6. [Place of Birth];[Place of Birth] & ", " & [Date of Birth]))
The result is "Alabama, 2/12/2000"

I want to change the date with long date format so the result must said "Alabama, February 12, 2000"

I have change all tables with long date format but it isn't work.

So what should i do?
Sep 6 '12 #1

✓ answered by zmbd

Format([dateofbirth],"mmmm dd, yyyy")
http://office.microsoft.com/en-us/ac...001099015.aspx

Also... commas (,) not semicolon (;)
SQL interprets semicolons as the end of the statement; thus, at best error or return nothing which IMHO is harder to troubleshoot because one doesn't know if there is a criteria mismatch or an error.

-z

Share this Question
Share on Google+
3 Replies


zmbd
Expert Mod 5K+
P: 5,287
Format([dateofbirth],"mmmm dd, yyyy")
http://office.microsoft.com/en-us/ac...001099015.aspx

Also... commas (,) not semicolon (;)
SQL interprets semicolons as the end of the statement; thus, at best error or return nothing which IMHO is harder to troubleshoot because one doesn't know if there is a criteria mismatch or an error.

-z
Sep 6 '12 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
@Yosiro
As I understand your logic, a simpler appraoch would be to write it as:
Expand|Select|Wrap|Line Numbers
  1. BirthInfo:([Place of Birth] + ", ") & format([Date of Birth],"mmmm dd, yyyy")
Notice the use of + ", " instead of & ", "
To understand the difference see below example (Where Place of birth is assumed Null
Expand|Select|Wrap|Line Numbers
  1. [Place of Birth] + ", ")=Null
  2. [Place of Birth] &  ", ")=", "
The format on a null value SHOULD return an empty string, so your safe on that part too. Give it a try. Be aware though that the + operator works differently on strings and null then it does on numerical values. It can take a bit of work to get used to it.


@zmbd
Whether or not you should use , or ; depends on your regional settings, AND to make it more fun, it is not consistent between VBA SQL strings and the Access query designer. For example the access query designer requires me to write Format(Date;"yyyy-mm-dd") whereas to use the same in VBA I need to use ",".
I do believe that access stores the query from query designer with "," but I suppose it doesn't really make much of a difference to the developer.
Sep 6 '12 #3

zmbd
Expert Mod 5K+
P: 5,287
TheSmileyCoder

I was not aware of the the regional differences for the "," vs. ";"
How fun of MS to include such a neat feature - OHHH and Better yet... not mention it this in any of the online files... (please hear a TON of sarcasm... against MS of course :) )

Thankfully for me, the VBA and SQL use the same format
(punny... format... chuckle)

--
As for the "+" vs. "IIF" for the null... although this works a vast majority of the time - I have had errors pop-up when using this approach both with-in VBA and the query builder. I don't currently have one of these or I'd post the code... and to make things more exciting... the error was somewhat random... who knows, it was a year or more ago and on v2003.

-z
Sep 6 '12 #4

Post your reply

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