DLookUp Quotation Problem

[GD]

Seemingly quick question using an A2K database. Here is my Dlookup
statement (Inserted into the control source for a text box):

DLookUp("Inspection_Tool","tblValidPartDimensions","Dimension_No =
' " & [txtDimension_No] & " ' ")

It returns the values I want just fine, except for one scenario. If
the string in txtDimension_No has a single quote in in ( ' ), then it
returns #Error. So imagine if it said something like O'Riley.

How can I rework my DLookUp statement to fix this? Thanks.

 

[Allen Browne]

Try:
DLookUp("Inspection_Tool","tblValidPartDimensions",
"Dimension_No = """ & [txtDimension_No] & """")

Explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Note that this assumes that Dimension_No is a Text field if you open
tblValidPartDimensions in design view. If it is a Number field, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

 

[GD]

Unfortunately that didn't work. Now instead of #Error it just turns
out blank. On some of the records that had a quotation mark (like 1")
they are now coming out as #Error. I read the article and what you
posted should have made my first problem go away but it didn't for
some reason. I don't know what I would do about the second. (Yes
this is a text field by the way).

Try:
DLookUp("Inspection_Tool","tblValidPartDimensions",
"Dimension_No = """ & [txtDimension_No] & """")

Explanation:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Note that this assumes that Dimension_No is a Text field if you open
tblValidPartDimensions in design view. If it is a Number field, see:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Seemingly quick question using an A2K database. Here is my Dlookup
statement (Inserted into the control source for a text box):

DLookUp("Inspection_Tool","tblValidPartDimensions","Dimension_No =
' " & [txtDimension_No] & " ' ")

It returns the values I want just fine, except for one scenario. If
the string in txtDimension_No has a single quote in in ( ' ), then it
returns #Error. So imagine if it said something like O'Riley.

How can I rework my DLookUp statement to fix this? Thanks.

 

[Guest]

You might try to use the Replace function. Something like
Replace([txtDimension_No],"'","''") realize that is " ' " and " ' ' "
with spaces removed.

 

[GD]

You might try to use the Replace function. Something like
Replace([txtDimension_No],"'","''") realize that is " ' " and " ' ' "
with spaces removed.

Seemingly quick question using an A2K database. Here is my Dlookup
statement (Inserted into the control source for a text box):

DLookUp("Inspection_Tool","tblValidPartDimensions","Dimension_No =
' " & [txtDimension_No] & " ' ")

It returns the values I want just fine, except for one scenario. If
the string in txtDimension_No has a single quote in in ( ' ), then it
returns #Error. So imagine if it said something like O'Riley.

How can I rework my DLookUp statement to fix this? Thanks.

Thanks Rich. I've been trying to incorporate that but I can't seem to
get it to work. Could you post your intention of the whole statement?

 

[Guest]

GD,
The third variable in the dlookup function is evaluated as a SQL statement
where clause.

The error in your code happens because the ' in the original statement
messes up the SQL statement, causing it to create a nonexecutable statement.

To get around this, the Replace function changes the ' to a '' (to single
quotes) so that the SQL gets evaluated correctly.

You might make sure that there are also no extra spaces before and after the
value in the single quotes of the dlookup statement.

I do believe this is what this should look like:

DLookUp("Inspection_Tool","tblValidPartDimensions","Dimension_No = '" &
Replace([txtDimension_No],"'","''") & "' ")

You might try to use the Replace function. Something like
Replace([txtDimension_No],"'","''") realize that is " ' " and " ' ' "
with spaces removed.

Seemingly quick question using an A2K database. Here is my Dlookup
statement (Inserted into the control source for a text box):

DLookUp("Inspection_Tool","tblValidPartDimensions","Dimension_No =
' " & [txtDimension_No] & " ' ")

It returns the values I want just fine, except for one scenario. If
the string in txtDimension_No has a single quote in in ( ' ), then it
returns #Error. So imagine if it said something like O'Riley.

How can I rework my DLookUp statement to fix this? Thanks.

Thanks Rich. I've been trying to incorporate that but I can't seem to
get it to work. Could you post your intention of the whole statement?

 

[GD]

GD,
The third variable in the dlookup function is evaluated as a SQL statement
where clause.

The error in your code happens because the ' in the original statement
messes up the SQL statement, causing it to create a nonexecutable statement.

To get around this, the Replace function changes the ' to a '' (to single
quotes) so that the SQL gets evaluated correctly.

You might make sure that there are also no extra spaces before and after the
value in the single quotes of the dlookup statement.

I do believe this is what this should look like:

DLookUp("Inspection_Tool","tblValidPartDimensions","Dimension_No = '" &
Replace([txtDimension_No],"'","''") & "' ")

You might try to use the Replace function. Something like
Replace([txtDimension_No],"'","''") realize that is " ' " and " ' ' "
with spaces removed.
:
Seemingly quick question using an A2K database. Here is my Dlookup
statement (Inserted into the control source for a text box):
DLookUp("Inspection_Tool","tblValidPartDimensions","Dimension_No =
' " & [txtDimension_No] & " ' ")
It returns the values I want just fine, except for one scenario. If
the string in txtDimension_No has a single quote in in ( ' ), then it
returns #Error. So imagine if it said something like O'Riley.
How can I rework my DLookUp statement to fix this? Thanks.

Thanks Rich. I've been trying to incorporate that but I can't seem to
get it to work. Could you post your intention of the whole statement?

Rich, that statement makes sense to me as well, but when I put that
in, I get #Name? come up for every record.
Perhaps this DlookUp statement should be written elsewhere? Right now
its in the control source of the text box.
So is it true that the Replace(Str, chr1, chr2) function outputs the
same string as sent as input, but all the chr1 characters have been
changed to chr2?