help pulling data from a query

[Daniel M]

I have a historical service database. In it i list all serial numbers and
work done. I wrote a query that scanns the db and find the last time a
battery was changed on all units.

I need to go now and from that query, pull a particual record (lets say for
serial number 123) then evaluate the date it was done and let the user know
if the battery should be replaced again or not. Lets set the criteria at 2
months or older to be replaced.

I have a form with a text box in it where i barcode scan all the units in. I
then parse that string to find each serial number. I now need a way to look
at the query for just that number and decide it's fate. Ideally it can be
written to a temptable. then after it cycles through all the serial numbers
and looks them up i can open the temp table. then just delete it after it
closes.

Can someone give me some ideas on the pulling the data out and validating
the date? Thanks.

 

[Allen Browne]

Daniel, I wasn't sure about:
a form with a text box in it where i barcode scan all the units in

Are these all listed one after the other in one text box? It is possible to
do that, but I think it would be better to put the records into a table.

Say you have a table named "tblUnits2Check". As you scan the units in, each
serial number becomes a record in this table. It's now dead easy to create a
query that joins this table to your main table (where all the units are), so
that the query just returns the scanned units. Now you can add your criteria
under the last test date. This would give the values older than 2 months:
< DateAdd("m", -2, Date())

 

[Daniel M]

I'll have to look at that. I put the serial numers in a text box so i can
parse them out. our scanners will only scan the data not hit return so i was
trying to keep the user interaction down. I just look at the string, divide
it by my serial number length to verify if it is right, then take 9 charaters
into a varialble and just keep looping though taking 9 digits at a time.
seems to work so far, been using it for other stuff for a while now.

I can easily put a command button that when clicked parses the sn and writes
it to a temp table. i'll have to look at that. so how would i join the tables?

 

[Allen Browne]

Sounds like you already have a loop that parses the tables, so you would:
Dim db As DAO.Database
Dim strSql As String
Set db = CurrentDb
Do
strSql = "INSERT INTO ...
db.Execute strSql, dbFailOnError
'get your next number
Loop

To create the query with the inner join, just use the graphical query
designer, with the 2 tables joined on the desired field.

To clear the temp table again before the next group of inputs:
db.Execute "DELETE FROM Table99;", dbFailOnError