Am currently rebuilding a database in Access 2007. I have date field in a query that I export into Excel for Scripting into another system. I need the Date to be in the format of mm/dd/yy as the system I am scripting the date into must have the year as 2 digits.
I've tried everything I can think of to get the format to appear in the query as mm/dd/yy, but if I type this into the format section on the query I get 01/01/2012 instead of 01/01/12.
Is there something different in 2007 or do I just need to live with it and format it in Excel after its out?
I have another question about formating. This one though is a number, I am not sure what the syntax should be or how it should be written in the formula.
I have a formula now that does some math based on another field and table. It displays fine but I export and it comes out with all kinds of decimal places, more then 2. I would like it to export with only 2 decimals.
I tried Format([field],#.##) but it didn't work so not sure how this should be.
Hmmm, I can't help you with Excel. It is not my thing.Try changing your excel date format to dd/mm/yyyy to see if Access imports it correctly.
P.S. Make Sure Your Table Fields Are Set To Display Correct Date Formats Before Exporting.
If the query displays correctly in Access then Good. In Excel you will need to do something like ;
Round (12.55, 1) would return 12.6 (rounds up)
Round (12.65, 1) would return 12.6 (rounds down)
Round (12.75, 1) would return 12.8 (rounds up)
In these cases, the last digit after rounding is always an even number. So, be sure to only use the Round function if this is your desired result.
The syntax for the Round function is:
Round ( expression, [ decimal_places ] )
The Round function can be used in VBA code. For example:
Dim LNumber As Double
LNumber = Round (210.67, 1)
In this example, the variable called LNumber would now contain the value of 210.7.