Sequel Help!

[boneKrusher]

Hi All,

I am trying to make the following sequel statement but I am having trouble. I want to add another "WHERE" crtieria but I am having trouble.

strSQL = "SELECT Audit_Schedule.* " & _
"FROM Audit_Schedule " & _
"WHERE & Month([AUD_due])= " & intMonth & " AND Year([AUD_due]) = " & MyYear & _
" ORDER BY ApptStartTime;"

I want to add "ACTIVE = -1" (active is a yes/no field). I can't figure it out!

Thanks,
bones

[Norie]

Wouldn't it just be this?

strSQL = "SELECT Audit_Schedule.* " & _
"FROM Audit_Schedule " & _
"WHERE & Month([AUD_due])= " & intMonth & " AND Year([AUD_due]) = " & MyYear & " AND ACTIVE=-1" & _
" ORDER BY ApptStartTime;"

[matthewspatrick]

Hm, try:








strSQL = "SELECT Audit_Schedule.* " & _"FROM Audit_Schedule " & _

"WHERE ([AUD_due] BETWEEN #" & DateSerial(MyYear, intMonth, 1) & # AND #" & _
DateSerial(MyYear, intMonth + 1, 0) & "# AND [Active] = True" & _
" ORDER BY ApptStartTime;"

[matthewspatrick]

BTW, the reason I tossed the Month() and Year() functions and went with the BETWEEN...AND structure is that, if your table is indexed on the AUD_due field, the query will run much faster that way (by using Month and Year, you keep the index from doing its job).

Patrick

[boneKrusher]

Thanks everyone. This is what worked:

strSQL = "SELECT Audit_Schedule.* " & _
"FROM Audit_Schedule " & _
"WHERE active = -1 AND Month([AUD_due])= " & intMonth & " AND Year([AUD_due]) = " & MyYear & _
" ORDER BY ApptStartTime;"