Date command returns wrong date despite system date being correct

[michal79]

Hi,

I'm saving my report as pdf and have written some VBA which exports the report to my desktop and assigns a custom name to the report based on the username and current date. It all works however despite my Win7 system date being correct the Date in code returns 22-11-2002. I think that I actually got the same date yesterday. My code is as follows:

Private Sub Command18_Click()
 
On Error GoTo Command18_Click_Err
 
Dim Username As String
Username = Environ("UserName")
UserDate = Username & " - " & Date
TheFile = "LLIS Report - " & UserDate & ".pdf"
 
DoCmd.OutputTo acOutputReport, "ReportLLIS", "PDFFormat(*.pdf)", "C:\Users\" & Username & "\Desktop\" & TheFile & "", False, "", , acExportQualityPrint
 
TheMessage = "The Report has been succesfully exported to Desktop\" & TheFile
 
Beep
MsgBox TheMessage, vbInformation, "Report Save Complete"
Command18_Click_Exit:
    Exit Sub
Command18_Click_Err:
    MsgBox Error$
    Resume Command18_Click_Exit
 
End Sub

Is there something I do wrong? Has anyone experienced simmilar problem. I have also tried to use Now() but that returns error and the action is cancelled while exporting the file.

Hope you can help.
Thanks,

Michal

 

[pr2-eugin]

Hello Michal,

I have seen this in the past, when I started developing DB.. Using Date alone will fail anyway.. Because general format of Date is 22/03/2012 and Windows does not allow you file/folder with / in them..

So I am making a bold guess that there is a control/field that has the name 'Date'.. You can overcome this by using

Format(DateValue(Now),"dd-mm-yyyy")

This is the reason why Date forms a bad control/field name.. So the other advice is that make sure that a "Proper naming convention" is followed..

 

[RainLover]

Paul

I have not done any testing etc but why not use this instead.

Format(DateValue(Date),"dd-mm-yyyy")

 

[pr2-eugin]

Paul

I have not done any testing etc but why not use this instead.

Format(DateValue(Date),"dd-mm-yyyy")

Hello Rain, If there is a control/field name in the Form/Table that it is bound to it will refer to that control but not the Date() (atleast in the one I had in the past), as we expect it should.. However using Now() might overcome this..

 

[RainLover]

Thanks Paul.

The OP should fix his naming convention as you mentioned. If not then it will be one work around after another.

Cheers.

BTW I am not sure that the code is correct, so I am assuming you have tested this in the past.

 

[pr2-eugin]

so I am assuming you have tested this in the past.

I have, and I also tested it out now.. I think it is right.. LOL..

? Format(DateValue(Now),"dd-mm-yyyy")
05-03-2013

Thanks for looking at that..

 

[michal79]

Paul,

Thank you very much! That certainly helped and the code works fine

Best regards,
Michal

 

[pr2-eugin]

Michal, good to hear it works..

BUT as Rain has pointed out, this is just a patch.. If you do not deal with proper naming conventions, you might end up finding work around on top of another leading to a total disaster.. It will save you a lot of hassle if you act now..

Good luck !!

 

[RainLover]

Good one Paul.