Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2009
    Posts
    120

    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

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    This works for me;
    Expr1: Format([Last Date Updated],"yy/mm/dd")

    Substitute your field name for Last Date Updated

  3. #3
    Join Date
    Mar 2009
    Posts
    120
    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?

  4. #4
    Join Date
    Nov 2011
    Posts
    413
    Expr1: Format([Order Date],"mm/dd/yy")

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

  5. #5
    Join Date
    Mar 2009
    Posts
    120
    Thank You Burrina!!!

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

    Thanks again.

  6. #6
    Join Date
    Mar 2009
    Posts
    120
    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.

  7. #7
    Join Date
    Nov 2011
    Posts
    413
    What is the field,Data Type?

  8. #8
    Join Date
    Mar 2009
    Posts
    120
    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.

  9. #9
    Join Date
    Nov 2011
    Posts
    413
    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,

  10. #10
    Join Date
    Mar 2009
    Posts
    120
    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.

  11. #11
    Join Date
    Nov 2011
    Posts
    413
    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.

  12. #12
    Join Date
    Mar 2009
    Posts
    120
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •