Sign in to follow this  
Followers 0
eaglekaiser

Access queries with quotes

7 posts in this topic

Programming with AutoIt I have a problem in my Access 2007 queries using quotes.

This is the query:

SELECT DISTINCT USERNAME, FORNAME FROM MEMBERS WHERE USERNAME Like 'FA*' AND CODENR = '10';

This works perfect in Access 2007. In AutoIt I coded it as follows:

$query = "SELECT DISTINCT NAME, FORNAME FROM MEMBERS WHERE NAME Like 'FA*' AND CODENR = '10';"

Retrieving the data I got the following error :

$title = $adoRs.Fields("USERNAME").Value

$title = $adoRs.Fields("USERNAME").Value^ ERROR

meaning there is probably a quotes-error in my query.

Can anyone help me on this ?

Many thanks

Share this post


Link to post
Share on other sites



The AutoIt query in my post is of course:

$query = "SELECT DISTINCT USERNAME, FORNAME FROM MEMBERS WHERE NAME Like 'FA*' AND CODENR = '10';"

Sorry

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

Looking at some of my code. In my query strings I have been using backticks around column names and single quotes around string values (not for numeric columns as generates an error). When retrieving values from the resultset I wrap the column name in single quotes.eg.

$query = "SELECT * FROM " & $tblname & " WHERE (`COL_MODE`='JT65' OR `COL_MODE`='JT65A') AND `COL_BAND` = '" & $band & "m' AND `COL_CALL`='" & $call & "'"
 
$name = $adoRs.Fields('COL_NAME').value

I use the above technique on different versions of Access .mdb files (I have no control of the structure or version used) and on different operating systems from XP to Win7. I do recall in the early days of debugging the queries I was getting errors and finally settled on the above syntax. Works for me.

Cheers

Edited by cits

.... GOTOs? We don't need no stinkin' GOTOs! ....

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

H'mmm, looks like I can only edit my posting once (new forum board limit?).

I should elaborate. The single quotes around string values in the query string could be double quotes, I just find it easier to use single quotes when construnctiong the string which is using double quotes. If you use backticks around your column names, you should have success.

The single quotes around column names when retrieving resultset data is just my preference. I primarily code in PHP where I use single quotes extensively. The query string syntax I use is similar to what I use for querying MySQL databases under PHP.

Cheers.

Edited by cits

.... GOTOs? We don't need no stinkin' GOTOs! ....

Share this post


Link to post
Share on other sites

First of all, thanks for the quick replies.

I tried the backticks arround the column names and the single qoutes to retrieve the data. The error stays the same, I didn't got a good result.

Share this post


Link to post
Share on other sites

Without seeing your code, I can only assume you're not opening it correctly.

Geosoft has an Access.au3 udf:

http://dundats.mvps.org/autoit/udf_code.aspx?udf=access

I'd specifically look at: Func _accessGetVal, to get a better idea of what your issue may be.


Common sense plays a role in the basics of understanding AutoIt... If you're lacking in that, do us all a favor, and step away from the computer.

Share this post


Link to post
Share on other sites

Thanks to all of you.

The moderator guided me to the access.udf where I found the solution. I changed my query as follows:

FROM:

$query = "SELECT DISTINCT USERNAME, FORNAME FROM MEMBERS WHERE NAME Like 'FA*' AND CODENR = '10';"

INTO:

$query = "SELECT DISTINCT USERNAME, FORNAME FROM MEMBERS WHERE NAME Like '%FA%' AND CODENR = '10';"

No errors, all passed well

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0