Hello All,

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?

Thanks

This works for me;
Expr1: Format([Last Date Updated],"yy/mm/dd")

Substitute your field name for Last Date Updated

Thanks Burrina. However the "yy/mm/dd" format isn't what I need, I need it to be 'mm/dd/yy' as the system I am scripting into has to have it in that format.

Will this work in that format as well and will this work in a Query/Report?

Expr1: Format([Order Date],"mm/dd/yy")

It works fine in my query, try and let me know, sorry for the backwards format

Thank You Burrina!!!

That works fantastic. It worked as I needed it too and everything.

Thanks again.

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.

What is the field,Data Type?

It isn't a field in a table, it is just a field in a Query that is doing a calculation:

MCRCLI: IIf([T-00-Charges]![BAR Mnemonic1]='4400',[T-96-MCR Fee Schedule]![Fee])

I have the properties set as Standard with 2 decimals. It works in the query when it is run and displayed on the screen but exporting to Excel I end up with 4 or more decimal places.

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.

Good Luck,

It isn't really excel. I am exporting a query from Access to Excel.

The field MCRCLI I pasted above shows fine when I run the query in Access and view it in Access. But when I export it to Excel the same filed has more then 2 decimal places.

I have the propert set in the Query as Standard and 2 decimals. But again when I export the query to Excel it comes out as somethign like 5.67999982833862 instead of 5.68.

If there isn't a way to use the Format comand in the query for this I'll just have to format them in Excel after I export from Access.

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.
Syntax

The syntax for the Round function is:

Round ( expression, [ decimal_places ] )
VBA Code

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.

Like I said, I am not a Excel guy!

Hope this helps.

Thanks. But I know how to do that in Excel. I was hoping for a way to get it to Export from Access correctly.

I'll just do it manualy after the fact.

Thanks again!