query to check for data

[mike]

Hi All
I need to write a query that checks the records in my database to see how
log ago a specific event too place. Depending on the length of time between
now and the date recorded in the database I need to trigger another query or
do nothing.

I have a table of events with the last date recorded. I have a query that
looks for the date difference between now and the previous recorded event.
I am not sure how to get the date difference as an integer from the query.
At the moment I am doing an event procedure:-

Dim strWeekDate As Integer
strWeekDate = Qry_Weekly_set
If strWeekDate < 6 Then
MsgBox "Do not trigger event"
Else
MsgBox "This event will trigger another query"
End If

But this does not work can someone point me in the right direction please?

Regards and TIA
Mike

 

[Dale Fye]

Mike,

First, why would you declare a variable as a string (strWeekDate) and then
define it as an integer? I'd use intWeekDate instead.

You cannot directly set the value of a variable (intWeekDate) equal to a
query (qry_Weekly_set). Assuming that qry_Weekly_Set returns only a single
record, you could use the Dlookup function with that query to get the value
of the field that is returned by that query. It might look like:

intWeekDate = dlookup("FieldName", "qry_Weekly_set")

where you replace "FieldName" with the name of the field you want from the
query.

 

[John W. Vinson]

Hi All
I need to write a query that checks the records in my database to see how
log ago a specific event too place. Depending on the length of time between
now and the date recorded in the database I need to trigger another query or
do nothing.

I have a table of events with the last date recorded. I have a query that
looks for the date difference between now and the previous recorded event.
I am not sure how to get the date difference as an integer from the query.
At the moment I am doing an event procedure:-

Dim strWeekDate As Integer
strWeekDate = Qry_Weekly_set
If strWeekDate < 6 Then
MsgBox "Do not trigger event"
Else
MsgBox "This event will trigger another query"
End If

But this does not work can someone point me in the right direction please?

Regards and TIA
Mike

You can use the builtin DateDiff() function to calculate the difference
between two dates:

DateDiff("d", [LastDateRecorded], Date())

as a calculated field in a query will be an integer number of days since that
date. You can use this for sorting, or for a Query, or in code to trigger an
event.