Dlookup error

[Chris SL]

Hi there

I try to make a Dlookup in a form in order to get a query record
As follow

=Dlookup("[prix_token_reeval]","Reevaluations_Last","[adresse_reeval] = " & [Formulaires]![Ajout_Reevaluations]![liste_adresse])
record I am looking for Query field to be check with combo list in form

The adress field from query should be equal adress selected in a combo list on the form

But I get #error in my control, I can not understand why.

Is somebody can help me to tell me what wrong ?

Thank you
Chris

 

[Gasman]

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

 

[theDBguy]

Hi Chris. Welcome to AWF!

I agree. #Error probably means a Type Mismatch error. If the address is a Short Text field, you'll need to use a delimiter in your criteria.

 

[Chris SL]

Hi Chris. Welcome to AWF!

I agree. #Error probably means a Type Mismatch error. If the address is a Short Text field, you'll need to use a delimiter in your criteria.

Hi theDBguy

Thank for your answer but what do you means by "you'll need to use a delimiter in your criteria" ?

 

[Chris SL]

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?

Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format

Numbers do not need anything

Also for anything other than one item of criteria, I tend to put the the criteria into a string variable and then debug.print it, until correct then also use that in the code.

Added benefit is, if you still cannot see the error, you can copy and paste back here for someone else to spot it.

Hi Gasman,

Thank you for your answer but it is close Chinese language for me, I am beginner and not fluent in English.
Sorry.
Can you please explain this: "Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works" ?

 

[theDBguy]

Hi theDBguy

Thank for your answer but what do you means by "you'll need to use a delimiter in your criteria" ?

It's just like what @Gasman said, try it this way:

=Dlookup("[prix_token_reeval]","Reevaluations_Last","[adresse_reeval] = '" & [Formulaires]![Ajout_Reevaluations]![liste_adresse] & "'")

 

[Chris SL]

it

It's just like what @Gasman said, try it this way:

=Dlookup("[prix_token_reeval]","Reevaluations_Last","[adresse_reeval] = '" & [Formulaires]![Ajout_Reevaluations]![liste_adresse] & "'")

It works perfectly, thanks a lot.

so I understand that it was necessary to add single quote on " & [Formulaires]![Ajout_Reevaluations]![liste_adresse] & " because it is short text field.

but why [adresse_reeval] does't need ? it is also short text field

 

[Gasman]

[adresse_reeval] is a literal, and the name of the field.
You are building a string statement.

Also the single ' will fail if you have an address like "Tony's Wharf"