DlookUp Help / question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Russ - 09 Sep 2005 12:15 GMT

What am I doing wrong?
Want to look up field [DeviceAAT] in table tblDevices where field [DCode]
from that table = MyDevice

Dim MyDevice as string
Dim X As String
X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = 'MyDevice' ")

Reply to this Message

Douglas J Steele - 09 Sep 2005 13:00 GMT

You don't appear to be doing anything wrong. What problem are you running
into?

You probably should declare X as a Variant, rather than a string, since
DLookup will return Null if it can't find the record, and Variants are the
only data types that can hold a Null value.

I also don't see why you've declare MyDevice as a string variable. Are you
expecting a value to be in MyDevice, and that's what you want to be looking
up, rather than the literal 'MyDevice'? If so,

X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = '" & MyDevice & "'")

Signature

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

> What am I doing wrong?
> Want to look up field [DeviceAAT] in table tblDevices where field [DCode]
[quoted text clipped - 3 lines]
> Dim X As String
> X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = 'MyDevice' ")

Reply to this Message

Russ - 09 Sep 2005 13:19 GMT

Ok, i was missing the & sign, everything works now.
Thanks
If I make X a varient, what happens if MyDevice is null?
What does it do?

>You don't appear to be doing anything wrong. What problem are you running
>into?
[quoted text clipped - 14 lines]
>> Dim X As String
>> X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = 'MyDevice' ")

Reply to this Message

Klatuu - 09 Sep 2005 14:29 GMT

If MeDevice is Null and there is a record in your table where [Dcode] is
Null, it will return that record. What Doug is saying is that DLookUp
returns Null when there are no matching records. So if x is a string
variable, you will get an error. So what you want to do it to declare x as a
Variant and after your DLookUp, check to see if IsNull(x). That will tell
you that no matching record was found.

> Ok, i was missing the & sign, everything works now.
> Thanks
[quoted text clipped - 19 lines]
> >> Dim X As String
> >> X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = 'MyDevice' ")

Reply to this Message

Russ - 09 Sep 2005 16:55 GMT

What if I want to add one more criteria, would I just use the And?
The other field is a yes or no box...
Example
X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = '" & MyDevice & "' And
[other field] = true")
Thanks in advance for everyones help!

Reply to this Message

Klatuu - 09 Sep 2005 17:01 GMT

that should work

> What if I want to add one more criteria, would I just use the And?
> The other field is a yes or no box...
[quoted text clipped - 21 lines]
> >> Dim X As String
> >> X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = 'MyDevice' ")

Reply to this Message

Russ - 09 Sep 2005 18:24 GMT

Should I use the word TRUE or -01?

>that should work
>
[quoted text clipped - 3 lines]
>> >> Dim X As String
>> >> X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = 'MyDevice' ")

Reply to this Message

Klatuu - 09 Sep 2005 18:32 GMT

I would use the word True
If this is not working:
X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = '" & MyDevice & "' And
[other field] = true")
Try it this way:
X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = '" & MyDevice & "' And
[other field] = " & True)

> Should I use the word TRUE or -01?
>
[quoted text clipped - 5 lines]
> >> >> Dim X As String
> >> >> X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = 'MyDevice' ")

Reply to this Message

Russ - 09 Sep 2005 23:59 GMT

Ok, how would it go if I want to lookup two fields from the table? "
[DeviceAAT]" And "[DeviceATD]"
Like this?
X = DLookup("[DeviceAAT]"And "[DeviceATD]", "tblDevices", "[DCode] = '" &
MyDevice & "' )

Can I then do a Select case on each?

Select case [DeviceAAT]
Case ...
end eselct

Select case [DeviceATD]
Case ...
end eselct

>I would use the word True
>If this is not working:
[quoted text clipped - 9 lines]
>> >> >> Dim X As String
>> >> >> X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = 'MyDevice' ")

Reply to this Message

Douglas J. Steele - 10 Sep 2005 00:11 GMT

You can use

X = DLookup("[DeviceAAT] & """" """" & [DeviceATD]", "tblDevices", "[DCode]
= '" & MyDevice & "' )

That will return the two fields with a space between them.

Signature

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

> Ok, how would it go if I want to lookup two fields from the table? "
> [DeviceAAT]" And "[DeviceATD]"
[quoted text clipped - 25 lines]
>>> >> >> Dim X As String
>>> >> >> X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = 'MyDevice' ")

Reply to this Message

Russ - 10 Sep 2005 00:22 GMT

Thanks, but Can I then do a Select case on each one then?

Select case [DeviceAAT]
Case ...
end eselct

Select case [DeviceATD]
Case ...
end eselct

>You can use
>
[quoted text clipped - 8 lines]
>>>> >> >> Dim X As String
>>>> >> >> X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = 'MyDevice' ")

Reply to this Message

Russ - 10 Sep 2005 00:27 GMT

Not working... :-(

>You can use
>
[quoted text clipped - 8 lines]
>>>> >> >> Dim X As String
>>>> >> >> X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = 'MyDevice' ")

Reply to this Message

Douglas J. Steele - 10 Sep 2005 11:56 GMT

I kinda went overboard on the quotes!

X = DLookup("[DeviceAAT] & "" "" & [DeviceATD]", "tblDevices", "[DCode] = '"
& MyDevice & "' )

should do it.

As to your other questions, you could put something other than a space
between them, and then split them into their component parts:

X = DLookup("[DeviceAAT] & "";"" & [DeviceATD]", "tblDevices", "[DCode] = '"
& MyDevice & "' )

varValues = Split(X, ";")

varValues(0) will now be the contents of [DeviceAAT], while varValues(1)
will be the contents of [DeviceATD]

Signature

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

> Not working... :-(
>
[quoted text clipped - 12 lines]
>>>>> >> >> X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = 'MyDevice'
>>>>> >> >> ")

Reply to this Message

Russ - 10 Sep 2005 11:55 GMT

Douglas X = DLookup("[DeviceAAT] & """" """" & [DeviceATD]", "tblDevices", "
[DCode] '" & MyDevice & "' )

Does not work, please help.

>You can use
>
[quoted text clipped - 8 lines]
>>>> >> >> Dim X As String
>>>> >> >> X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = 'MyDevice' ")

Reply to this Message

Nikos Yannacopoulos - 09 Sep 2005 13:05 GMT

Russ,

Your expression is actually looking for string MyDevice in the DCode
field. Is this what you want? I would have guessed you are assigning a
value to variable MyDevice prior to the lookup (not shown in your code
here), in which case the syntax should be:

X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = '" & MyDevice & "'")

so MyDevice is treated like a variable rather than a text string.

HTH,
Nikos

Reply to this Message