Problem with DLookUp

  • Thread starter Thread starter Audiss=?ISO-8859-1?B?6Q==?=e
  • Start date Start date
A

Audiss=?ISO-8859-1?B?6Q==?=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
 
Audissée said:
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
Click to expand...

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], """", """""") & """"
 
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

Audissée said:
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
Click to expand...

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)
Click to expand...
 
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
 
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
 
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'.
Click to expand...

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] & """")
 
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'.
Click to expand...

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] & """")
Click to expand...
 
Back
Top