Find Previous Record in a Query

[teric2]

I'm sure it's been posted before but I can't find it.
How do I find the previous record in a query using the autonumber field?

Thanks in advance to anyone who helps!

 

[Uncle Gizmo]

Does your query return only one result?

 

[teric2]

No.

It looks at some fields on a form and uses a date field. I get what I want except I need the next oldest record (previous) that was entered.

SELECT resin_lot_change.silo, resin_lot_change.resin, resin_lot_change.resin_lot, resin_lot_change.Resin_lot_date, resin_lot_change.Resin_lot_time, resin_lot_change.Auto
FROM resin_lot_change
WHERE (((resin_lot_change.silo)=[text14]) AND ((resin_lot_change.Resin_lot_date)>[combo2])) OR (((resin_lot_change.Resin_lot_date)=[combo2])) OR (((resin_lot_change.Resin_lot_date)=[combo2]-1))
ORDER BY resin_lot_change.Resin_lot_date DESC;

 

[jdraw]

In plain English what exactly do you want to happen.

Are you looking for the record with the second latest .Resin_lot_date????

 

[teric2]

users are picking a date on a form that finds the matching record in the table. I have a sub form that's based on this query to show me any records that match the date selected or greater then the date selected. That works fine. I just need the query to also return the last record that was entered previous to the date selected.

That make any sense?

 

[jdraw]

Tell us about the tables involved. What are they and what do they represent?
What records exactly from which table/query do you need based on what criteria?

Form and subform normal set up is when tables are related in a 1 to Many relationship.
The 1 side populates the form and the subform has records from the Many table where the linkfields are equal.

I 'm not following your form/subform, but your descriptions of your tables may clarify things.

 

[teric2]

Dates are entered in one table "Silo Changes" to show when a machine has been changed to a different silo containing raw material. The other table "resin_lot_change" lists the silos along with the raw material in the silo and the date that silo switched to a certain raw material.
I want to find the machine and the date it was put on a certain silo and the raw material that was in that silo at the time.

the form has combo boxes to select when a machine was put on a certain silo. the sub form is linked to the main form by silo number. It's the only common field they can be joined on.
The dates in both tables are entered separately and may or may not match.
This is going to be really lengthy if I try to explain all of it.

I've got everything working if I can just get my sub form to also show the last record entered before the date that is selected in the combo box of my form and everything from that date forward.

 

[teric2]

Here's an image of my query if that helps.

 

[jdraw]

Maybe this tutorial will help with using form controls as query criteria.

 

[gemma-the-husky]

the reason for all the exploratory questions is that, in general, there is no such thing as "the previous record" when dealing with a database. Such a concept requires the data to be first sorted in a given order, which can be changed at will.

So although a previous record can be determined given a particular sort order, it is an awkward and slow thing to do. often it is better to try and avoid the concept, and just deal with the "domain totals" - ie the overall totals of all the data - if you can.

Hence

"I'm sure it's been posted before but I can't find it."

is not necessarily a given.

good luck

 

[vbaInet]

Once you've sorted out the referencing problems like jdraw mentioned, if the sort order remains the same (i.e. Resin_lot_date DESC) then you could, in a separate query and with the same sort and filter applied as before:

* add a filter that states

Resin_lot_date <= [ReferenceToDateControl] AND AutoIDField <> [ReferenceToIDControl]

* The very first record will be the "previous record" that you seek and you can then use TOP 1 to get that record.

You could also get the previous record unique ID from a recordset by applying the same criteria as before, move next, grab the ID (which is your previous record because of the DESC sorting order on the date field) and apply that ID to your query.