azizrasul
Well-known Member
- Joined
- Jul 7, 2003
- Messages
- 1,304
- Office Version
- 365
- 2019
- 2016
- Platform
- Windows
I have been doing a study in dates for some time as I have a DB that uses them a lot. I would appreciate it if someone can confirm my findings. My computer settings viz. Regional Options settings of the Control Panel are English(U.K.).
Date literals used in queries
1. Calculations of dates & interpretation of dates are determined by the computer settings.
2. Where dates are written in English(U.S.) format, they are changed on screen as per the computer settings e.g. #01/13/2004# to #13/01/2004#.
String dates used in queries
1. Calculations of dates & interpretation of dates are determined by the computer settings.
2. Where dates are written in English(U.S.) format, they are NOT changed on screen as per the computer settings but are calculated as per the computer settings none the less e.g. DateAdd("ww",1,"01/13/2003") = 20/01/2003.
Date literals used in VBA
Dates should and will be interpretated as English(U.S.) and calculated as such but will output the date as per the computer settings e.g. DateAdd("ww", 1, #1/11/2003#) = 18/01/2003 i.e. 18th of Jan
String dates used in VBA
1. Dates will be interpretated as per the computer settings and outputs as per the computer settings as well. For example DateAdd("ww", 1, "1/11/2003") = 08/11/2003 i.e. 8th of Nov
2. Dates that could NOT comply according to the computer settings will be interpretated as English(U.S.) but will still output the result as per the computer settings e.g. DateAdd("ww", 1, "1/13/2003") 20/01/2003 i.e. 20th of Jan
Conclusion
In queries use the computer settings to write dates. The results will also comply to the computer settings.
In VBA dates should be written as English(U.S.) and the resulting dates will follow thcomputer settings format. If the dates are required to be written according to the computer settings, then use the Format function to 'persuade' the dates in that way.
Date literals used in queries
1. Calculations of dates & interpretation of dates are determined by the computer settings.
2. Where dates are written in English(U.S.) format, they are changed on screen as per the computer settings e.g. #01/13/2004# to #13/01/2004#.
String dates used in queries
1. Calculations of dates & interpretation of dates are determined by the computer settings.
2. Where dates are written in English(U.S.) format, they are NOT changed on screen as per the computer settings but are calculated as per the computer settings none the less e.g. DateAdd("ww",1,"01/13/2003") = 20/01/2003.
Date literals used in VBA
Dates should and will be interpretated as English(U.S.) and calculated as such but will output the date as per the computer settings e.g. DateAdd("ww", 1, #1/11/2003#) = 18/01/2003 i.e. 18th of Jan
String dates used in VBA
1. Dates will be interpretated as per the computer settings and outputs as per the computer settings as well. For example DateAdd("ww", 1, "1/11/2003") = 08/11/2003 i.e. 8th of Nov
2. Dates that could NOT comply according to the computer settings will be interpretated as English(U.S.) but will still output the result as per the computer settings e.g. DateAdd("ww", 1, "1/13/2003") 20/01/2003 i.e. 20th of Jan
Conclusion
In queries use the computer settings to write dates. The results will also comply to the computer settings.
In VBA dates should be written as English(U.S.) and the resulting dates will follow thcomputer settings format. If the dates are required to be written according to the computer settings, then use the Format function to 'persuade' the dates in that way.