How do I use SQL.Request with a WHERE statement comparing text va.

[Rtomli]

I seem to have a problem that I do not understand. I am querying an access
database from excel, the path is stored in cell A1. Catalog Number is a text
field in ACCESS and List Price is a numeric filed.

When I use the following in an excel spreadsheet, it works fine:

=SQL.REQUEST($A$1,,,"SELECT `Catalog Number` FROM `Pricing Database` WHERE
(`Pricing Database`.`List Price`= "&G5&")")

I can type a list price in cell G5 and return a catalog number.

However, when I try it the other way around:

=SQL.REQUEST($A$1,,,"SELECT `List Price` FROM `Pricing Database` WHERE
(`Pricing Database`.`Catalog Number`="&G6&")")

I get the N/A error message. There is something wrong in my WHERE statement
that will not allow me to compare the two text fields (Catalog Number in the
ACCESS database and the catalog number stored in cell G6 which is formated as
a text field.

Can someone point out the error in my ways?

Thank you in advance for your assistance.
Ron

 

[sebastienm]

Hi
the textvaue for a text fied shoud be surrounded with the db text
sourrounding characters. Assuming it is apostrophe:
....`.`Catalog Number`='"&G6&"')")
regards,
sebastien

 

[Guest]

Thanks for the reply. I tried that and it still did not
return valid information. Any other suggestions?
Ron

 

[sebastienm]

the surrounding character could be double quote instead of apostrophe :
.....`.`Catalog Number`="""&G6&""")")
Could put the sql formula in a separate cell and post here the result of the
formula, specially with the apostrophe surrounding characters?

Regards,
Sebastien

 

[Rtomli]

I tried the first with no change in the results and did not understand the
part about putting the formula in a separate cell. Could you elaborate a
little? I would be happy to send you a small example file of what I have
attempted to do including a portion of the .mdb and .xls file. Once I got
the first part working, I thought I was home free, but now it has turned into
a personal challenge. It is undoubtedly something very simple that I am
missing.

Thanks,
Ron

 

[sebastienm]

I meant braeaking the SQL.REQUEST in two parts:

FROM
=SQL.REQUEST($A$1,,,"SELECT `List Price` FROM `Pricing Database` WHERE
(`Pricing Database`.`Catalog Number`='"&G6&"')")

TO
in a cell like B1, put the SQL:
="SELECT `List Price` FROM `Pricing Database` WHERE (`Pricing
Database`.`Catalog Number`='"&G6&"')"
And in a the query cell:
=SQL.REQUEST($A$1,,,B1)

It makes it easier to read the sql and see if any problem. Just to make sure:
- table and field names are surrounded by BACK-apostrophe
- text values (cell G6 here) are surrounded by Apostrophe (not
BACK-apostrophe).

Any luck pin-pointing the problem?Still getting the NA error?

Regards,
Sebastien

 

[Rtomli]

Thank you very much.... I knew it was something simple. I tried quotes,
double quotes, back apostrophes... et al. When I used the forward apostrophe
all of my problems went away. I sincerely appreciate your assistance. I
will try breaking up the request but I am not sure that the cell reference
will increment when I copy it down to additional cells. This has truly been
an educational experience.

Gratefully,
Ron

 

[sebastienm]

Thanks Ron. Glad i could help.
Regards,
Sebastien