DlookUp Help / question

[Russ via AccessMonster.com]

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' ")

 

[Douglas J Steele]

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 & "'")

 

[Nikos Yannacopoulos]

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

 

[Russ via AccessMonster.com]

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?

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 & "'")

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' ")

 

[Guest]

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
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?

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 & "'")

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' ")

 

[Russ via AccessMonster.com]

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!

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 & "'")

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' ")

 

[Guest]

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...
Example
X = DLookup("[DeviceAAT]", "tblDevices", "[DCode] = '" & MyDevice & "' And
[other field] = true")
Thanks in advance for everyones help!

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 & "'")

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' ")

 

[Russ via AccessMonster.com]

Should I use the word TRUE or -01?

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' ")

 

[Guest]

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?

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' ")

 

[Russ via AccessMonster.com]

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:
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' ")

 

[Douglas J. Steele]

You can use

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

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


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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:
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' ")

 

[Russ via AccessMonster.com]

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

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

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

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' ")

 

[Russ via AccessMonster.com]

Not working... :-(

You can use

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

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

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' ")

 

[Russ via AccessMonster.com]

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

Does not work, please help.

You can use

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

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

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' ")

 

[Douglas J. Steele]

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]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Not working... :-(

You can use

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

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

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'
")