Dlookup()

S

SOS

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!
 
D

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.
 
S

SOS

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!



Douglas J Steele said:
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!)


SOS said:
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!
 
G

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.
 
S

SOS

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

George Nicholson said:
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.


SOS said:
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!
 
D

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!)



SOS said:
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!



Douglas J Steele said:
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!)


SOS said:
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!
 
G

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.



SOS said:
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

George Nicholson said:
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.


SOS said:
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!
 
S

SOS

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

George Nicholson said:
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.



SOS said:
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

George Nicholson said:
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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top