Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2008
    Location
    Missouri
    Posts
    8

    Unanswered: Export tbl or qry to csv in date format MM/DD/YYYY must have...

    Hello:

    I need to export a table or query to csv text file. The Date fields need to be in the following Short date format:

    MM/DD/YYYY must have leading zeros for example 02/03/2009

    Window XP Regional settings seems to force dropping leading zeros and adding Hours Minutes and seconds. I can not change regional settings for other users.

    What is an easy way to export dates that look like this
    ,2/3/2009 0:00:00,

    and turn them into this
    ,02/03/2009,

    Your help would be greatly appreciated.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    format$(yourDate, "MM/DD/YYYY")

    ??

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Nov 2008
    Location
    Missouri
    Posts
    8

    Thanks izy

    That probably would have been easier than:

    AuthDate: IIf(Month([CHIST]![CHECK_DATE])<10,"0" & Month([CHIST]![CHECK_DATE]),Month([CHIST]![CHECK_DATE])) & "/" & IIf(Day([CHIST]![CHECK_DATE])<10,"0" & Day([CHIST]![CHECK_DATE]),Day([CHIST]![CHECK_DATE])) & "/" & Year([CHIST]![CHECK_DATE])


  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by MS Access Help File on Format Function
    Format Function

    Returns a Variant (String) containing an expression formatted according to instructions contained in a format expression.
    Hey Izy, I thought Format already returns a string type, so why the format$? Just curious, not challenging.
    Me.Geek = True

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I believe it's faster.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    depending where you look in M$ documentation, you are told either that format() returns a variant(string) or a string.

    AFAIK
    format() returns a variant (of subtype string)
    format$() returns a string
    if this happens to be correct, format$() should save one cast:
    date -> string
    vs
    date -> variant(string) -> string

    the question is, how to prove this fine theory?

    the only two ways i can think of for checking if something is a variant(string) or a string are:
    - the isempty test (should only work on variants)
    - speed

    i hope to look into it over the weekend
    i.e. i will waste many MANY times the milliseconds i could save in an entire lifetime of using the hypothetically faster format$()

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    May 2005
    Posts
    1,191
    Tested:

    Code:
    Private Sub cmdbtnTimer_Click()
        
        Dim sngStart As Single, _
            sngEnd As Single
        Dim i As Long, _
            iMax As Long
        Dim strTemp As String
        
        
        iMax = 1000000
        
        
        sngStart = Timer()
        For i = 0 To iMax
            strTemp = Format$(Date, "MM/DD/YYYY")
        Next i
        sngEnd = Timer()
        
        
        MsgBox CStr(iMax) & " Format$ Time = " & sngEnd - sngStart & " seconds", _
                , _
                "Timer Results"
        
    End Sub
    Format = 1.484375 secs
    Format$ = 1.4375 secs

    About 3% faster.
    Attached Thumbnails Attached Thumbnails Format Timer.bmp   Format$ Timer.bmp  
    Me.Geek = True

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    and tested again:

    i used TimeGetTime as the timer and subtracted the empty loop overhead each run.

    initial test was in MDB using
    const dtTest as date = #3/4/2005#
    format(dtText, "MM/DD/YYYY")
    ...i found an extremely thin 0.68% advantage for format$()

    next test was in MDE using
    const dtText as date = #3/4/2005 15:16:17#
    format(dtText, "MM/DD/YYYY hh:nn:ss")
    ...i found a much nicer advantage averaging 10% over 10 + 10 runs of 1,000,000 loops.

    izy
    Attached Thumbnails Attached Thumbnails formattest.GIF  
    currently using SS 2008R2

Posting Permissions

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