If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Date format in update query
Hi
How can I ensure that date is always assigned in dd/mm/yyyy format to a date filed, in an update query? Thanks Regards |
#2
|
|||
|
|||
Date format in update query
John, Access does not store dates in a specific format. The format is
applied at display time, and depends on the user's Regional Settings (in the Windows Control Panel.) So perhaps your question is, "How can I ensure that Access understands the dates in the query, and stores the correctly when the Update query executes?" I also live in a d/m/y country, and in 15 years of working with Access have found 3 cases where Access is likely to misunderstand our dates. Details in: International Date Formats in Access at: http://allenbrowne.com/ser-36.html If the dates are calculated ones, typecasting them in the query can also help: Calculated fields misinterpreted at: http://allenbrowne.com/ser-45.html -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "John" wrote in message ... How can I ensure that date is always assigned in dd/mm/yyyy format to a date filed, in an update query? Thanks Regards |
#3
|
|||
|
|||
Date format in update query
Allen I found your ser-36 article to be extremely useful. I written my own SQLDate() wrapper function, but apparently mine contained two errors that your function does not. I am baffled why the function appears to have worked properly for the (at least) three years that I've been using it. Can you offer any insight why it continued to work with these two errors? Was I just lucky...?? 1) I did not know about the "\/" format trick, and my output used the "-" that you mention 2) My output uses "yyyy/mm/dd" format, and as far as I can tell, it always selects the correct data. On tweak that my function includes is an optional boolean parameter whether or not to include the time portion of the date in its output. With a default value of False, the normal calls to the function are identical to yours. Simply adding "TRUE" after the passed value causes the time to be included in the formatted output string. I find it handy in many situations. On Fri, 23 Jun 2006 11:37:20 +0800, "Allen Browne" wrote: John, Access does not store dates in a specific format. The format is applied at display time, and depends on the user's Regional Settings (in the Windows Control Panel.) So perhaps your question is, "How can I ensure that Access understands the dates in the query, and stores the correctly when the Update query executes?" I also live in a d/m/y country, and in 15 years of working with Access have found 3 cases where Access is likely to misunderstand our dates. Details in: International Date Formats in Access at: http://allenbrowne.com/ser-36.html If the dates are calculated ones, typecasting them in the query can also help: Calculated fields misinterpreted at: http://allenbrowne.com/ser-45.html -- remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security |
#4
|
|||
|
|||
Date format in update query
That's good. Hopefully you will get consistent results.
Re the yyyy/mm/dd format, although that is not the native JET format, I would expect it to be interpreted consistently, unless the mm part is greater than 12. That is an international database standard, and it seems to me like the most logical format to use, and those who use it generally report that it gives consistent results. The reason I recommend the mm/dd/yyyy format is that this is what Access/JET does natively. I like the idea of an optional boolean argument for specifying whether to include the time component. Not only is it flexible, but acts as a memory jogger for any developer who uses the function. I might incorporate that idea too. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "jacksonmacd" wrote in message ... Allen I found your ser-36 article to be extremely useful. I written my own SQLDate() wrapper function, but apparently mine contained two errors that your function does not. I am baffled why the function appears to have worked properly for the (at least) three years that I've been using it. Can you offer any insight why it continued to work with these two errors? Was I just lucky...?? 1) I did not know about the "\/" format trick, and my output used the "-" that you mention 2) My output uses "yyyy/mm/dd" format, and as far as I can tell, it always selects the correct data. On tweak that my function includes is an optional boolean parameter whether or not to include the time portion of the date in its output. With a default value of False, the normal calls to the function are identical to yours. Simply adding "TRUE" after the passed value causes the time to be included in the formatted output string. I find it handy in many situations. On Fri, 23 Jun 2006 11:37:20 +0800, "Allen Browne" wrote: John, Access does not store dates in a specific format. The format is applied at display time, and depends on the user's Regional Settings (in the Windows Control Panel.) So perhaps your question is, "How can I ensure that Access understands the dates in the query, and stores the correctly when the Update query executes?" I also live in a d/m/y country, and in 15 years of working with Access have found 3 cases where Access is likely to misunderstand our dates. Details in: International Date Formats in Access at: http://allenbrowne.com/ser-36.html If the dates are calculated ones, typecasting them in the query can also help: Calculated fields misinterpreted at: http://allenbrowne.com/ser-45.html -- remove uppercase letters for true email http://www.geocities.com/jacksonmacd/ for info on MS Access security |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Adding info to query | mjj4golf | Running & Setting Up Queries | 20 | January 9th, 2006 02:29 AM |
MS Query Date Format | scos00 | General Discussion | 0 | October 21st, 2005 04:58 PM |
Copy Cat Ain't Working | shep | Setting Up & Running Reports | 15 | September 12th, 2005 05:14 PM |
Modify Format of Date Query | Leroy | Running & Setting Up Queries | 7 | September 10th, 2005 12:33 AM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |