On Specific Time Criteria

[Guest]

I have a Macro set to run On Current set on a minimzed form.
This is used to export two queries to excel on the hour every hour (Rather
than use the ON Timer event procedure). However I cannot seem to get the Time
function to work as I would with Weekday or Date function criteria.

This is what I have but it does not work

On Current: run macro: Publish Info

Criteria Action
Time()=#13:30:00# Run Macro - Publish RF1

or

Time=()=time(hh:30:00) Run Macro - Publish RF1

The second is the one I want but I can't parse the expression.

I am sure I am near. Any help would be greatfully appreciated.

Thanks

Andi

 

[Guest]

Further to this I have even used this and it tells me it is invalid!

DatePart("mm",Time())=25

For 25 minutes past.

Please enlighten me.

Thanks

Andi

 

[Guest]

Ok,

I have decided to use the ON Timer interval to run the macro instead.

But it keeps asking me to overwirte the old file.

Is there a way to have it automatically save without generating this message?

Thanks

Andi

 

[Steve Schapel]

Andi,

Time()=#13:30:00# is correct syntax, but you will never get it to work
in this context, as it would rely on the instant that the macro is
called being precisely at that time, to the 1/100th of a second, which
of course is extremely unlikely.

 

[Steve Schapel]

Andi,

"m" refers to month, so the maximum value is 12.

You could use either of these:
Minute(Now())=25
Format(Now(),"n")=25
Format(Time(),"n")=25
DatePart("n",Now())=25
DatePart("n",Time())=25

As long as you triggered the event which runs the macro within the
minute, the macro should work.

 

[Steve Schapel]

Andi,

Not that I know of. You will either have to set your macro to generate
a different file name every time, or else delete the old file first,
which can't be done with a macro, you would need to use a VBA procedure
using the Kill method for this.

 

[Guest]

Ahhh.... Now I see. Thanks very much.

Andi,

Time()=#13:30:00# is correct syntax, but you will never get it to work
in this context, as it would rely on the instant that the macro is
called being precisely at that time, to the 1/100th of a second, which
of course is extremely unlikely.