Construct SQL string

  • Thread starter Thread starter paul
  • Start date Start date
P

paul

Hi,
In my Access 2000 application, I search previous record
with SQL string:
strSql = "SELECT * FROM tblRoster WHERE Class_no= """ &
(Me.class_no) & """"
So far it works fine. But when tried to add two more
creteria for the Where clause, which is EndDate (date
field)& Location (text field), I had hard time to append
them at the end of strSql. What's the syntax for adding
this two more condition? Any easy to construct the SQL
statement?
Thanks.
 
Hi Paul

You combine multiple WHERE conditions by joining them with AND or OR
(depending on whether you want both conditions or either condition to be
met).

Date values for comparison must be enclosed in # characters, and must be in
the US date format (mm/dd/yyyy).

Text values must be enclosed in either single or double quotes (as you are
doing already with Class_no).

So a valid string might look like this:

strSql = "SELECT * FROM tblRoster WHERE Class_no= """ _
& Me.class_no & """ AND (EndDate<=" _
& Format(Me.txtEndDate, "\#mm/dd/yyyy\#") _
& " OR EndDate Is Null) AND Location=""" _
& Me.txtLocation & """;"
 
dim rstRecs as dao.RecordSet

strSql = "SELECT * FROM tblRoster WHERE Class_no= '" & (Me.class_no) & "'"
& _
" and EndDate = #" & format(me.classDate,"mm/dd/yyyy") &
"#" & _
" and location = '" & me.Location & "'"

......as many as you want....

Then....

set rstRecs = currentdb.OpenRecordSet(srtSql)

Note I started using ' (single) quotes to surround those fields..as I find
it easer to read/deal with..

Note how strings much be surrounded with quotes, and dates with #, and, in
us mm/dd/yyyy format to work.
 
Back
Top