Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Question Unanswered: Export a DateField

    I have a Date Filed in a table, I want to export it to a .csv . My problem is I get this field, as a Date and time i.e tt.mm.yy 00:00:00 but I need it only in the tt.mm.yy. Is there any chance to do this.

    Thanks

  2. #2
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238

    Re: Export a DateField

    Originally posted by hammbakka
    I have a Date Filed in a table, I want to export it to a .csv . My problem is I get this field, as a Date and time i.e tt.mm.yy 00:00:00 but I need it only in the tt.mm.yy. Is there any chance to do this.

    Thanks
    Use the format command as you export the date field. For example use:

    SELECT Format(MyDateField, "dd.mm.yy") FROM MyTable

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Re: Export a DateField

    Originally posted by justin_tighe
    Use the format command as you export the date field. For example use:

    SELECT Format(MyDateField, "dd.mm.yy") FROM MyTable
    That's nice, but I use the DoCmd.TransferText in which you pass a table name. Perhaps I could make a create table query which creates a table with the desired format, and then export this table.

    Any other idea? Thanks agian

  4. #4
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    You can use a query in the Docmd.TransferText command instead of a Table name. So you could use the format command in a saved query and pass that to the TransferText command

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by justin_tighe
    You can use a query in the Docmd.TransferText command instead of a Table name. So you could use the format command in a saved query and pass that to the TransferText command
    justin_tighe

    Thank you very much, I'll try it now.

    By the way what is the format to include leading nulls?

    Thanks

  6. #6
    Join Date
    Nov 2003
    Location
    LONDON
    Posts
    238
    To add leading spaces you can use the Space command to return a set number of spaces. You can use this in a SQL statement as well as in VBA. For example:

    SELECT SPACE(10) & Format([MyDateField], "dd.mm.yy") FROM MyTable

    I hope this helps!

  7. #7
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by justin_tighe
    You can use a query in the Docmd.TransferText command instead of a Table name. So you could use the format command in a saved query and pass that to the TransferText command
    I did it, but it export the date as a text (between two quotes " ")

    It were better to get it as a date.

    Inspite thanks allot

  8. #8
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by justin_tighe
    To add leading spaces you can use the Space command to return a set number of spaces. You can use this in a SQL statement as well as in VBA. For example:

    SELECT SPACE(10) & Format([MyDateField], "dd.mm.yy") FROM MyTable

    I hope this helps!
    I did it like so:

    SELECT Format([myDateField],"dd\.mm\.yy") AS DateField, Format([myNumericField],"000000") AS NumericField FROM myTable

    so I could fix this field [myNumericField] to 6 digits

    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
  •