By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,749 Members | 1,627 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,749 IT Pros & Developers. It's quick & easy.

Lookup Date Table for Mising Date

P: n/a
Each day a user is supposed to import a list of information into a
database table. The user only has 30 days to import this information.
After 30 days the information is lost. I need to be able to create a
form or a report or something that a user would be able to review and
show that a day was missed doing the import (indicating they forgot to
import that days information).

The table the information is being imported into is called
tblTransaction. One of the fields of the table is TxDate which is a
date field. Each day has multiple transactions with the same date and
a duplicates Ok for the TXDate field. I can run a query and get the
days that have transactions and the number of transactions for that
day. I use todays date and the date function that gives me today's
date -30 days. Each time the query is run it is for the past 30 days.
This works fine but the user has to look down to query to see if there
is a missing date.

I would create a table to denote the import was done at the time of
the import but sometimes the import data is for multiple days and the
import is not always done on the same date as the transaction date.

I am sure there is a way to just print or display the missing days but
I just cannot seem to make it happen.

Is there someone out there that would help?

Thanks
Jan 7 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
www.ttdown.com wrote:
Each day a user is supposed to import a list of information into a
database table. The user only has 30 days to import this information.
After 30 days the information is lost. I need to be able to create a
form or a report or something that a user would be able to review and
show that a day was missed doing the import (indicating they forgot to
import that days information).

The table the information is being imported into is called
tblTransaction. One of the fields of the table is TxDate which is a
date field. Each day has multiple transactions with the same date and
a duplicates Ok for the TXDate field. I can run a query and get the
days that have transactions and the number of transactions for that
day. I use todays date and the date function that gives me today's
date -30 days. Each time the query is run it is for the past 30 days.
This works fine but the user has to look down to query to see if there
is a missing date.

I would create a table to denote the import was done at the time of
the import but sometimes the import data is for multiple days and the
import is not always done on the same date as the transaction date.

I am sure there is a way to just print or display the missing days but
I just cannot seem to make it happen.

Is there someone out there that would help?

Thanks


In the form that calls the query (I'll assume you call the query from a
form), I'll assume you have a FromDate (starting date) for the query.
Using a SQL statement similar to your update query (one that selects the
records, not updates) select the dates and group on date. Now loop
through the set. Aircode follows....btw, it may be 29 instead of
30...you decide.
Dim strMsg As String
Dim datFor As Date
Dim blnMissing As Boolean
Dim rst As Recordset
Dim strSQL As String
strSQL = "Select TXDate From TX Where TXDate Between #" & _
Me.FromDate & "# And #" & Me.FromDate + 30 & "# " & _
"Group By TXDate"
set rst = Currentdb.openrecordset(strSQL,dbopensnapshot)
'go through all 30 dates
For datFor = Me.FromDate To Me.FromDate + 30
rst.findfirst "TXDate = #" & datFor & "#"
If rst.Nomatch then
blnMissing = True
strMsg = strMsg & datFor & ", "
endif
next

'disable button to execute query if missing dates
Me.CommandButtonToExecuteQuery.Enabled = (Not blnMissing)

If blnMissing then
msgbox "The following dates; " & strMsg & _
" are missing. Fix first"
else
msgbox "All dates were found. You can update."
endif
Jan 7 '06 #2

P: n/a
Sorry for this rather late reply, Salad, but it might be faster to
execute a Count Query against the Grouped query to return how many
different dates there are. If that count doesn't equal the number of
days in the month (or number of "work days", however *that* is
determined).

That way there's no real need to walk through each record, or each
group of records for each date) unless there is a problem.

Oh, also, I'd recommend that these queries be "Stored", so that Access
can optimize them.

Ya'll Have Fun!

Jan 10 '06 #3

P: n/a
Chuck Grimsby wrote:
Sorry for this rather late reply, Salad, but it might be faster to
execute a Count Query against the Grouped query to return how many
different dates there are. If that count doesn't equal the number of
days in the month (or number of "work days", however *that* is
determined).

That way there's no real need to walk through each record, or each
group of records for each date) unless there is a problem.

Oh, also, I'd recommend that these queries be "Stored", so that Access
can optimize them.

Ya'll Have Fun!

Good solution. Yes, yours would be quicker. With mine I wanted the
user to see what dates were missing if there were any and I didn't see
any obvious way by determining them except by looping thru the list.
Jan 10 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.