Dlookup returns WRONG entries

[thisisntwally]

Im using variations of the following to pull documents associated with a certain record, marked 'A' 'B' etc. they all pull up the 'A' documents, so i suspect the AND is not working.

What am i doing wrong?

=DLookUp("[SupDoc] ","[tbl SupDoc]","keyID = Me!keyID" And "Var = 'A'")

=DLookUp("[SupDoc] ","[tbl SupDoc]","keyID = Me!keyID" And "Var = 'B'")

and so on...

 

[rainman89]

http://www.mvps.org/access/general/gen0018.htm

look there. you are using the and wrong as you suspected

 

[thisisntwally]

RayRay - you rule!

 

[rainman89]

haha..
glad that helps

 

[thisisntwally]

while it helps, i am still an incompetant buffoon.

= DLookUp("[SupDoc] ","[tbl SupDoc]","keyID = " & Me!keyID [COLOR="Red"]&[/COLOR]  _ & " AND Var = '" & A & "'")

apparently my syntax is still lousy. the page implied didn't show the red ampersand, but it doesn't work either way.

io sono retardo

 

[Rabbie]

Try

= DLookUp("[SupDoc] ","[tbl SupDoc]","keyID = " & Me!keyID & " AND Var = 'A')"

 

[thisisntwally]

as written, it returns 'the expression you entered is missing a closing parenthesis, bracket or verical bar.

with the closing parenthesis outside the quotes, the syntax error returns...



edit: = DLookup("[SupDoc]", "[tbl SupDoc]", "KeyId = " & Me!keyID _ & " AND "Var = 'A'") doesnt work either, its apparently not the underscore.

 

[rainman89]

so this

= DLookUp("[SupDoc] ","[tbl SupDoc]","keyID = " & Me!keyID & " AND Var = 'A'")

doesnt work? it should

 

[thisisntwally]

ok, the code now sticks, w/o error. i must have been trying to apply it it datasheet view.

the new problem is, unlike the faulty original code, this returns no results...

 

[rainman89]

stupid question but..... you are sure there are records that match this exact query?

 

[thisisntwally]

haha, there are no stupid questions.

just questions posed to stupid people (me)

the ONLY entry, is an exact match. I was just double checking. as its time for me to head home (there is 1 reason to get in at 7:00am) and as i have had a productive day overall, i will put off obsessing over this until tomorrow.

 

[rainman89]

troubleshoot see if it is retrieving one and not the other. break the dlookup in half. search for one part at a time. if both are being returned then there is something else going fishy. enjoy your nite. im out in an hour.... grr

 

[unclejoe]

Hi,

One missing "&" ampersand from the Dlookup.

= DLookup("[SupDoc]", "[tbl SupDoc]", "KeyId = " & Me!keyID & " AND " & "Var = 'A'")

as written, it returns 'the expression you entered is missing a closing parenthesis, bracket or verical bar.
with the closing parenthesis outside the quotes, the syntax error returns...

edit: = DLookup("[SupDoc]", "[tbl SupDoc]", "KeyId = " & Me!keyID _ & " AND "Var = 'A'") doesnt work either, its apparently not the underscore.

 

[thisisntwally]

Thank you unclejoe, rainman and rabble

=DLookUp("[SupDoc]","[tbl SupDoc]","KeyId = " & [Forms]![Summery]![keyID] & " AND " & "Var = 'A'")

the ampersand alone didn't do it, it took an explicit reference to the form to make this fly.

 

[rainman89]

Thank you unclejoe, rainman and rabble

=DLookUp("[SupDoc]","[tbl SupDoc]","KeyId = " & [Forms]![Summery]![keyID] & " AND " & "Var = 'A'")

the ampersand alone didn't do it, it took an explicit reference to the form to make this fly.

makes sense. thought this was in the form itself thats why me! should have worked. i just "learnt" something new

 

[thisisntwally]

it is in the same form.