Dlookup()

[Guest]

In a form, I created a DLOOKUP() field. I want to find the previous record
ID. The form is based on a query and the number from the table below the
query is an autonumber so there should be no differences record to record.

The problem I am having is that the Dlookup is working fine, then for
certain series of records it does not work at all.

This is the expression I am using...
=DLookUp("[URAID]","Admin Audit Query","[URAID]=" & [URAID]-1)

Can anyone help?

Thanks!

 

[Douglas J Steele]

Be aware that Autonumbers aren't guaranteed to be sequential. If, for
example, you start to add a record and change your mind and cancel the
addition, you lose the number that would have been assigned.

 

[Guest]

Yes, I am aware. I just referenced that to say that there would not be any
variance in how the data is entered. I don't care what the number is for the
previous record, I just want to see 1 less than the previous record but for
some reason it does not work for every record.

Thanks!

Be aware that Autonumbers aren't guaranteed to be sequential. If, for
example, you start to add a record and change your mind and cancel the
addition, you lose the number that would have been assigned.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

In a form, I created a DLOOKUP() field. I want to find the previous record
ID. The form is based on a query and the number from the table below the
query is an autonumber so there should be no differences record to record.

The problem I am having is that the Dlookup is working fine, then for
certain series of records it does not work at all.

This is the expression I am using...
=DLookUp("[URAID]","Admin Audit Query","[URAID]=" & [URAID]-1)

Can anyone help?

Thanks!

 

[George Nicholson]

for certain series of records it does not work at all.
Without more specifics as to what records it doesn't work for, I can tell
you that if you delete a record, or have an aborted append function, or
other reasons that don't come to mind at the moment, that URAIDs will have
gaps in what would otherwise appear to be a sequential series. This would
cause your Dlookup to fail to return a valid ID under those not-so-special
circumstances.

If your form is sorted on URAID then I guess you could do a
RecordsetClone.MovePrevious in code and grab the URAID of that record.

 

[Guest]

Does it matter if it is sequential or not with DLookup? Maybe I don't
understand the function. I just want to use the value of the previous record
as a reference to determine formatting for a field. Because you can't do
grouping on a form, I want to force a group header, saying that if the
previous record<>current, value, "".

Sorry for the confussion...Is there an even better way to do this perhaps?

Thanks

for certain series of records it does not work at all.

Without more specifics as to what records it doesn't work for, I can tell
you that if you delete a record, or have an aborted append function, or
other reasons that don't come to mind at the moment, that URAIDs will have
gaps in what would otherwise appear to be a sequential series. This would
cause your Dlookup to fail to return a valid ID under those not-so-special
circumstances.

If your form is sorted on URAID then I guess you could do a
RecordsetClone.MovePrevious in code and grab the URAID of that record.

--
George Nicholson

Remove 'Junk' from return address.

In a form, I created a DLOOKUP() field. I want to find the previous
record
ID. The form is based on a query and the number from the table below the
query is an autonumber so there should be no differences record to record.

The problem I am having is that the Dlookup is working fine, then for
certain series of records it does not work at all.

This is the expression I am using...
=DLookUp("[URAID]","Admin Audit Query","[URAID]=" & [URAID]-1)

Can anyone help?

Thanks!

 

[Douglas J. Steele]

If there are gaps in the numbers, then there isn't always a record whose
value is 1 less than the previous record!

What you can use instead is

=DMax("[URAID]","Admin Audit Query","[URAID] < " & [URAID])


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, I am aware. I just referenced that to say that there would not be
any
variance in how the data is entered. I don't care what the number is for
the
previous record, I just want to see 1 less than the previous record but
for
some reason it does not work for every record.

Thanks!

Be aware that Autonumbers aren't guaranteed to be sequential. If, for
example, you start to add a record and change your mind and cancel the
addition, you lose the number that would have been assigned.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

In a form, I created a DLOOKUP() field. I want to find the previous record
ID. The form is based on a query and the number from the table below
the
query is an autonumber so there should be no differences record to
record.

