Query for only the last record entered

[Harold via AccessMonster.com]

Can I make a query to find info only from the last record entered? I need to
use that record in an expression.

Thanks
Harold

 

[PC Datasheet]

If you have an autonumber for the primary key, the last record entered will
have the largest value for the primary key and you can use the DMax
function.

 

[Harold via AccessMonster.com]

Thank you, I will try it

If you have an autonumber for the primary key, the last record entered will
have the largest value for the primary key and you can use the DMax
function.

 

[Harold via AccessMonster.com]

This is what Im doing :
=[tblSIP Subform].[Form]![InspectionDate]+(365*3)
I need to apply this to the last record in the form. I have an autonumber
field called SIPID with these records. How could I use the Dmax function, P.S.
Im not very good with VB.

Thanks
Harold

 

[PC Datasheet]

This post and your original post seem to conflict! Oroginally, you said
"find info only from the last record entered". Here you say you want to
apply the expression to the last record in the form". Please clarify.

 

[Harold via AccessMonster.com]

Sorry,
What I want to do is find the last record entered, and then add 3 years to a
field in that record.

I think I can somehow get the record with the highest autonumber (primary key)
and then add an expresion to a date field in that record. What Im tring to do
is, I have a table where I put in inspection dates, I need to add three years
on to whatever the last inspection date was to come up with a inspection date
for next time.

Sorry, Its hard to explain.

Thanks
Harold

This post and your original post seem to conflict! Oroginally, you said
"find info only from the last record entered". Here you say you want to
apply the expression to the last record in the form". Please clarify.

This is what Im doing :
=[tblSIP Subform].[Form]![InspectionDate]+(365*3)

[quoted text clipped - 5 lines]

Thanks
Harold

 

[Douglas J. Steele]

If what you want is the record with the latest date, you're better off
querying the date field itself, rather than the AutoNumber field.
Realistically, it's bad practice to rely on the meaning of an AutoNumber
field. For example, should you ever replicate your database, the AutoNumber
field will become a random number, rather than a sequential one, so your
code would no longer function as desired.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Sorry,
What I want to do is find the last record entered, and then add 3 years to
a
field in that record.

I think I can somehow get the record with the highest autonumber (primary
key)
and then add an expresion to a date field in that record. What Im tring to
do
is, I have a table where I put in inspection dates, I need to add three
years
on to whatever the last inspection date was to come up with a inspection
date
for next time.

Sorry, Its hard to explain.

Thanks
Harold

This post and your original post seem to conflict! Oroginally, you said
"find info only from the last record entered". Here you say you want to
apply the expression to the last record in the form". Please clarify.

This is what Im doing :
=[tblSIP Subform].[Form]![InspectionDate]+(365*3)

[quoted text clipped - 5 lines]

Thanks
Harold

 

[PC Datasheet]

Your question now seems to have a different meaning than your original
question. It sounds like you have something like equipment amd each
equipment item gets inspected. You have a table to record the inspections
and one of the fields is the inspection date. A table like:
TblEquipmentInspection
EquipmentInspectionID
EquipmentID
InspectionDate
<Other inspection fields>

If this is the case, you should not be entering the date for the next
inspection. Rather you need a query that lists the equipment where an
inspection is due as of a certain date. The date could be today or some
specified date. The query would return a list of equipment where the last
inspection date of that item of equipment is more than three years before
the date you specify.

Base your query on TblEquipmentInspection. Put the following expression in
the criteria of the InspectionDate field:
(Select Max([InspectionDate]) From TblEquipmentInspection As T Where
T.EquipmentID = TblEquipmentInspection.EquipmentID)
This query returns the last inspection date for each piece of equipment.
Now, in a blank field in the query, add this expression:
NextInspectionDueDateateAdd("yyyy",3,[InspectionDate])
Run the query again and this field returns the next inspection date.
Finally, add this expression in the criteria of NextInspectionDueDate:
<=[Enter A Date To Find The Equipment Where An Inspection Is Due On Or
Before The Date]
The query will now return a list of equipment where inspection is due on or
before the date you entered.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com

Sorry,
What I want to do is find the last record entered, and then add 3 years to
a
field in that record.

I think I can somehow get the record with the highest autonumber (primary
key)
and then add an expresion to a date field in that record. What Im tring to
do
is, I have a table where I put in inspection dates, I need to add three
years
on to whatever the last inspection date was to come up with a inspection
date
for next time.

Sorry, Its hard to explain.

Thanks
Harold

This post and your original post seem to conflict! Oroginally, you said
"find info only from the last record entered". Here you say you want to
apply the expression to the last record in the form". Please clarify.

This is what Im doing :
=[tblSIP Subform].[Form]![InspectionDate]+(365*3)

[quoted text clipped - 5 lines]

Thanks
Harold

 

[Harold via AccessMonster.com]

Thanks,
That worked great! , untill I got to the part where I put this; <=[Enter A
Date To Find The Equipment Where An Inspection Is Due On Or Before The Date]
into the criteria of NextInspectionDueDate. After I did that I got no
information in any of the fields no matter what date I put in.

Thanks again for the help

Harold

 

[PC Datasheet]

The dates you tried, were they after at least one of the
NextInspectionDueDates?

Check the criteria, did you use square brackets and not parantheses?

Try a shorter criteria:
<= [Enter A Date]

Remove the criteria and run your query and check out:
1. Are the dates in the InspectionDate field the last inspection dates?
2. Do you have dates in the NextInspectionDueDate? Are they all 3 years
later than the dates in the InspectionDate field?

 

[Harold via AccessMonster.com]

In the inspectionDate field I put this in the criteria - (Select Max(
[InspectionDate]) From TblSIP As T Where
T.BusinessID = TblSIP.BusinessID)

Then I have this exprestion in the next column - NextInspectionDueDate:
DateAdd("yyyy",3,[InspectionDate])

Both of these work fine.

When I run the query it returns the last inspection and the next due with 3
years added. When I add the criteria to the second collumns criteria field
asking for a date I get two blank fields after running. No matter what date I
put in.

This is what it looks like in SQL -
SELECT tblSIP.InspectionDate, DateAdd("yyyy",3,[InspectionDate]) AS
NextInspectionDueDate
FROM tblSIP
WHERE (((tblSIP.InspectionDate)=(Select Max([InspectionDate]) From TblSIP As
T Where
T.BusinessID = TblSIP.BusinessID)) AND ((DateAdd("yyyy",3,[InspectionDate]))
<=[Enter A Date]));


Again, thanks for the help.

Harold

The dates you tried, were they after at least one of the
NextInspectionDueDates?

Check the criteria, did you use square brackets and not parantheses?

Try a shorter criteria:
<= [Enter A Date]

Remove the criteria and run your query and check out:
1. Are the dates in the InspectionDate field the last inspection dates?
2. Do you have dates in the NextInspectionDueDate? Are they all 3 years
later than the dates in the InspectionDate field?

Thanks,
That worked great! , untill I got to the part where I put this; <=[Enter A

[quoted text clipped - 6 lines]

Harold