Can any of the experts out there point me in the direction of some tutorials,
explanations, resources etc, on how to use the Dlookup function.

John Spencer was kind enough yesterday to help me with a problem I had with
a Dlookup function, and today I have a similar problem which I can't solve,
and finding information is pretty hit and miss.

I have posted the problem below just to get me going on my project again,
but I really want to understand exactly how this works as I don't want to
keep bothering people when I should know the answers myself.

I need to get the value of a field called 'SerialNumber' from the last row
of a datatable. I used Dmax to get the ID number for the last row, and then I
do a Dlookup using that value but it doesn't work (again!)

intUnique = DMax("[ID]", "[tblMainDataTable]")
intLastSerialNumber = DLookup("[SerialNumber]", "[tblMainDataTable]", _
"[ID]=""" & intUnique & """")

Thanks for your patience in helping.

Neil

> intUnique = DMax("[ID]", "[tblMainDataTable]")
> intLastSerialNumber = DLookup("[SerialNumber]", "[tblMainDataTable]", _
> "[ID]=""" & intUnique & """")
>
> Thanks for your patience in helping.

There seems to be no point in the above DLookup. You're asking for the ID
from
the tblMainDataTable where the ID is the equal to intUnique. Since you
already know the ID is equal to intUnique, you can just use THAT value
instead.

Tom Lake

The lookup is supposed to retrieve the value of the field called SerialNumber
(this is actually a text field as it can contain letters, and is not
sequential)

I need to do this because at a certain point in the application I need to
know the serial number of the last component worked on.

I can't 'D-Max' the serial number field as there may be a greater serial
numbered component already entered into the database, so my idea was to use
DMax to obtain the ID field (an Access Autonumbering field) and then use this
as a parameter in a Dlookup to obtain the value in the serial number field of
the last record in the table.

Have I made something that should be simple far too complicated?

Neil

"Tom Lake" wrote:

> > intUnique = DMax("[ID]", "[tblMainDataTable]")
> > intLastSerialNumber = DLookup("[SerialNumber]", "[tblMainDataTable]", _
> > "[ID]=""" & intUnique & """")
> >
> > Thanks for your patience in helping.
>
> There seems to be no point in the above DLookup. You're asking for the ID
> from
> the tblMainDataTable where the ID is the equal to intUnique. Since you
> already know the ID is equal to intUnique, you can just use THAT value
> instead.
>
> Tom Lake
>
>
>

"Neil" <(E-Mail Removed)> wrote in message
news3745FC9-478A-4DCC-8FEB-(E-Mail Removed)...
> The lookup is supposed to retrieve the value of the field called
> SerialNumber
> (this is actually a text field as it can contain letters, and is not
> sequential)
>
> I need to do this because at a certain point in the application I need to
> know the serial number of the last component worked on.
>
> I can't 'D-Max' the serial number field as there may be a greater serial
> numbered component already entered into the database, so my idea was to
> use
> DMax to obtain the ID field (an Access Autonumbering field) and then use
> this
> as a parameter in a Dlookup to obtain the value in the serial number field
> of
> the last record in the table.
>
> Have I made something that should be simple far too complicated?

No, it was my mistake. For some readon I thought you were looking up ID.

Tom Lake

Assuming that ID is a number field:

intUnique = DMax("[ID]", "[tblMainDataTable]")
intLastSerialNumber = DLookup("[SerialNumber]", "[tblMainDataTable]", _
"[ID]=" & intUnique)

Number fields are not surrounded by quote marks or any other delimiter
Text fields need quote marks
Date fields need # marks.

The way I think of the last argument in the domain functions (DLookup, DMax)
is that it is a WHERE argument without the word "Where".
So if you were building an SQL statement you would be creating the where
clause.

By the way if intUnique is defined as an integer, you may eventually run
into a problem. The Max value of an integer is 32,767 and if your ID field
ever is larger than that - Boom! It blows up. So you might consider
Dimming the value as Long instead of Integer.

As far as a tutorial, I'm not aware of any.

"Neil" <(E-Mail Removed)> wrote in message
news:F6257126-548D-41F9-ACA7-(E-Mail Removed)...
> Can any of the experts out there point me in the direction of some
> tutorials,
> explanations, resources etc, on how to use the Dlookup function.
>
> John Spencer was kind enough yesterday to help me with a problem I had
> with
> a Dlookup function, and today I have a similar problem which I can't
> solve,
> and finding information is pretty hit and miss.
>
> I have posted the problem below just to get me going on my project again,
> but I really want to understand exactly how this works as I don't want to
> keep bothering people when I should know the answers myself.
>
> I need to get the value of a field called 'SerialNumber' from the last row
> of a datatable. I used Dmax to get the ID number for the last row, and
> then I
> do a Dlookup using that value but it doesn't work (again!)
>
> intUnique = DMax("[ID]", "[tblMainDataTable]")
> intLastSerialNumber = DLookup("[SerialNumber]", "[tblMainDataTable]", _
> "[ID]=""" & intUnique & """")
>
> Thanks for your patience in helping.
>
> Neil