473,322 Members | 1,781 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Lookup Date Table for Mising Date

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
3 2161
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: fak | last post by:
I have a workorder entry form. There is a table that contains workorder information and another table that contains work dates that are closed (fully booked). When the workorder form is being...
3
by: my-wings | last post by:
I've been reading about how evil Lookup fields in tables are, but I've got to be missing something really basic. I know this subject has been covered before, because I've just spent an hour or two...
3
by: John | last post by:
Hi, I need to build a BAUDOT lookup table for an encoder. I see to use a char as the index 'A' or 'B' and get back an array of booleans (five elements long). Baudot = {00011} Baudot =...
7
by: Juris Krumins | last post by:
I have a problem with postgresql tables. periodicaly, I would say frequently about 5-10 time per hour i have such errors in my server log file: 2004-04-14 12:23:32 ERROR: cache lookup of...
3
by: jim.murphy | last post by:
I am not sure If I can do this with a lookup, but what I would like to do is perhaps use lookup to retrieve a control date from an unassociated table to control what date is entered in another...
1
by: Beeker | last post by:
I have a table called 'RawData' that collects production data. We run a report on this data everyday to see the performance of each employee. I have another table called 'tblStandards' with...
5
by: Andrus | last post by:
I'm creating a database Winforms application using VCS Express 2005 I have some large lookup tables (may be up to 500000 records) which contains name and id and are stored in sql server. I...
1
by: RXRPHRICH | last post by:
Hi I Thought I Was Fairly Savvy At Creating A Lookup Vba. I Guess My Brain Has A Block. Here Are My Parameters: Table #1 - Transaction Date Field And Price Field .Each Record Is A Unique...
1
by: joeino | last post by:
I want to do a lookup query and append the record to a history table before editing the data. I created a macro to run the lookup query to append the record to history and it works fine. I did the...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.