Problem with DLookUp

[Audissée]

Hello

First, appologies for my bad english.

I need help for the following problem. I'm using the Dlookup method to help
filling a field :

me![Name] = DLookUp("[Name]", "[Table_contact]","[Surname]= '" &
me![Surname] & "'")

This is the good syntax, but the problem is that it return nothing if the
field me![Surname] contains the character '. Otherwise, it is OK.

How can I solve this problem?

Thanks for help

Stephane DAYNAC

 

[Dirk Goldgar]

Hello

First, appologies for my bad english.

I need help for the following problem. I'm using the Dlookup method
to help filling a field :

me![Name] = DLookUp("[Name]", "[Table_contact]","[Surname]= '" &
me![Surname] & "'")

This is the good syntax, but the problem is that it return nothing if
the field me![Surname] contains the character '. Otherwise, it is OK.

How can I solve this problem?

Thanks for help

Stephane DAYNAC

Unless [Surname] is likely to contain the " character, you can use this
criterion:

"[Surname]= """ & Me![Surname] & """"

An alternative is to use the Replace function on the value of
Me![Surname] to replace the quote character -- single or double -- with
two of the same character:

' using single quotes
"[Surname]= '" & Replace(Me![Surname], "'", "''") & "'"

or

' using double quotes
"[Surname]= """ & Replace(Me![Surname], """", """""") & """"

 

[Audissée]

Hello Dick

Thanks very much for the rapid answer.

Stéphane

De : "Dirk Goldgar" <[email protected]>
Répondre à : "Dirk Goldgar" <[email protected]>
Groupes : microsoft.public.access
Date : Sun, 21 Dec 2003 11:56:10 -0500
Objet : Re: Problem with DLookUp

Hello

First, appologies for my bad english.

I need help for the following problem. I'm using the Dlookup method
to help filling a field :

me![Name] = DLookUp("[Name]", "[Table_contact]","[Surname]= '" &
me![Surname] & "'")

This is the good syntax, but the problem is that it return nothing if
the field me![Surname] contains the character '. Otherwise, it is OK.

How can I solve this problem?

Thanks for help

Stephane DAYNAC

Unless [Surname] is likely to contain the " character, you can use this
criterion:

"[Surname]= """ & Me![Surname] & """"

An alternative is to use the Replace function on the value of
Me![Surname] to replace the quote character -- single or double -- with
two of the same character:

' using single quotes
"[Surname]= '" & Replace(Me![Surname], "'", "''") & "'"

or

' using double quotes
"[Surname]= """ & Replace(Me![Surname], """", """""") & """"

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Tom Wickerath]

In addition to Dirk Goldgar's answer, I recommend avoiding the use of reserved words for
the names of objects or controls in your database. You appear to have a table or query
named "Name" and a form or report control with the same name. This is a reserved word in
Access.

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=209187

Tom
_________________________________________


Hello

First, appologies for my bad english.

I need help for the following problem. I'm using the Dlookup method to help
filling a field :

me![Name] = DLookUp("[Name]", "[Table_contact]","[Surname]= '" &
me![Surname] & "'")

This is the good syntax, but the problem is that it return nothing if the
field me![Surname] contains the character '. Otherwise, it is OK.

How can I solve this problem?

Thanks for help

Stephane DAYNAC

 

[Audissée]

Hello every one

The name of examples i have send with my mail are for exemple only. The reel
names I use are not reserved words.

I'm not sure I have explain the problem correctly. It is not the name of
controls or objets that make the problem. It is their content.

I take another example :

me![The_name] = DLookUp("[The_name-target]", "[Table_target]","[The
Surname_target]= '" & me![The_surname] & "'")

I have this programmation on an after update field call The_Surname.
It has to search the corresponding name ("The_name_target") in the Table
called "Table_target" to automatically fill the current form field
"The_name".

We supposed "The_surname" to be a key field in the "Table_contact".
So, for every "The-Surname" correspond only one "The_name".
It'll be difficult to explain briefly but I don't want to use the
traditionnal relation between Table to obtain The_name.


If The_surname = Dupond, it return the corresponding name "The_name" of
Mister Dupond

If the_surname = O'Brien, it doesn't work because of the character'.

Every time the name contain the character ' Dlookup return nothing.

And it's problematic to limit the_surname to have no " ' " in it.

I hope the explanation are clear and thanks to every one for according some
time to read my mail and respond

Sincerly

Stéphane

 

[Dirk Goldgar]

Hello every one

The name of examples i have send with my mail are for exemple only.
The reel names I use are not reserved words.

I'm not sure I have explain the problem correctly. It is not the name
of controls or objets that make the problem. It is their content.

I take another example :

me![The_name] = DLookUp("[The_name-target]", "[Table_target]","[The
Surname_target]= '" & me![The_surname] & "'")

I have this programmation on an after update field call The_Surname.
It has to search the corresponding name ("The_name_target") in the
Table called "Table_target" to automatically fill the current form
field "The_name".

We supposed "The_surname" to be a key field in the "Table_contact".
So, for every "The-Surname" correspond only one "The_name".
It'll be difficult to explain briefly but I don't want to use the
traditionnal relation between Table to obtain The_name.


If The_surname = Dupond, it return the corresponding name "The_name"
of Mister Dupond

If the_surname = O'Brien, it doesn't work because of the character'.

Every time the name contain the character ' Dlookup return nothing.

And it's problematic to limit the_surname to have no " ' " in it.

I hope the explanation are clear and thanks to every one for
according some time to read my mail and respond

Sincerly

Stéphane

Stéphane, the answers you've been given should have solved this problem.
Have they not done so?

 

[John Vinson]

If The_surname = Dupond, it return the corresponding name "The_name" of
Mister Dupond

If the_surname = O'Brien, it doesn't work because of the character'.

The apostrophe causes the problem because it is being interpreted as
the closing quote delimiter - you open the criterion in the DLookUp
with ', and it sees the apostrophe as the closing '.

Use " instead: to include a " inside a string delimited with ", use
two consecutive " characters.

me![The_name] = DLookUp("[The_name-target]", "[Table_target]","[The
Surname_target]= """ & me![The_surname] & """")

 

[Audissée]

Thanks a lot to Dirk and John.
The use of the two conscutive character solve the problem.

Sincerly

Stéphane

De : John Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
Répondre à : (e-mail address removed)
Groupes : microsoft.public.access
Date : Mon, 22 Dec 2003 09:45:43 -0700
Objet : Re: Problem with DLookUp (v2)

If The_surname = Dupond, it return the corresponding name "The_name" of
Mister Dupond

If the_surname = O'Brien, it doesn't work because of the character'.

The apostrophe causes the problem because it is being interpreted as
the closing quote delimiter - you open the criterion in the DLookUp
with ', and it sees the apostrophe as the closing '.

Use " instead: to include a " inside a string delimited with ", use
two consecutive " characters.

me![The_name] = DLookUp("[The_name-target]", "[Table_target]","[The
Surname_target]= """ & me![The_surname] & """")