Running a specific Macro depending on the Day!!

[MarkNie]

Hi All

I am not very good with VBA and any help would be great.

I have 2 macro's that run reports for diff day eg:

Macro1 runs data as Date()-1
Macro2 runs data as Date()-2

What I want to do is use an if and then code to make this happen.

This is what I got so far but does not seem to work. Can't seem to get the procedure right to run the code:

This is the if code:

Sub MISExport()

If [yqryAppendPerformanceSummary].[Date] = vbMonday Then
acCmdRunMacro = MISExport3day
Else
acCmdRunMacro = MISExport1day
End If
End Sub

This is the procedure code:

Sub RunMIS()

Dim MisRun As String

MisRun.Run

End Sub

Any help with this will be great.

Thanks
Regards
Mark

 

[SkipVought]

Hi,

If Format([yqryAppendPerformanceSummary].[Date],"ddd") = "Mon" Then
...

Skip,

_[red][/red]

 

[mp9]

Alternately, why not take the effort out of running the macro completely?

Remove the date evaluation stuff from the macro and then use a scheduling tool like the Handy Access Launcher to run the macro automatically on the day/time/whenever you like.

 

[MarkNie]

Hi Skipvought

Where would I put this in?

In VB ?

Thanks
;o)

 

[randy700]

Skipvought is showing you how to correct the IF statement you have in your code.

If [yqryAppendPerformanceSummary].[Date] = [s]vbMonday[/s] Then

If [COLOR=red]Format([/color][yqryAppendPerformanceSummary].[Date][COLOR=red],"ddd") = "Mon"[/color] Then

Randy

 

[MarkNie]

Thanks for that

I had to change it a bit as I have just realised that there is no date field and that the date has to come from the computer.

So I have done the following:

Sub MISExport()

If Format(Date, "ddd") = "Mon" Then
DoCmd.RunMacro "MISExport3day", 1
Else
DoCmd.RunMacro "MISExport1day", 1
End If
End Sub

But then it asks me for a procedure. I have no Idea how to create a procedure for this. Any help at all will be great.

Thanks
Regards
Mark

 

[SkipVought]

In a Module?

In a FORM?

In a REPORT?

Skip,

_[red][/red]

 

[MarkNie]

Hi

In a module, which runs the macros which runs the queries.There are no forms or reports used for this just tables which get exported to Excel.

I think that is the flow.

Thanks for your help so far.

Regards
Mark

 

[Binnit]

Don't forget that you may not get your reports exported on a Bank holiday Monday if nobody opens the database!

Just playing devils advocate...

Happiness is...not getting what you want but wanting what you have already got

 

[mp9]

Hence the need for scheduling tools!

 

[MarkNie]

Hi Guys

Thanks for all the advise, the problem I have is that it is alot of red tape to install new programs onto our systems FSA reg and everything you know.

I do also know it will be a problem if there are public holidays but I have got a work around for that.

All I need is someway to do a IF Then statement which says if Monday then run macro a else run macro b.

Any help will be great thanks
Regards
Mark