Converting the Date to a String

[Finchy]

Hi there,

I am trying to automate copying a file from one place to another in Access.

The file name changes daily (the date) so it needs to build the file name. What I have looks like it SHOULD work, even hovering above the code I can see that everything is exactly as it should be. I can only think that there is a problem with using the date in the file name since access handles dates as a number.

Is there a way of converting the date to a string?

Would love anyones thoughs or possible sollutions....

The code:
Dim Day As Date
Dim startpath As String
Dim endpath As String
Day = Now()-1


startpath = "P:\Export\Scanners_" & format(Day, "mm") & "#" & format(Day, "dd") & "#" & format(Day, "yy") & ".txt"

endpath = "C:\Availability\Archives\Scanners_" & format(Day, "mm") & "#" & format(Day, "dd") & "#" & format(Day, "yy") & ".txt"

FileCopy "startpath", "endpath"

End Function

The error message is 'File not found'

Hovering above the startpath line shows that it is exactly what it sould be, the spelling and the file name are bang on?!?

 

[darbid]

do a debug.print of the 2 variables and then cut and copy the path into your explorer. Do you get the files?

I would not use "Day" as a variable. I would say that could lead to problems at some stage as it most likely is a reserved word.

 

[Finchy]

Thanks for the quick response darbid. I am noob at VBA and I have never heard of debug before. I have checked google out and it appears that I use the line at the start of the line I wish to debug. I did this, and it returned 'false' for both startpath and endpath line.

As I am writing this I have figured out what I have done wrong.
FileCopy "startpath", "endpath"

the above line does not need "" since I have already defined the names. I feel a bit of a plonker but this is how we learn.

Thanks anyway!

John

 

[darbid]

and then I could have seen that as well in your original post.

Yes if you dont waste hours and loose your hair - then you paid for your code.

 

[Finchy]

Yes, its not the first time I have made a simple but stupid mistake. I appreciate your time though.

John

 

[boblarson]

Also, Finchy just a few other words of advice.

Not only should you not use Day as a variable (because it is actually an Access Reserved Word, more specifically an existing function) you should use DATE and not NOW if you just want the date portion and don't need the time.

Second, you're string when setting the file path is way too hard. If you use format you can set it to be what you want with one simple stroke:

Dim mDay As Date
[COLOR=black]Dim startpath As String[/COLOR]
[COLOR=black]Dim endpath As String[/COLOR]
[COLOR=black]mDay = DateAdd("d", -1, Date)[/COLOR]


startpath = "P:\Export\Scanners_" & format(mDay, "mm\#dd\#yy") & ".txt"

[COLOR=black]endpath = "C:\Availability\Archives\Scanners_" & format(mDay, "mm\#dd\#\yy") & ".txt"[/COLOR]

See you don't need to break it up into the individual pieces. If you have a literal character like # you want in there you use the backslash to tell it you want that literal character. Makes the code shorter and easier to read.

Just thought you might like to know that.

 

[Finchy]

Hello Bob,

Thanks for taking the time out to tell me that. I have been guilty of using reserved words before, also naming my modules the same as the function in it..

I will alter my code accordingly and use your less complicated way to build the file name up.

Again, Thank you.

John