The problem I am having is that the Dlookup is working fine, then for
certain series of records it does not work at all.

This is the expression I am using...
=DLookUp("[URAID]","Admin Audit Query","[URAID]=" & [URAID]-1)

Can anyone help?

Thanks!

 

[George Nicholson]

My point was that there can be "breaks" in autonumber sequences. Because of
that " [URAID]-1 " will refer to a number that doesn't exist
whenever you are on the 1st record following one of those "breaks".

Douglas & I are saying the same thing, but his solution is better.
=DMax("[URAID]","Admin Audit Query","[URAID] < " & [URAID])
Translation: "What is the largest value of URAID that is less than x?"
(Where x = the current URAID)

It avoids the "one less than" issue very nicely.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Does it matter if it is sequential or not with DLookup? Maybe I don't
understand the function. I just want to use the value of the previous
record
as a reference to determine formatting for a field. Because you can't do
grouping on a form, I want to force a group header, saying that if the
previous record<>current, value, "".

Sorry for the confussion...Is there an even better way to do this perhaps?

Thanks

for certain series of records it does not work at all.

Without more specifics as to what records it doesn't work for, I can tell
you that if you delete a record, or have an aborted append function, or
other reasons that don't come to mind at the moment, that URAIDs will
have
gaps in what would otherwise appear to be a sequential series. This would
cause your Dlookup to fail to return a valid ID under those
not-so-special
circumstances.

If your form is sorted on URAID then I guess you could do a
RecordsetClone.MovePrevious in code and grab the URAID of that record.

--
George Nicholson

Remove 'Junk' from return address.

In a form, I created a DLOOKUP() field. I want to find the previous
record
ID. The form is based on a query and the number from the table below
the
query is an autonumber so there should be no differences record to
record.

The problem I am having is that the Dlookup is working fine, then for
certain series of records it does not work at all.

This is the expression I am using...
=DLookUp("[URAID]","Admin Audit Query","[URAID]=" & [URAID]-1)

Can anyone help?

Thanks!

 

[Guest]

OH, now I get it!!! Thank you!

My point was that there can be "breaks" in autonumber sequences. Because of
that " [URAID]-1 " will refer to a number that doesn't exist
whenever you are on the 1st record following one of those "breaks".

Douglas & I are saying the same thing, but his solution is better.
=DMax("[URAID]","Admin Audit Query","[URAID] < " & [URAID])
Translation: "What is the largest value of URAID that is less than x?"
(Where x = the current URAID)

It avoids the "one less than" issue very nicely.

HTH,
--
George Nicholson

Remove 'Junk' from return address.

Does it matter if it is sequential or not with DLookup? Maybe I don't
understand the function. I just want to use the value of the previous
record
as a reference to determine formatting for a field. Because you can't do
grouping on a form, I want to force a group header, saying that if the
previous record<>current, value, "".

Sorry for the confussion...Is there an even better way to do this perhaps?

Thanks

for certain series of records it does not work at all.
Without more specifics as to what records it doesn't work for, I can tell
you that if you delete a record, or have an aborted append function, or
other reasons that don't come to mind at the moment, that URAIDs will
have
gaps in what would otherwise appear to be a sequential series. This would
cause your Dlookup to fail to return a valid ID under those
not-so-special
circumstances.

If your form is sorted on URAID then I guess you could do a
RecordsetClone.MovePrevious in code and grab the URAID of that record.

--
George Nicholson

Remove 'Junk' from return address.


In a form, I created a DLOOKUP() field. I want to find the previous
record
ID. The form is based on a query and the number from the table below
the
query is an autonumber so there should be no differences record to
record.

The problem I am having is that the Dlookup is working fine, then for
certain series of records it does not work at all.

This is the expression I am using...
=DLookUp("[URAID]","Admin Audit Query","[URAID]=" & [URAID]-1)

Can anyone help?

Thanks!