Re: Dlookup()

Douglas J Steele

unread,

Jun 6, 2005, 4:01:55 PM6/6/05

to

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
http://I.Am/DougSteele
(no e-mails, please!)

"SOS" <S...@discussions.microsoft.com> wrote in message
news:9660DA27-90DC-469D...@microsoft.com...
> 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!
>

SOS

unread,

Jun 6, 2005, 4:16:01 PM6/6/05

to

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!

George Nicholson

unread,

Jun 6, 2005, 4:20:54 PM6/6/05

to

> 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

unread,

Jun 6, 2005, 4:43:02 PM6/6/05

to

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

Douglas J. Steele

unread,

Jun 6, 2005, 5:29:12 PM6/6/05

to

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
http://I.Am/DougSteele
(no e-mails, please!)

"SOS" <S...@discussions.microsoft.com> wrote in message

news:1BEF4F6F-B42A-4AA0...@microsoft.com...

George Nicholson

unread,

Jun 6, 2005, 6:28:04 PM6/6/05

to

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" <S...@discussions.microsoft.com> wrote in message

news:9A003637-2D11-4BDC...@microsoft.com...

SOS

unread,

Jun 7, 2005, 8:16:02 AM6/7/05

to

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