Syntax Error in Expression

[Xproterg]

Evening all,

I could really use some expertise on the query below. It selects matches between two days (3 sets of two dates actually, depending on if the next set is empty or not); however, I get a syntax error in query expression when i open the form and it doesn't seems to work at all. Any help would be greatly appreciated.

strSQL6 = "
 
 
SELECT Students_Info.firstname, Students_Info.lastname FROM Students_Info WHERE 
 
(
(#" & janumonthly & "# BETWEEN 
DateAdd('m',1,Students_Info.startONE) AND 
DateAdd('m',-1,Students_Info.gradONE)
) 
and Students_Info.gradTWO Is Null) 
 
OR 
 
(
(#" & janumonthly & "# BETWEEN 
DateAdd('m',1,Students_Info.startTWO) AND 
DateAdd('m',-1,Students_Info.gradTWO)
) 
and Students_Info.gradTHR Is Null) 
 
OR 
 
(#" & janumonthly & "# BETWEEN 
DateAdd('m',1,Students_Info.startTHR) AND 
DateAdd('m',-1,Students_Info.gradTHR)
);"

Or if you'd prefer to read it in its natural form, look below:

strSQL6 = "SELECT Students_Info.firstname, Students_Info.lastname FROM Students_Info WHERE ((#" & janumonthly & "# BETWEEN DateAdd('m',1,Students_Info.startONE) AND DateAdd('m',-1,Students_Info.gradONE)) and Students_Info.gradTWO Is Null) OR ((#" & janumonthly & "# BETWEEN DateAdd('m',1,Students_Info.startTWO) AND DateAdd('m',-1,Students_Info.gradTWO)) and Students_Info.gradTHR Is Null) OR (#" & janumonthly & "# BETWEEN DateAdd('m',1,Students_Info.startTHR) AND DateAdd('m',-1,Students_Info.gradTHR));"

Thanks in advance!

 

[spikepl]

1. When querying one table only, there is no need to refer to the table name for each field
2. debug.print your sqlstring and inspect it
3. When 547 things put together give you an error, then deconstruct your construction. Add one bit at a time and make sure all works before adding the next bit
4. You have a built-in syntax checker in the query builder. Paste your code in the SQLview of the query designer and see what it says