Problem(s) with DLookUp

I am having problems getting DLookUp to work when variables are
involved. The following expression works fine; until I try to
substitute the variable SVID in place of the number 332?

SiteID = DLookup("[ID]", "SiteTBL", "[Service Volume ID]=332")

SiteID is dimensioned as a Variant
[ID] and [Service Volume ID] is a number fields is SiteTBL table.

Bob

As you typed it, the DLookup() function is using the literal value of 332
for it's where clause.

I suspect your use of SVID as a direct replacement for 332 is causing Access
to try to find a [Service Volume ID] = , literally, SVID. Since [Service
Volume ID] is a number field, are you getting a 'type mismatch' error?

(check Access HELP for the syntax and examples using DLookup())

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


"BobC" wrote in message
...
I am having problems getting DLookUp to work when variables are involved.
The following expression works fine; until I try to substitute the variable
SVID in place of the number 332?

SiteID = DLookup("[ID]", "SiteTBL", "[Service Volume ID]=332")

SiteID is dimensioned as a Variant
[ID] and [Service Volume ID] is a number fields is SiteTBL table.

Hello Bob!

Try this:

SiteID = DLookup("[ID]", "SiteTBL", "[Service Volume ID] = " & Me!SVID

Steve




"BobC" wrote in message
...
I am having problems getting DLookUp to work when variables are involved.
The following expression works fine; until I try to substitute the variable
SVID in place of the number 332?

SiteID = DLookup("[ID]", "SiteTBL", "[Service Volume ID]=332")

SiteID is dimensioned as a Variant
[ID] and [Service Volume ID] is a number fields is SiteTBL table.

No luck?
The examples I have found are also giving me problems ... maybe because
SVID is Dim As Integer?

Steve wrote:
Hello Bob!

Try this:

SiteID = DLookup("[ID]", "SiteTBL", "[Service Volume ID] = "& Me!SVID

Steve




wrote in message
...
I am having problems getting DLookUp to work when variables are involved.
The following expression works fine; until I try to substitute the variable
SVID in place of the number 332?

SiteID = DLookup("[ID]", "SiteTBL", "[Service Volume ID]=332")

SiteID is dimensioned as a Variant
[ID] and [Service Volume ID] is a number fields is SiteTBL table.

You mean SVID is simply a variable in your code, as opposed to a control on
your form?

SiteID = DLookup("[ID]", "SiteTBL", "[Service Volume ID] = "& SVID)

Note that this will only work if SiteID is defined as a Variant, since
DLookup will return Null if it cannot find a record corresponding to the
SVID value provided and Variants are the only data type that can hold Null
values. If you'd settle for having a SiteID of 0 if one isn't found, you
could declare SiteID as a specific type (Integer or Long), and then use

SiteID = Nz(DLookup("[ID]", "SiteTBL", "[Service Volume ID] = "& SVID), 0)


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"BobC" wrote in message
...
No luck?
The examples I have found are also giving me problems ... maybe because
SVID is Dim As Integer?

Steve wrote:
Hello Bob!

Try this:

SiteID = DLookup("[ID]", "SiteTBL", "[Service Volume ID] = "& Me!SVID

Steve




wrote in message
...
I am having problems getting DLookUp to work when variables are
involved.
The following expression works fine; until I try to substitute the
variable
SVID in place of the number 332?

SiteID = DLookup("[ID]", "SiteTBL", "[Service Volume ID]=332")

SiteID is dimensioned as a Variant
[ID] and [Service Volume ID] is a number fields is SiteTBL table.

Yes, SVID is a variable in my code.
In your example, does it matter if SVID is a Variant, Integer or String?

Douglas J. Steele wrote:
You mean SVID is simply a variable in your code, as opposed to a control on
your form?

SiteID = DLookup("[ID]", "SiteTBL", "[Service Volume ID] = "& SVID)

Note that this will only work if SiteID is defined as a Variant, since
DLookup will return Null if it cannot find a record corresponding to the
SVID value provided and Variants are the only data type that can hold Null
values. If you'd settle for having a SiteID of 0 if one isn't found, you
could declare SiteID as a specific type (Integer or Long), and then use

SiteID = Nz(DLookup("[ID]", "SiteTBL", "[Service Volume ID] = "& SVID), 0)

Yes it does matter.

First I would probably declare SVID as a LONG and not an integer since the
largest integer is 64K in size.

If SVID is a variant then you could have a problem is you didn't populate it
with some value.

If SVID is a string then you should not have a problem (unless you fail to
assign it a value).

If Service Volume ID is a number then you have a valid statement.

If Service Volume ID is a string then you need to include quote marks around
the SVID when you use it in the expression.

SiteID = NZ(DLookup("[ID]", "SiteTBL", "[Service Volume ID] = """ & SVID &
""""),0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

BobC wrote:
Yes, SVID is a variable in my code.
In your example, does it matter if SVID is a Variant, Integer or String?

Douglas J. Steele wrote:
You mean SVID is simply a variable in your code, as opposed to a
control on
your form?

SiteID = DLookup("[ID]", "SiteTBL", "[Service Volume ID] = "& SVID)

Note that this will only work if SiteID is defined as a Variant, since
DLookup will return Null if it cannot find a record corresponding to the
SVID value provided and Variants are the only data type that can hold
Null
values. If you'd settle for having a SiteID of 0 if one isn't found, you
could declare SiteID as a specific type (Integer or Long), and then use

SiteID = Nz(DLookup("[ID]", "SiteTBL", "[Service Volume ID] = "&
SVID), 0)

John,
I certainly want to thank you for explaining it!
To date, I have been using 'trial and error(s!!!!!!!!!!!!!!!!!!)
Thanks Much!
Bob

John Spencer wrote:
Yes it does matter.

First I would probably declare SVID as a LONG and not an integer since
the largest integer is 64K in size.

If SVID is a variant then you could have a problem is you didn't
populate it with some value.

If SVID is a string then you should not have a problem (unless you fail
to assign it a value).

If Service Volume ID is a number then you have a valid statement.

If Service Volume ID is a string then you need to include quote marks
around the SVID when you use it in the expression.

SiteID = NZ(DLookup("[ID]", "SiteTBL", "[Service Volume ID] = """ & SVID
& """"),0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

BobC wrote:
Yes, SVID is a variable in my code.
In your example, does it matter if SVID is a Variant, Integer or String?

Douglas J. Steele wrote:
You mean SVID is simply a variable in your code, as opposed to a
control on
your form?

SiteID = DLookup("[ID]", "SiteTBL", "[Service Volume ID] = "& SVID)

Note that this will only work if SiteID is defined as a Variant, since
DLookup will return Null if it cannot find a record corresponding to the
SVID value provided and Variants are the only data type that can hold
Null
values. If you'd settle for having a SiteID of 0 if one isn't found, you
could declare SiteID as a specific type (Integer or Long), and then use

SiteID = Nz(DLookup("[ID]", "SiteTBL", "[Service Volume ID] = "&
SVID), 0)