DBDATE used in SELECT Statement

[Allan]

I am building a project that uses the Acess database and one field is a DBDATE type.

My Select statement does not want to work - do not know the correct syntax for this statement?

Snippet of code to show the error I have with -  TheSql = "SELECT * FROM master WHERE TheDate = CurDate"

Code Select Expand

DBDATE CurDate
UINT hMaster
POINTER db
STRING DBase

' set pointers to NULL
db = NULL
hMaster = NULL

DBase = GETSTARTPATH + "scheduler.mdb"
' connect to the scheduler.mdb
db = dbConnect("Microsoft Access Driver (*.mdb)",DBase,"")

IF db = NULL
'couldn't open scheduler.mdb so try creating scheduler.mdb
dbCreateMDB(DBase)
' connec to the scheduler.mdb
db = dbConnect("Microsoft Access Driver (*.mdb)",DBase,"")
ENDIF

hMaster = dbExecSQL(db,"CREATE TABLE master (id counter(1,1), TheDate DATE, FullDate VARCHAR(40) )")

'........

STRING TheSql, FullDate, error

CurDate.day = VAL(DATE$("dd"))
CurDate.month = VAL(DATE$("mm"))
CurDate.year = VAL(DATE$("yyyy"))

'PROBLEM HERE....
' CurDate is of type DBDATE - TheDate Field in the Database Table is of type DBDATE

TheSql = "SELECT * FROM master WHERE TheDate = CurDate"
' TheSql = "SELECT * FROM master WHERE TheDate = #" + CurDate + "#"
hMaster = DBEXECSQL(db,TheSql)
error = DBGETERRORCODE(hMaster)
IF LEN(error)
MESSAGEBOX 0, "Error Code: " + error + "\n" + "Error Text: " + DBGETERRORTEXT(hMaster),_
"Select Query failed", @MB_OK|@MB_ICONINFORMATION
ENDIF

DBFREESQL(hMaster)


Thanks.

[Ionic Wind Support Team]

Sitting in Linux at the moment....but you are passing a string so the ODBC driver has no idea what you mean by 'CurDate'.  You'll need to bind the parameter and use a ? as a placeholder, or perhaps just format a date string....

TheSql = "SELECT * FROM master WHERE TheDate = "+Date$

Which might require you to format the string in a specific day,month,year order. 

Paul.

[Allan]

DBASE UDT is used in the TABLE 'master'

The SQL does not accept that for WHERE as I was trying it.

SQL will however accept a string in the WHERE like this:

string tst
tst = DATE$("dd'-'MM'-'yyyy")
' following works
   TheSql = "SELECT * FROM master WHERE TheDate = #" + tst + "#"

Thanks Paul for the answer.