dlookup and string for criteria

[DRBE]

Hopefully this is a simple query, but I just keep getting stuck:
I am trying to use Dlookup on a form to look up the corresponding email
address for an agency.
I use this as a textbox control source field, with the aim of the
appropriate email address being displayed (which can then later me included
in an actual email from a command button)
Using Access 2000:

=DLookUp("[agency appointments email]","AgenciesTable","[AgencyName]=
""DuckandCoAgency""")

it works perfectly and the corresponding email address is returned.

However, when I replace "DuckandCo" with [Agency]

=DLookUp("[agency appointments email]","AgenciesTable","[AgencyName] =
""[Agency]""")

where the entry for the [Agency] field is DuckandCo

it just comes up blank. ( tested in on access 2003 it gives message "no
current record"

I also tried

=DLookUp("[agency appointments email]","AgenciesTable","[AgencyName] =
"[Agency]"")

but this gives the error
"the expression you entered contains and invalid syntax
you may have entered and operand without and operator"

presumably my quotes are in the wrong places. would appreciate any advice.

regards
Bruce

 

[Graham Mandeno]

Hi Bruce

By the time your DLookup request leaved the form and gets to the query
processor, it has no idea what [Agency] is.

The solution is to include the *value* of [Agency] in the string that you
use for the third argument for DLookup. Of course, it must also be enclosed
in quotes:

=DLookup("[agency appointments email]",
"AgenciesTable","[AgencyName]=""" & [Agency] & """)

Are you, by chance, using a combo box on your form to select the agency? If
so, then a better way to do it would be to include the email address as an
extra column (say the third) in your combo box and hide it by setting its
ColumnWidth to zero. Then you can simply refer to that column:

=[Agency].Column(2)

 

[DRBE]

I tried this but it now gives the error,
The expression you entered has an invalid string
a string can be up to 2048 charactars long

 

[DRBE]

I changed it to:
=DLookUp("[agency appointments email]","AgenciesTable",'[AgencyName]="" &
[Agency] & ""')

and using the single quote seems to have worked now thanks

regards
Bruce

Hi Bruce

By the time your DLookup request leaved the form and gets to the query
processor, it has no idea what [Agency] is.

The solution is to include the *value* of [Agency] in the string that you
use for the third argument for DLookup. Of course, it must also be enclosed
in quotes:

=DLookup("[agency appointments email]",
"AgenciesTable","[AgencyName]=""" & [Agency] & """)

Are you, by chance, using a combo box on your form to select the agency? If
so, then a better way to do it would be to include the email address as an
extra column (say the third) in your combo box and hide it by setting its
ColumnWidth to zero. Then you can simply refer to that column:

=[Agency].Column(2)
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hopefully this is a simple query, but I just keep getting stuck:
I am trying to use Dlookup on a form to look up the corresponding email
address for an agency.
I use this as a textbox control source field, with the aim of the
appropriate email address being displayed (which can then later me
included
in an actual email from a command button)
Using Access 2000:

=DLookUp("[agency appointments email]","AgenciesTable","[AgencyName]=
""DuckandCoAgency""")

it works perfectly and the corresponding email address is returned.

However, when I replace "DuckandCo" with [Agency]

=DLookUp("[agency appointments email]","AgenciesTable","[AgencyName] =
""[Agency]""")

where the entry for the [Agency] field is DuckandCo

it just comes up blank. ( tested in on access 2003 it gives message "no
current record"

I also tried

=DLookUp("[agency appointments email]","AgenciesTable","[AgencyName] =
"[Agency]"")

but this gives the error
"the expression you entered contains and invalid syntax
you may have entered and operand without and operator"

presumably my quotes are in the wrong places. would appreciate any
advice.

regards
Bruce

 

[Douglas J. Steele]

Just note that the single quote will fail if the name has an apostrophe in
it, like O'Reilly And Sons

To handle that possibility, you need to use one of the following:

=DLookUp("[agency appointments email]","AgenciesTable", _
"[AgencyName]='" & Replace([Agency], "'", "''") & "'")

=DLookUp("[agency appointments email]","AgenciesTable", _
"[AgencyName]=""" & [Agency] & """")

Exagerated for clarity, those area

=DLookUp("[agency appointments email]","AgenciesTable", _
"[AgencyName]= ' " & Replace([Agency], " ' ", " ' ' ") & " ' ")

=DLookUp("[agency appointments email]","AgenciesTable", _
"[AgencyName]=" " " & [Agency] & " " " ")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I changed it to:
=DLookUp("[agency appointments email]","AgenciesTable",'[AgencyName]="" &
[Agency] & ""')

and using the single quote seems to have worked now thanks

regards
Bruce

Hi Bruce

By the time your DLookup request leaved the form and gets to the query
processor, it has no idea what [Agency] is.

The solution is to include the *value* of [Agency] in the string that you
use for the third argument for DLookup. Of course, it must also be
enclosed
in quotes:

=DLookup("[agency appointments email]",
"AgenciesTable","[AgencyName]=""" & [Agency] & """)

Are you, by chance, using a combo box on your form to select the agency?
If
so, then a better way to do it would be to include the email address as
an
extra column (say the third) in your combo box and hide it by setting its
ColumnWidth to zero. Then you can simply refer to that column:

=[Agency].Column(2)
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hopefully this is a simple query, but I just keep getting stuck:
I am trying to use Dlookup on a form to look up the corresponding email
address for an agency.
I use this as a textbox control source field, with the aim of the
appropriate email address being displayed (which can then later me
included
in an actual email from a command button)
Using Access 2000:

=DLookUp("[agency appointments email]","AgenciesTable","[AgencyName]=
""DuckandCoAgency""")

it works perfectly and the corresponding email address is returned.

However, when I replace "DuckandCo" with [Agency]

=DLookUp("[agency appointments email]","AgenciesTable","[AgencyName] =
""[Agency]""")

where the entry for the [Agency] field is DuckandCo

it just comes up blank. ( tested in on access 2003 it gives message
"no
current record"

I also tried

=DLookUp("[agency appointments email]","AgenciesTable","[AgencyName] =
"[Agency]"")

but this gives the error
"the expression you entered contains and invalid syntax
you may have entered and operand without and operator"

presumably my quotes are in the wrong places. would appreciate any
advice.

regards
Bruce

 

[DRBE]

tried the 1st two methods, and both worked great thanks.
wanting to use similar option for various other parts of my database, so
great to have some techniques that work.

thanks again

kind regards
Bruce Ella

Just note that the single quote will fail if the name has an apostrophe in
it, like O'Reilly And Sons

To handle that possibility, you need to use one of the following:

=DLookUp("[agency appointments email]","AgenciesTable", _
"[AgencyName]='" & Replace([Agency], "'", "''") & "'")

=DLookUp("[agency appointments email]","AgenciesTable", _
"[AgencyName]=""" & [Agency] & """")

Exagerated for clarity, those area

=DLookUp("[agency appointments email]","AgenciesTable", _
"[AgencyName]= ' " & Replace([Agency], " ' ", " ' ' ") & " ' ")

=DLookUp("[agency appointments email]","AgenciesTable", _
"[AgencyName]=" " " & [Agency] & " " " ")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I changed it to:
=DLookUp("[agency appointments email]","AgenciesTable",'[AgencyName]="" &
[Agency] & ""')

and using the single quote seems to have worked now thanks

regards
Bruce

Hi Bruce

By the time your DLookup request leaved the form and gets to the query
processor, it has no idea what [Agency] is.

The solution is to include the *value* of [Agency] in the string that you
use for the third argument for DLookup. Of course, it must also be
enclosed
in quotes:

=DLookup("[agency appointments email]",
"AgenciesTable","[AgencyName]=""" & [Agency] & """)

Are you, by chance, using a combo box on your form to select the agency?
If
so, then a better way to do it would be to include the email address as
an
extra column (say the third) in your combo box and hide it by setting its
ColumnWidth to zero. Then you can simply refer to that column:

=[Agency].Column(2)
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand


Hopefully this is a simple query, but I just keep getting stuck:
I am trying to use Dlookup on a form to look up the corresponding email
address for an agency.
I use this as a textbox control source field, with the aim of the
appropriate email address being displayed (which can then later me
included
in an actual email from a command button)
Using Access 2000:

=DLookUp("[agency appointments email]","AgenciesTable","[AgencyName]=
""DuckandCoAgency""")

it works perfectly and the corresponding email address is returned.

However, when I replace "DuckandCo" with [Agency]

=DLookUp("[agency appointments email]","AgenciesTable","[AgencyName] =
""[Agency]""")

where the entry for the [Agency] field is DuckandCo

it just comes up blank. ( tested in on access 2003 it gives message
"no
current record"

I also tried

=DLookUp("[agency appointments email]","AgenciesTable","[AgencyName] =
"[Agency]"")

but this gives the error
"the expression you entered contains and invalid syntax
you may have entered and operand without and operator"

presumably my quotes are in the wrong places. would appreciate any
advice.

regards
Bruce