DLookup = Hassle?

[Sgte]

This is the expression as I have typed it in the field view of my query QryTaxReturnStatus :
Expr1: DLookUp("[Client Name]","QryAllClientNames","[TRIndCode]"="[QryAllClientNames]!

Code: Select all

 ")

I am trying to dislpay the name of the client taken from a union query called QryAllClientNames. The result I get from this expression is a blank field. So can anyone guide me in the right direction please?

[HansV]

Where does TRIndCode live? In QryTaxReturnStatus or in QryAllClientNames?

[Sgte]

TRIndcode lives in QryTaxReturnStatus

[HansV]

Thanks. And is it a text field or a number field? (This makes a diiference)

[Sgte]

hI Hans - Ii's a text field

[HansV]

Try changing the expression to

Expr1: DLookUp("[Client Name]","QryAllClientNames","

Code: Select all

=" & Chr(34) & [TRIndCode] & Chr(34))

Chr(34) is the double quote character " that is needed around text values.

[Sgte]

Thanks very much Hans. It is ODD that no mention of the Chr(34) is mentioned in the Access help file - for 2003 - nor in the on-line MS help.

[HansV]

It is not treated as a separate subject. Here is some extra info, adapted from something I once posted in Woody's Lounge:

We use double quotes in Visual Basic and in expressions to indicate where a string begins and ends:

strLastName = "Gates"

SQL uses single quotes to delimit strings, but it also accepts double quotes - but you must be consistent. For example,

WHERE LastName = "Gates'

is not valid.

What should we do when a string must contain quotes? If we use double quotes, this leads to confusion with the quotes that delimit the string:

strCondition = "WHERE LastName = "Gates""

is invalid, for "WHERE LastName = " is seen as a complete string, causing Access to complain about Gates. There are several ways around this. In strings we type in ourselves, we can use double double quotes, or (if it has to do with SQL) single quotes. So a valid version of the instruction above is

strCondition = "WHERE LastName = ""Gates"""

or

strCondition ="WHERE LastName = 'Gates'"

Now, the numeric value (ASCII code or ANSI code) for a double quote is 34, so " = Chr(34), and the numeric value for a single quote is 39, so ' = Chr(39). These can also be used to include quotes in a string:

strCondition = "WHERE LastName = " & Chr(34) & "Gates" & Chr(34)

or

strCondition = "WHERE LastName = " & Chr(39) & "Gates" & Chr(39)

I often use one of the latter two methods because code with a lot of single and/or double quotes adjacent to each other is hard to read.