RunTime error 2471 with DLOOKUP (1 Viewer)

[JoséBob]

Hello,
I have a runtime error when using a DLOOKUP function. The code is following :

StrValue = DLookup("[English]","LanguageTable","[German]=German")
StrValue = DLookup("[English]","LanguageTable","[FieldRef_123]=Abc")

it is indeed strange, but the first line is just to show that it works with the first one and doesn't with the second. I tried many other name for the column (i.e. FieldRef_123), I closed and opened the data base, I deleted the table and rebuilt it, and it still works with the first line and doesn't with the second and giving a runtime error.
Any idea ?

 

[arnelgp]

try:

StrValue = DLookup("[English]","LanguageTable","[German]='German'")
StrValue = DLookup("[English]","LanguageTable","[FieldRef_123]='Abc'")

 

[JoséBob]

The first one ([German]='German'") doesn't work but the second ("[FieldRef_123]='Abc'") does.
I forgot to mention that both columns are identically parametrised in the table (short text)

 

[JoséBob]

it works indeed. Do you know why?

 

[MajP]

In sql literal strings need single quotes to delimit them. Dates need # around them.
See examples of delimiters
Thread 'Get Filter from Controls, CSQL, and Combine Multiple Filters' https://www.access-programmers.co.u...ols-csql-and-combine-multiple-filters.311743/

 

[Gasman]

The first one ([German]='German'") doesn't work but the second ("[FieldRef_123]='Abc'") does.
I forgot to mention that both columns are identically parametrised in the table (short text)

I cannot see why that would be the case?

 

[The_Doc_Man]

Just for the record, error 2471 is a semi-generic error that says that a query returned an error. The detailed text displayed for that error message would include some hint (unfortunately, usually a bit vague) on what went wrong. USUALLY it is that some field or clause was incorrectly specified. Because queries are executed in the context of the ACE sub-process but VBA code is executed in the context of MSACCESS.EXE's main (GUI) process, anything that would be visible to VBA code is not necessarily visible to the SQL (implied or explicit) code associated with the operation.

Every Domain Aggregate function (and DLookup IS one such function) builds a query in the background based on the parameters you give it. It is clear based on the fact that you COULD get a result for the [Field_123] case that the first parameter ("[English]") and the second parameter ("LanguageTable") are correct. Which leaves the criteria parameter "[German]=German" OR "[German]='German'" as your culprit. If NEITHER of the two formats work then I have to ask a couple of questions.

Is there ever a time when [German] = 'Irish'? What are the expected values of [German]? It seems to be a tautology to say that [German]='German' so maybe your problem is the data type and content of this [German] field, whatever it is. Tell us what it is or represents.

 

[JoséBob]

Just for the record, error 2471 is a semi-generic error that says that a query returned an error. The detailed text displayed for that error message would include some hint (unfortunately, usually a bit vague) on what went wrong. USUALLY it is that some field or clause was incorrectly specified. Because queries are executed in the context of the ACE sub-process but VBA code is executed in the context of MSACCESS.EXE's main (GUI) process, anything that would be visible to VBA code is not necessarily visible to the SQL (implied or explicit) code associated with the operation.

Every Domain Aggregate function (and DLookup IS one such function) builds a query in the background based on the parameters you give it. It is clear based on the fact that you COULD get a result for the [Field_123] case that the first parameter ("[English]") and the second parameter ("LanguageTable") are correct. Which leaves the criteria parameter "[German]=German" OR "[German]='German'" as your culprit. If NEITHER of the two formats work then I have to ask a couple of questions.

Is there ever a time when [German] = 'Irish'? What are the expected values of [German]? It seems to be a tautology to say that [German]='German' so maybe your problem is the data type and content of this [German] field, whatever it is. Tell us what it is or represents.

Thanks for the detailed answer and indeed some questions arise. I wouldn't write here the description as I develop on a computer with Access in French.
The idea behind the issue is a try to make a multilingual UI. Each objects (label, dropdown, etc.) will load their caption based on the language chosen. The "LanguageTable" is a table with column for each languages with one language in one column [English], [French], [German] and [Field_123] is the column with the object name. The idea is that the dlookup will look for the caption in the correct language based on its name.

I tried

StrValue = DLookup("[English]","LanguageTable","[FieldRef_123]=")

and it returns error as empty (as expected),
I tried

StrValue = DLookup("[English]","LanguageTable","[German]= Irish")

and it returns error
I tried

StrValue = DLookup("[English]","LanguageTable","[German]= GE")

and it returns error
but I tried

StrValue = DLookup("[English]","LanguageTable","[German]='GE'")

works

I don't know if that helps.

 

[Mike Krailo]

Sounds like a table structure problem to me. All the languages should be stacked vertically, not accross in columns. I would assign each language a simple number based on another lookup table. Then in your languageTable, the structure would be ID, control name, controlType, caption, LangID.

Something similar to that should allow the type of dlookup needed to switch languages. Actually, a query would pick out all the controls needed for one language. Then looping through them, sets all the captions.

 

[JoséBob]

Sounds like a table structure problem to me. All the languages should be stacked vertically, not accross in columns. I would assign each language a simple number based on another lookup table. Then in your languageTable, the structure would be ID, control name, controlType, caption, LangID.

Something similar to that should allow the type of dlookup needed to switch languages. Actually, a query would pick out all the controls needed for one language. Then looping through them, sets all the captions.

Thanks for the answer. We could open another thread about how to build a multilingual UI because I wasn't sure about the best way to do it.

 

[Mike Krailo]

You made clear both field types are Short Text field types. If that is true, then the where clause part of dlookup must be enclosed in single quotes. My guess is you are not using a variable in the where clause as shown below. Please confirm yes/no.

In post #1, you said this works and that's because, you hard coded the string value into the lookup.

StrValue = DLookup("[English]","LanguageTable","[German]=German")

Normally, the string "German" would be in a variable or a control source on a form like these two examples.

' Simple string variable varLang or call it whatever you want
StrValue = DLookup("[English]","LanguageTable","[German]='" & varLang & "'")

' If the variable is data from the form
StrValue = DLookup("[English]","LanguageTable","[German]='" & Me.MyCombobox & "'")

The first one ([German]='German'") doesn't work but the second ("[FieldRef_123]='Abc'") does.
I forgot to mention that both columns are identically parametrised in the table (short text)

Again, you are showing a literal string quoted and not a variable like we would expect normally.

 

[Josef P.]

StrValue = DLookup("[English]","LanguageTable","[German]=German")
Except for Null in the data field German, the criteria German=German will be true for every record.

 

[Pat Hartman]

Except for Null in the data field German, the criteria German=German will be true for every record.

It should be false for every record. With this structure (which is not properly normalized), the field labeled "German" should contain the caption in German. The field labeled "English" should contain the caption in English, etc.

This expression -
StrValue = DLookup("[English]","LanguageTable","[German]= 'zimmer'")

Should return the value Room if I remember some German.