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.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Date Format mm/dd/yy

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Mar 2009
Posts: 113
Date Format mm/dd/yy

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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Nov 2011
Posts: 376
This works for me;
Expr1: Format([Last Date Updated],"yy/mm/dd")

Substitute your field name for Last Date Updated
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Mar 2009
Posts: 113
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?
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Nov 2011
Posts: 376
Expr1: Format([Order Date],"mm/dd/yy")

It works fine in my query, try and let me know, sorry for the backwards format
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Mar 2009
Posts: 113
Thank You Burrina!!!

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

Thanks again.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Mar 2009
Posts: 113
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.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Nov 2011
Posts: 376
What is the field,Data Type?
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Mar 2009
Posts: 113
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.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Nov 2011
Posts: 376
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,
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Mar 2009
Posts: 113
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.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Nov 2011
Posts: 376
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.
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
Join Date: Mar 2009
Posts: 113
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!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On