using a variable in an Access Query

[braddog]

;I have a problem with an Access Query using a variable in the Query string.
;For a simple Access Database:

;ID Date ALT AST
;1 2/2/2008 100 110
;2 3/15/2008 200 210
;3 4/1/2008 300 310

Database db2.Open(db2.CsAccess("$personal$\AccessFiles\mydb1.mdb"))
ARRAY(str) a
int r c
;I want to query with Date as a variable:
DATE d="4/1/2008"
;But this does not work for me:
db2.QueryArr("SELECT ALT FROM Table1 WHERE Date = d" a)
for r 0 a.len(2)
,out "--Record %i--" r+1
,for c 0 a.len(1)
,,out a[c r]
,,
,,
;The following code works fine when Date is not sent as a variable:
Database db2.Open(db2.CsAccess("$personal$\AccessFiles\mydb1.mdb"))
ARRAY(str) a
int r c
db2.QueryArr("SELECT ALT FROM Table1 WHERE Date = #4/1/2008#" a)
for r 0 a.len(2)
,out "--Record %i--" r+1
,for c 0 a.len(1)
,,out a[c r]
;How can I send a Variable in the query?
,,

[Gintaras]

Macro

Code:

Copy      Help

ARRAY(str) a
int r c
DATE d="4/1/2008"

;To create string with variables, use format().
;But variables of type DATE cannot be used with format().
;Convert d to string. To convert, you can simply assign d to a str
;variable (str sd=d). It uses your computer's date format. To make
;sure that date format is like 4/1/2008 on all computers, use time().
str sd.time(d "MM/dd/yyyy")

str sql.format("SELECT ALT FROM Table1 WHERE Date = %s" sd)
;out sql

db2.QueryArr(sql a)
for r 0 a.len(2)
,out "--Record %i--" r+1
,for c 0 a.len(1)
,,out a[c r]

[braddog]

Thank you. I also had to wrap the date in # symbols to make it work:

str sd.time(d "MM/dd/yyyy")
str sdTemp.from("#" sd)
sd.from(sdTemp "#")

str sql.format("SELECT ALT FROM Table1 WHERE Date = %s" sd)

[Gintaras]

Macro

Code:

Copy      Help

str sd.time(d "#MM/dd/yyyy#")