dlookup syntax question

[vojaka]

Can please anyone suggest right syntax for criteria (marked red)? Second dlookup returns number.

Thanks for help!!!


Status = DLookup("[Company_Status]", "[tblCompanyStatus]", "[Company_StatusID] = DLookup("[Company_StatusID]", "[tblCompany]", "[Company] = '" & strSelected & "'") ")

 

[Galaxiom]

That is a tricky one. How about?

Status = DLookup("[Company_Status]", "[tblCompanyStatus]", "[Company_StatusID] = DLookup('[Company_StatusID]', '[tblCompany]', '[Company] = " & strSelected & "')")

The second level has to be done in single quotes. The location of the last single quote is a conundrum. The variable should not be inside any quotes so the the final single quote needs to be the first character inside the last double quoted string.

I'm looking forward to seeing the right answer because I'm definitely not confident about mine.

 

[stopher]

I'd go for (untested):

Status = DLookup("[Company_Status]", "[tblCompanyStatus]","[Company_StatusID] = " & DLookup("[Company_StatusID]", "[tblCompany]", "[Company] = '" & strSelected & "'") )

Chris

 

[vojaka]

That is a tricky one. How about?

Status = DLookup("[Company_Status]", "[tblCompanyStatus]", "[Company_StatusID] = DLookup('[Company_StatusID]', '[tblCompany]', '[Company] = " & strSelected & "')")

The second level has to be done in single quotes. The location of the last single quote is a conundrum. The variable should not be inside any quotes so the the final single quote needs to be the first character inside the last double quoted string.

I'm looking forward to seeing the right answer because I'm definitely not confident about mine.

hmmm, it's not working. Second level dlookup works fine. Second dlookup shows status id number on the form. First level dlookup searches for the status name in tblCompanyStatus by Company_Status ID. In original, access shows error "Data mismatch in criteria", as second level dlookup value is string, but Company_StatusID is integer. Any ideas?

 

[vojaka]

I'd go for (untested):

Status = DLookup("[Company_Status]", "[tblCompanyStatus]","[Company_StatusID] = " & DLookup("[Company_StatusID]", "[tblCompany]", "[Company] = '" & strSelected & "'") )

Chris

doesn't work. (((

 

[Galaxiom]

Use the Val() function to return a number from the string.
Or the Str() function to convert the numer to a string.

Usually though your fields to be tested in this situation should be the same format in all your tables.

 

[vojaka]

Finally problem is solved.

Status = DLookup("[Company_Status]", "[tblCompanyStatus]", "[Company_StatusID] = val('" & DLookup("[Company_StatusID]", "[tblCompany]", "[Company] = '" & strSelected & "'") & "') ")

Thanks to GalaxyAtHome for help!

 

[Galaxiom]

Status = DLookup("[Company_Status]", "[tblCompanyStatus]", [COLOR=black]"[Company_StatusID] = val('" & DLookup("[Company_StatusID]", "[tblCompany]", "[Company] = '" & strSelected & "'") & "') ")[/COLOR]

That is a different approach. I tried nested LookUps where you have used concatenation. Either way these expressions are hard to read.

This is why problems like this are often better handled using variables, though when setting it as a control source it is tempting to try and write the whole expression.

The only way to deal with this is to break it down. Colour coding to emphasise the strings after simplifying names to T for table and F for field (don't care which field) and S for string we get:

[COLOR=silver][COLOR=gray]DLookup( [/COLOR][COLOR=blue]"F"[/COLOR][COLOR=gray],[/COLOR] [COLOR=blue]"T"[/COLOR][/COLOR][COLOR=gray],[/COLOR] [COLOR=blue]"F = val( [B][COLOR=black][COLOR=red]'[/COLOR] [/COLOR][/B]"[/COLOR] & [COLOR=silver][COLOR=orange]DLookup([/COLOR][COLOR=seagreen]"F"[/COLOR][COLOR=orange],[/COLOR] [COLOR=seagreen]"T"[/COLOR][COLOR=orange],[/COLOR] [/COLOR][COLOR=green]"F = [COLOR=black][B]'[/B][/COLOR] "[/COLOR] & S & [COLOR=teal]"[COLOR=black] [B]'[/B][/COLOR]"[/COLOR][COLOR=silver][COLOR=orange])[/COLOR] [/COLOR]& [COLOR=blue]" [B][COLOR=red]' [/COLOR][/B]) "[/COLOR][COLOR=gray])[/COLOR]
 
[COLOR=black]Reducing to:[/COLOR]
 
[COLOR=#c0c0c0][COLOR=silver][COLOR=gray]DLookup( [/COLOR][COLOR=blue]F[/COLOR][COLOR=gray], [/COLOR][COLOR=blue]T[/COLOR][/COLOR][COLOR=gray], [/COLOR][COLOR=blue]F = val( [/COLOR][COLOR=red][B]'[/B] [/COLOR][COLOR=silver][COLOR=orange]DLookup( [/COLOR][COLOR=green]F[/COLOR][COLOR=orange],[/COLOR] [COLOR=green]T[/COLOR][COLOR=orange],[/COLOR] [/COLOR][COLOR=green][COLOR=green]F = [/COLOR][B][COLOR=black]'[/COLOR] [/B][COLOR=black][I]value of S[/I][/COLOR][/COLOR][COLOR=black][B]  ' [/B][/COLOR][COLOR=orange])  [/COLOR][COLOR=blue][B][COLOR=red]'  [/COLOR][/B])  [/COLOR][COLOR=gray])[/COLOR]
 
[/COLOR]

The blue strings belong to the outer(grey) DLookUp. The green strings belong to the inner (orange) LookUp. The tested field in the internal DLookUp is equated to a string held in variable S.

If I have understood this correctly, the red outer single quotes are superfluous. If not I may be about to learn something new.

Thanks for the great question and the answer.

 

[Galaxiom]

I often find it useful to understand what doesn't work. My original suggestion is represented here in the same style as my previous post.

DLookup( "F" , "T" , "F = DLookup( 'F' , 'T' , ' F = " & S & " ' ) " )

Reducing to:

DLookup( F , T , F = DLookup( 'F' , 'T' , ' F = value of S ' ) )

My suggested code would have only worked if the string was a number. I guess I would have to use a str function on the value of S (even though it is already a string) to get around the need for more quotes.

And then I think also add the Val function in vojaka's solution.