DLOOKUP CONFUSION

[Guest]

I need to create a formula (I assume using DLookup) that will populate one
box in my form based on another box within my form and another query within
my database.

I would like to populate client id based on the chosen client name. Here is
what I have tried and doesn't seem to be working.

=DLookUp("[ClientID]","[CLIENT ID QUERY]","[NAME]=" & [FORMS]!Transaction
Table 1![CLIENT NAME] & "'")

Any advice? Thanks!

 

[fredg]

I need to create a formula (I assume using DLookup) that will populate one
box in my form based on another box within my form and another query within
my database.

I would like to populate client id based on the chosen client name. Here is
what I have tried and doesn't seem to be working.

=DLookUp("[ClientID]","[CLIENT ID QUERY]","[NAME]=" & [FORMS]!Transaction
Table 1![CLIENT NAME] & "'")

Any advice? Thanks!

You left off a single quote "[Name] = ' "
And you also have spaces in the form name, so it's best to enclose the
form name within brackets, as you did with [Client Name].

=DLookUp("[ClientID]","[CLIENT ID QUERY]","[NAME]= '" &
[FORMS]![Transaction Table 1]![CLIENT NAME] & "'")

which will fail if your client name has an apostrophe in it, i.e. John
O'Leary.

Better to use:
=DLookUp("[ClientID]","[CLIENT ID QUERY]","[NAME]= """ &
[FORMS]![Transaction Table 1]![CLIENT NAME] & """")

Now what do you do if you have 2 or more clients with the same name?

In addition, Name is a reserved Access/VBA/Jet word and should not be
used as a field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

 

[Wolfgang Kais]

Hello fredg.

[=DLookUp("[ClientID]","[CLIENT ID QUERY]","[NAME]= '" &
[FORMS]![Transaction Table 1]![CLIENT NAME] & "'")
will fail if client name has an apostrophe in it, i.e. John O'Leary.
Better to use:
=DLookUp("[ClientID]","[CLIENT ID QUERY]","[NAME]= """ &
[FORMS]![Transaction Table 1]![CLIENT NAME] & """")]

For every record player there's a record it can't play:
Using double quotes will fail for Fred "the guru" G.
;-)

 

[Douglas J. Steele]

Hello fredg.

[=DLookUp("[ClientID]","[CLIENT ID QUERY]","[NAME]= '" &
[FORMS]![Transaction Table 1]![CLIENT NAME] & "'")
will fail if client name has an apostrophe in it, i.e. John O'Leary.
Better to use:
=DLookUp("[ClientID]","[CLIENT ID QUERY]","[NAME]= """ &
[FORMS]![Transaction Table 1]![CLIENT NAME] & """")]

For every record player there's a record it can't play:
Using double quotes will fail for Fred "the guru" G.
;-)

The "secret" is to double whatever you're using as the delimiter.

I wrote about this in my May, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access". (You can download the column and sample
database for free at http://www.accessmvp.com/DJSteele/SmartAccess.html)

A function like the following will do it:

Function CorrectText( _
InputText As String _
, Optional Delimiter As String = "'" _
) As String

Dim strTemp As String

strTemp = Delimiter
strTemp = strTemp & _
Replace(InputText, Delimiter, Delimiter & Delimiter)
strTemp = strTemp & Delimiter

CorrectText = strTemp

End Function

You'll see that I've got Delimiter set to a single quote by default. That
means you can use

=DLookUp("[ClientID]","[CLIENT ID QUERY]","[NAME]= '" & _
CorrectText([FORMS]![Transaction Table 1]![CLIENT NAME]) & "'")

or

=DLookUp("[ClientID]","[CLIENT ID QUERY]","[NAME]= """ & _
CorrectText([FORMS]![Transaction Table 1]![CLIENT NAME], """") & """")