Results 1 to 10 of 10

Thread: Date to String

  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date to String

    Does anyone know how I can change the following:

    Dim strDate As Date
    Dim strFileName As String

    strDate = Date - WeekDay(Date - 6) - 7
    strFileName = "PH_Exp$" & strDate

    DoCmd.CopyObject "", (strFileName), acTable, "PH_Exp$"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strFileName, c:burncdstrFilename.xls", True

    When the table changes the name to strFileName I get this.... PH_Exp&4/27/01.....I want this....PH_Exp$42701

    I have tried changing Dim strDate As Date to strDate As String...but no luck....

    Hope someone knows the answer...

    Thanks,
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date to String

    Try this:

    strFileName = "PH_Exp$" & Format(strDate, "mddyy")

    Instead of:

    strFileName = "PH_Exp$" & strDate

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date to String

    Paul,

    Thanks for the suggestion, it works perfectly.

    I thought the fact that the date 4/27/01 had the forward slashes was giving me an error but what you suggested worked and I still get the error, so I guess it wasn't the forward slashes after all.......

    I do appreciate your help.

    Thanks
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date to String

    Glad to help, Roberta! And I must say it's nice to connect a face with the name after seeing just your name on the other forum (ElementK).

    What's the error...maybe I can help with that too.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date to String

    Hi Paul,

    I'm not sure if I will explain this correctly, but I will give it a try.

    I have some tables that I want to either use the Export or TransferSpreadsheet method with. This data needs to be saved in Excel for archiving purposes. Because it is being archived, adding the date to the end of the name of the table is important.

    At this time, that isn't a problem, however, if I use the Export method, (see below) the user must to know the path in which to save it to.

    DoCmd.OutputTo acTable, (strFilename), acFormatXLS, , False

    If I use the TransferSpreadsheet method, I continue to get the a runtime error which I have attached. (see below)

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strFilename, "c:HistoryArchivesstrFilename.xls", True

    The thing is is I don't want the user to have to do anything. I want the table to go straight to the path which is designated in the method.

    I am not sure why I can't add the date (in string format) to the table name and transfer the spreadsheet to the predesignated path.

    If you have any suggestions, I would certainly appreciate it.

    Thanks alot Paul.
    Attached Files Attached Files
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date to String

    I believe the problem lies in your statement:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, strFilename, "c:HistoryArchivesstrFilename.xls", True

    The argument strFileName should be the name of the table you are exporting as it exists in the database without any date concatenation. The next argument is where you want the date to concatenate with the table name. Something like this:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "tblNameOfTableToExport", "c:HistoryArchivestblNameOfTableToExport" & format(Date(),"mddyy") & ".xls", True

    You can do the concatenation on separate lines but don't confuse the name of the table to export with the destination file name. They should be two separate objects or variables.

    BTW, I tried exporting a table using the same method and intentionally supplying an invalid table name and got a similar error message so that's why I'm focusing on the transferspreadshet method.

  7. #7
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date to String

    Paul,

    OK...I think I am almost there. I did the following:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "PH_Exp$", "c:HistoryArchivesPH_Exp$" & Format(Date, "mddyy") & ".xls", True

    and got the attached error.

    Thanks again for your help.
    Attached Files Attached Files
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  8. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date to String

    I think it is the "$" in the table name that is throwing things off. I tried the statement with the $ and without it (had to rename the table) and it worked without but I got a similar error that you did with it. So, try renaming the table and editing the code and try it that way and see if it works.

  9. #9
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date to String

    Good Morning Paul....

    Excellent!!! Thank you so much for your help...I wish I could do more to repay you but I can't....You have been a life saver....

    Have a nice day and again...THANKS!!!!!!!!!!!
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date to String

    Great!! that we figured it out! Glad to have helped....

Posting Permissions

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