Use of time in VBA If statement-Syntax

[Richard]

I have a form with a test box displaying the current time
and updating every 3 seconds.Works fine. I want to query
this text box with an If ...else statement to have an
action created in specified time frame (an automatic
update event(append a table)). Do I set the Dim XXXXXX
as Date or Time and in the if statement I need the
correct syntax for "Time" so it can compare it the long
time in the current time text box I have set up.

Any help would be much appreciated.

Richard

 

[Jim Allensworth]

I have a form with a test box displaying the current time
and updating every 3 seconds.Works fine. I want to query
this text box with an If ...else statement to have an
action created in specified time frame (an automatic
update event(append a table)). Do I set the Dim XXXXXX
as Date or Time and in the if statement I need the
correct syntax for "Time" so it can compare it the long
time in the current time text box I have set up.

Any help would be much appreciated.

Richard

Why are you querying the textbox?

Since it is displaying the current time why not just use the Now()
function?

- Jim

 

[Wayne Morgan]

As Jim mentioned, there is no need to query the textbox, just use the Now or
Time function in your If statement. However, You'll still need to do the
compare. There is no predefinded data type "time", if you try to Dim a
variable as Time you will get a compile error that the "User-defined data
type not defined." The Date data type handles dates and time.

Another thing to be aware of, is that you probably won't catch it at just
the right instant, so you'll want your comparison to be at or after the time
desired, but limit the item to running only once. To do this, you'll also
need to see if the update had been done that day (the update may occur quite
a bit later if the computer should lock-up or the database get close for
some reason). So what you'll want to check for is the last date the update
was done. If that date is before today but not more than one day ago then
you need to see if the time has been reached yet. You will want to run the
update if the time has been reached or the date was more than one day ago
(i.e. yesterday). You can store this date in a field in the appended table
to tell what date the data was appended or you can create a table just to
hold this data. Other options would be an ini file, writing to the registry,
or a user defined property. Use DMax to get the last date.

Example:
Dim intDaysSinceUpdate As Integer
intDaysSinceUpdate = DateDiff("d", DMax("DateField", "tblDbProperties"),
Date)
If (Time >= #7:00 PM# And intDaysSinceUpdate > 0) Or intDaysSinceUpdate >1
Then
CurrentDb.Execute "QueryName", dbFailOnError
End If

 

[Richard]

Thanks Jim/Wayne you have saved me wasting a lot of time
trying to do it the wrong way !!!

Thanks

Richard

-----Original Message-----
As Jim mentioned, there is no need to query the textbox, just use the Now or
Time function in your If statement. However, You'll still need to do the
compare. There is no predefinded data type "time", if you try to Dim a
variable as Time you will get a compile error that the "User-defined data
type not defined." The Date data type handles dates and time.

Another thing to be aware of, is that you probably won't catch it at just
the right instant, so you'll want your comparison to be at or after the time
desired, but limit the item to running only once. To do this, you'll also
need to see if the update had been done that day (the update may occur quite
a bit later if the computer should lock-up or the database get close for
some reason). So what you'll want to check for is the last date the update
was done. If that date is before today but not more than one day ago then
you need to see if the time has been reached yet. You will want to run the
update if the time has been reached or the date was more than one day ago
(i.e. yesterday). You can store this date in a field in the appended table
to tell what date the data was appended or you can create a table just to
hold this data. Other options would be an ini file, writing to the registry,
or a user defined property. Use DMax to get the last date.

Example:
Dim intDaysSinceUpdate As Integer
intDaysSinceUpdate = DateDiff("d", DMax

("DateField", "tblDbProperties"),