Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2005
    Posts
    67

    Unanswered: Exporting to comma delim text file, format of numbers changing

    Hi - in exporting to a comma delimited text file from access via vba, my numbers are re-formatting. How do I ensure that the ones without decimals, remain without decimals? I've set all the formats in the tables and the forms and they appear alright there, but once exported they change format....

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Export them as strings. Access gets confused when you try to tell it to store two different data types in the same column. 2 <> 2.00. that's an integer vs a float.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2005
    Posts
    67
    How?

    This is my export code:
    DoCmd.TransferText acExportDelim, "tblHeadOutExpSpec", "tblHeaderOutput", "D:\temp\HeaderOutput.txt", False

    DoCmd.TransferText acExportDelim, "tblDetailsOutExportSpec", "tblDetailsOutput", "D:\temp\DetailsOutput.txt", False

    So where do I tell Access to export as strings?

  4. #4
    Join Date
    Dec 2005
    Posts
    67
    For some reason it's working now. Thanks for your response anyway

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    In the future, you can use the Format function in a query to turn your numbers into strings with user defined formatting

  6. #6
    Join Date
    Dec 2005
    Posts
    67
    Well I'm having a problem with that of sorts already.

    Each time I export, the date format changes to date with time appended to it (e.g. 14/12/2005 00:00:00). I've tried formatting it with this piece of code:

    Me.DTPicker4DateToday = FormatDateTime(Date, vbShortDate)
    Me.DTPickerRcptDate = FormatDateTime(Date, vbShortDate)

    Within the database it's fine, but once it's in the text file it changes to the long date format. I've tried going into the 'spec', but I can't even find anywhere to edit it even under the 'Advanced' tab.

  7. #7
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Since you're going to a text file, you might as well use the functions that convert data types to text: Format

    For a date: Format([DateField],"mm/dd/yyyy")
    For a number: Format([NumField],"0.00")

    There are a large variety of ways you can define the format (the part in the quotes) - check the help file for details.

  8. #8
    Join Date
    Dec 2005
    Posts
    67
    Thanks but the date is still not working. It saves in the correct format in the tables, but by the time it gets into the text files it's reformatted. This is what I wrote as my code:

    Me.DTPicker4DateToday = Format([DTPicker4DateToday], "mm/dd/yyyy")

    and

    Me.DTPickerRcptDate = Format(DTPickerRcptDate, "mm/dd/yyyy")

    They're still coming out with as date and time....15/9/2005 00:00:00

  9. #9
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    wait, you're using the format command to take a date value form a date control, convert it to text and then place it back into a date control.

    Placing text that looks like a date into a date control leads Access to convert the text into a valid date.

    Add 2 fields called txtDTPicker4DateToday and txtDTPickerRcptDate and try this:
    Code:
    Me.txtDTPicker4DateToday = Format([DTPicker4DateToday], "mm/dd/yyyy")
    
    and 
    
    Me.txtDTPickerRcptDate = Format(DTPickerRcptDate, "mm/dd/yyyy")

  10. #10
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,422
    Provided Answers: 7
    Been there

    I always format the date as mmm so it wrights the word most progam can pick up the word

    what the differance 01/05 05/01 is it jan or may (new zealand here dd/mm/yyy format )

    i have found that msaccesss has a guest on what it think it should be

    so i always format my dates to dd mmm yyyy format when exporting
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  11. #11
    Join Date
    Dec 2005
    Posts
    67

    Date changing format when exported to text file

    Thanks - but it's still not working. I did what you said, tcace, i.e:

    created two new fields and then wrote this code;

    Me.txtDTPicker4DateToday = Format([DTPicker4DateToday], "mm/dd/yyyy")

    and

    Me.txtDTPickerRcptDate = Format(DTPickerRcptDate, "mm/dd/yyyy")


    DTPicker4DateToday was already a field name - maybe that's why it's not working. Should I just delete both field names and then start again without using a date picker but just making the user type in the date (I really would prefer a date picker though)... what do you suggest?

    Myle - I can't do what you suggested because part of the requirements spec is that the text file should contain dates in the format dd/mm/yyyy, but thanks anyway.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    see posting #5 frm tcace
    tcace already pointed out to you that you are formatting a date/time value as a date and stroring it back as a date time/value

    generate your export using a query, do your formatting in that query
    forcing a numeric month on a date value is always prone to problems especially if the exported file is going to computer system.

    as an alternative use a file export specification: set the date format in the export specification as required. I think yu cna also set numeric fields defining the number of decimals

    Concur with myle dd mmm yyyy, or mmm dd yyyy, or yyyy mmm dd etc.. is far safer - text imports can nearly always handle those safely. Unless of course its going to a predetermined file format when you have no choice. The number of times I've sent exports of data to client computers which think they are America rather than the true date zone and had the imports throw an exception as it read 12/22/2005 as month 22, or far worse where 01/12/2005 is interpreted as 12th january, rather than 1st Dec

  13. #13
    Join Date
    Dec 2005
    Posts
    67
    Thanks for your help.

    Nothing was working but I finally got round it by simply changing the format in the table to 'short date' and it worked . I take your point about the formatting i.e. 'mmm' and will bear it in mind for other projects, but the client for this one has insisted they want it in the dd/mm/yyyy format, but I will definitely bear all your comments in mind. 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
  •