Using a function in a macro to "snapshot" a report

[jdowski]

Hi Excel...er, I mean Access Experts,

I have created a function called dateSnap()

Function dateSnap()

dateSnap = Format(Now(), "mm/dd/yy hh:mm AM/PM")

End Function

My function works fine by itself.
Now I would like to use this function within a macro using the Output to statement to "snap" out some exception reports each month. We run the reports 5 or 6 times a day sometimes and I need to be able to track the different reports as we adjust data.

In the macro I am typing the following below where it asks for Output File:

="C:\MIS\Snapshot Reports\Exception Reports\12_MissingOfficer " & datesnap() & ".snp"

But my macro keeps bombing. This is just one report I experimenting with. I have literally dozens more to do this with once I get this problem ironed out.

Thanks,

Joe in Oxford, CT. USA.
Power just came back @ 4:50am this morning.....

 

[RossLaw]

Re: Using a function in a macro to "snapshot" a re

You could try to call the variable when outputiing the file and not call the variable setting function:

In the macro I am typing the following below where it asks for Output File:

="C:\MIS\Snapshot Reports\Exception Reports\12_MissingOfficer" & datesnap & ".snp"

If that fails try creating datesnap just before you output the file...

 

[jdowski]

Re: Using a function in a macro to "snapshot" a re

Thanks RossLaw,

I found out what it was. My format statement was creating a date/time format that includes "/" and ":" both of which apparently are no-no's for naming comventions. Once I got rid of those the rest of my statement works fine.

Thanks again !!

J.