RunTime error 2471 with DLOOKUP

[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.

 

[JoséBob]

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 & "'")

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

Hello Mike,
At that time I was still working (iteratively) on the structure of the code and thus not with variable but you're giving me the proper syntax if I want to use variable (thanks). At some point I will use a public variable to be able to read it from all forms and reports.

 

[MajP]

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.

I did this with another user's DB.
I have an application settings table where it holds one record and a field for each setting. I never had a need to normalize that and it is easier to work in that structure

TblApplicationSettings
-SettingID
-DefaultLanguage
-DefaultFolder
.....

Then had a table of Captions and languages

tblCaptions
-CaptionID
-FormReportName
-ControlName
-EnglishCaption
-RussianCaption
-SpanishCaption
etc.

on the form or reports opening loop tblCaptions where FormReportName = current objects name. Set the caption to DefaultLanguage.

Now to make this easier the application was already built. So I loaded tblCaptions by looping all the forms/reports then looping the controls and loading up tblCaptions with all labels. Then just had to add the additional languages.

 

[arnelgp]

i also have done this before.
the db is created on A2021.
if you have lower version, it might not work.

when the db opens:
put common to both username and password.

click "Search and View" on the Ribbon.
on "Datasheet Tools" tab (ribbon) you can change language
of the Labels of the form.

 

[JoséBob]

Many Thanks @MajP and @arnelgp I'll have a look at the example and the structure in detail.
And thanks to the others for the answers on the dlookup.

​

 

[JoséBob]

One more question :
I have certain msgbox of which the text shall be different depending on language. Using the simple code as

NewMsgBoxTxt = DLookup("[" & LangVar & "]", "LanguageTable", "[Ref_Call]='cmbSearchTxtBox1'")
MsgBox NewMsgBoxTxt

It works fine.

My only issue is that I want to have line breaks. The text in the table is in the form "Text, Text, Text" " & vbNewLine & vbNewLine & "Text" .
But I can't get the string to be interpreted with the vbNewLine.
What the msgbox shows is "Text, Text, Text" " & vbNewLine & vbNewLine & "Text".

I tried to remove some of the " as for example Text, Text, Text" " & vbNewLine & vbNewLine & "Text but this neither works.

What I don't understand is that passing the string through the variable directly works fine as in the example below:

NewMsgBoxTxt = "Text, Text, Text" " & vbNewLine & vbNewLine & "Text"
MsgBox NewMsgBoxTxt

I hope this is clear.

 

[Josef P.]

Do I understand you correctly: This text "Text, Text, Text" " & vbNewLine & vbNewLine & "Text" can be read exactly like this in the text field of the table?
Then it is also displayed in exactly the same way in the message text. vbNewLine is then just a text.
Why is it stored like this? The text to be output would be easier to use.

But let's take a look at the current situation purely in VBA:

Private Sub Test()

    Dim NewMsgBoxTxt As String
    
    NewMsgBoxTxt = """Text, Text, Text"" "" & vbNewLine & vbNewLine & ""Text"""
    Debug.Print NewMsgBoxTxt
    MsgBox NewMsgBoxTxt
    
' fix to correct vba code inside string:
    NewMsgBoxTxt = """Text, Text, Text"" & vbNewLine & vbNewLine & ""Text"""
    Debug.Print NewMsgBoxTxt
    MsgBox NewMsgBoxTxt
    MsgBox Eval(Replace(NewMsgBoxTxt, "vbNewLine", "chr(13) & chr(10)"))

End Sub

 

[The_Doc_Man]

It sort of depends on what version of Access you are using, but vbNewLine is now deprecated.

Constants.vbNewLine Field (Microsoft.VisualBasic)

Represents a newline character for print and display functions.

learn.microsoft.com

I've had success with vbCrLf as a way to have multiple lines in a text box.