Access queries with quotes

[eaglekaiser]

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

[eaglekaiser]

The AutoIt query in my post is of course:

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

Sorry

[Loz]

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 September 10, 2011 by cits

[Loz]

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 September 10, 2011 by cits

[eaglekaiser]

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.

[SmOke_N]

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.

[eaglekaiser]

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