Dlookup tutorial.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
Click to expand...

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 said:
intUnique = DMax("[ID]", "[tblMainDataTable]")
intLastSerialNumber = DLookup("[SerialNumber]", "[tblMainDataTable]", _
"[ID]=""" & intUnique & """")

Thanks for your patience in helping.
Click to expand...

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
Click to expand...
 
Neil said:
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?
Click to expand...

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.
 
Back
Top