Lookup Date Table for Mising Date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

www.ttdown.com - 07 Jan 2006 15:42 GMT

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

Reply to this Message

salad - 07 Jan 2006 16:00 GMT

> 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.
[quoted text clipped - 23 lines]
>
> 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

Reply to this Message

Chuck Grimsby - 10 Jan 2006 00:23 GMT

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!

Reply to this Message

salad - 10 Jan 2006 03:18 GMT

> 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
[quoted text clipped - 9 lines]
>
> 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.

Reply to this Message