Another Dlookup Question

  • Thread starter Thread starter Brig
  • Start date Start date
B

Brig

I apologize if this was posted previously. I searched a string of posts
regarding dlookup but did not find the answer I am looking for.

What I am trying to do is to match 2 criterias to get the value instead of
just one.

Here is a sample:

Table xyz

A B C
1 2 3
4 5 6

On the form I got unbound controls: uA, uB, uC

If in the uA and uB matched what is on the table A and B respectively, then
it will bring the C value in the uC

If uA = 1 And uB = 2 Then uC = 3
If uA = 4 And uB = 5 Then uC = 6

Basically, this is the dlookup string I entered in the controlsource of uC

=Dlookup("C", "xyz", "[A]=[uA]" And " = [uB]")

I don't get any error at all but I don't get any values too.

Any help will be greatly appreciated.

Thank you very much.
 
You need the variables to be outside of the string:

=Dlookup("C", "xyz", "[A]=" & [uA] & " And = " & [uB])

If the fields were text, you'd need to use quotes:

=Dlookup("C", "xyz", "[A]='" & [uA] & "' And = '" & [uB] & "'")
or
=Dlookup("C", "xyz", "[A]=" & Chr$(34) & [uA] & Chr$(34) & " And = " &
Chr$(34) & [uB] & Chr$(34))

Finally (for the sake of completeness), if the fields were dates, you need
to delimit them with # characters AND they need to be in mm/dd/yyyy format
(or in some unambiguous format such as yyyy-mm-dd or dd mmm yyyy):

=Dlookup("C", "xyz", "[A]=" & Format([uA], "\#mm\/dd\/yyyy\#") & " And =
" & Format([uB], "\#mm\/dd\/yyyy\#"))
 
Yes they were texts. So probably I am missing the quotes.

I will give your suggestion a shot and will post the results.

Thanks again.



Douglas J. Steele said:
You need the variables to be outside of the string:

=Dlookup("C", "xyz", "[A]=" & [uA] & " And = " & [uB])

If the fields were text, you'd need to use quotes:

=Dlookup("C", "xyz", "[A]='" & [uA] & "' And = '" & [uB] & "'")
or
=Dlookup("C", "xyz", "[A]=" & Chr$(34) & [uA] & Chr$(34) & " And = " &
Chr$(34) & [uB] & Chr$(34))

Finally (for the sake of completeness), if the fields were dates, you need
to delimit them with # characters AND they need to be in mm/dd/yyyy format
(or in some unambiguous format such as yyyy-mm-dd or dd mmm yyyy):

=Dlookup("C", "xyz", "[A]=" & Format([uA], "\#mm\/dd\/yyyy\#") & " And
=
" & Format([uB], "\#mm\/dd\/yyyy\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Brig said:
I apologize if this was posted previously. I searched a string of posts
regarding dlookup but did not find the answer I am looking for.

What I am trying to do is to match 2 criterias to get the value instead
of
just one.

Here is a sample:

Table xyz

A B C
1 2 3
4 5 6

On the form I got unbound controls: uA, uB, uC

If in the uA and uB matched what is on the table A and B respectively, then
it will bring the C value in the uC

If uA = 1 And uB = 2 Then uC = 3
If uA = 4 And uB = 5 Then uC = 6

Basically, this is the dlookup string I entered in the controlsource of
uC

=Dlookup("C", "xyz", "[A]=[uA]" And " = [uB]")

I don't get any error at all but I don't get any values too.

Any help will be greatly appreciated.

Thank you very much.
Click to expand...
Click to expand...
 
=Dlookup("C", "xyz", "[A]='" & [uA] & "' And = '" & [uB] & "'")

The above worked for what I am trying to do. I just needed to use proper
quotations.

Thank you very much Doug.

Brig


Douglas J. Steele said:
You need the variables to be outside of the string:

=Dlookup("C", "xyz", "[A]=" & [uA] & " And = " & [uB])

If the fields were text, you'd need to use quotes:

=Dlookup("C", "xyz", "[A]='" & [uA] & "' And = '" & [uB] & "'")
or
=Dlookup("C", "xyz", "[A]=" & Chr$(34) & [uA] & Chr$(34) & " And = " &
Chr$(34) & [uB] & Chr$(34))

Finally (for the sake of completeness), if the fields were dates, you need
to delimit them with # characters AND they need to be in mm/dd/yyyy format
(or in some unambiguous format such as yyyy-mm-dd or dd mmm yyyy):

=Dlookup("C", "xyz", "[A]=" & Format([uA], "\#mm\/dd\/yyyy\#") & " And
=
" & Format([uB], "\#mm\/dd\/yyyy\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Brig said:
I apologize if this was posted previously. I searched a string of posts
regarding dlookup but did not find the answer I am looking for.

What I am trying to do is to match 2 criterias to get the value instead
of
just one.

Here is a sample:

Table xyz

A B C
1 2 3
4 5 6

On the form I got unbound controls: uA, uB, uC

If in the uA and uB matched what is on the table A and B respectively, then
it will bring the C value in the uC

If uA = 1 And uB = 2 Then uC = 3
If uA = 4 And uB = 5 Then uC = 6

Basically, this is the dlookup string I entered in the controlsource of
uC

=Dlookup("C", "xyz", "[A]=[uA]" And " = [uB]")

I don't get any error at all but I don't get any values too.

Any help will be greatly appreciated.

Thank you very much.
Click to expand...
Click to expand...
 
Back
Top