May 29, 2024, 05:48:55 AM


IonicWind Snippit Manager 2.xx Released!  Install it on a memory stick and take it with you!  With or without IWBasic!

DBDATE used in SELECT Statement

Started by Allan, August 18, 2008, 07:45:07 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.


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"

UINT hMaster

' 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
' connec to the scheduler.mdb
db = dbConnect("Microsoft Access Driver (*.mdb)",DBase,"")

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


STRING TheSql, FullDate, error = VAL(DATE$("dd"))
CurDate.month = VAL(DATE$("mm"))
CurDate.year = VAL(DATE$("yyyy"))

' 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



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. 

Ionic Wind Support Team


August 18, 2008, 10:51:59 PM #2 Last Edit: August 18, 2008, 10:53:31 PM by 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.