Re: Lookup Date Table for Mising Date

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 	

		
.

Relevant Pages

  • Re: Continuing before values returned from Query?
    ... one account can have many transactions. ... The other is a query that is a summary of all the transactions. ... >>Dim mParm As Parameter ...
    (microsoft.public.access.formscoding)
  • Lookup Date Table for Mising Date
    ... Each day has multiple transactions with the same date and ... Each time the query is run it is for the past 30 days. ... is a missing date. ...
    (comp.databases.ms-access)
  • Re: Code wont work
    ... There are 42 boxes on my form with 4 text boxes each. ... pulled from a query. ... Dim FTally, RTally, BTally As Long ... It's also missing the Me in 3 statements. ...
    (microsoft.public.access.formscoding)
  • Re: Do While Loop code
    ... transactions, and you merely need to count the number of them as well as the ... I would just create a stored query from that SQL. ... Dim Days30 As Integer ... Dim Counting As Integer ...
    (microsoft.public.access.modulesdaovba)
  • RE: Breaking down imported information
    ... Single-record append query: ... ' Check if at EOF of Recordset (rsDataViaCode) ... ' if at EOF Exit Do. ... Dim rsDataViaCode As DAO.Recordset ...
    (microsoft.public.access.modulesdaovba)