Date Extraction

[robk5787]

What I am doing is creating a database to keep track of our scanner inventory from 10 different conveyor lines with 10 different scanner positions. We change out a few scanners on each of these lines with new ones weekly. I have developed a table for each conveyor line, with a form pointing directly to each table. I also used a Lookup box for each of the 10 scanner positions. I need to create a query that would display the most current scanner information for each scanner position and conveyor line using the date. The query I am trying to create has four columns. One for the scanner position number, second being serial# for scanner, third being who changed it out, and fourth being the date installed. I used the Like "" expression to select each scanner, but I could not get the query to extract the most current entry by date. I know this is asking a bit, but I would appreciate any help you could offer. I have tried the MAX(Date) expression, but I get a error. PLEASE HELP!

 

[Sohaila Taravati]

Group your query and then choose Max or First.

 

[robk5787]

Query

I think I understand what you are speaking of. Just to make sure , when you say group I should click the totals feature. Then in the criteria tab type Max(Date). Is this correct?

 

[sambo]

Sort by Date (Descending) then by Scanner (Ascending), this will sort the sort the scanners by date. I believe this what was meant by grouping.

You can then use the Dlookup function to get the first Date.

In your VB Code try this

Dlookup("[Date]", "yourQueryNameHere", "[Scanner] = '" & Me.scannerControl & "'")

This should lookup the top date in the query, for any given scanner.

Which scanner depends on what value is entered in the scannerControl box (or whatever you call it) on your form.

Let me know if this is not what you are looking for

 

[robk5787]

Thanks

Thank for your help. I will try this!

 

[Jon K]

If you need to show all the scanners, try this query (type/paste in the SQL View of a new query, replacing with the correct table name and field names):-

SELECT *
FROM [TableName] AS a
WHERE [Date Installed] = (Select Max([Date Installed]) from [TableName] where [Scanner Position No]=a.[Scanner Position No]);


Run the query.

 

[robk5787]

Query

Thanks everyone for your help. I have used the infrmation you gave me to branch out and use it for several other applications I am currently working on. As a beginner this is the information that allows you to focus your thoughts and build new ways to do other features using slightly different code. Thanks again!