Using in Between to Compare Numbers in SQL Statement

[Lrn2Code]

Here I am again with my problem du jour!

I'm trying to select all records in a table where an educator ID is = to an exact value but the courseid numbers are between two values. I've tried multiple ways to reference the numbers (with and without ", &, and values with () ) and I keep getting a syntax error.

Have searched this site but most of the answers I'm finding relate to date values and that's not what I'm looking for.

If someone could tell me what I'm missing/doing wrong that'd be fantastic. Here's my latest iteration of the code -

strS5 = "Select * from tblTeacher Courses where EducatorID = " & lngThisID & " And courseid => " & 5053000 & " and =< " & 5059000 & ""
Debug.Print (strS5)
Set rst5 = dbs.OpenRecordset(strS5) - This line is where the syntax error happens. Error 3075 missing operator.
If rst5.RecordCount < 1 Then
sql = "insert into data_error_log (errdesc, teachername,suid,posid) values (' Educator has an Alt Prog Teacher role, does not have any associated alt prog classes', '" & tname & "','" & emporg1 & "','" & strPOSID & "')"
Set qd = db.CreateQueryDef("", sql)
qd.Execute
cleandata = False
End If

Variables are declared -

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim sql As String

Dim rst2 As DAO.Recordset
Dim rst As DAO.Recordset
Dim rstSPED As DAO.Recordset
Dim dbs As DAO.Database
Dim rst3 As DAO.Recordset
Dim rst4 As DAO.Recordset
Dim rst5 As DAO.Recordset

Dim strS3 As String
Dim strS4 As String
Dim strS5 As String
Dim strConsult As String
strConsult = "Consulting"
Dim strPrimary As String
strPrimary = "Primary"
Dim intHasCourse As Integer
Dim strPOS As String
Dim strPOSID As String
Dim strThisID As Long
Dim strS As String

Dim counter1 As Integer
Dim intLookForAP As Integer

Dim tname As String
Dim sqlText As String
Dim emporg1 As String
Dim lngEdID As Long
Dim lngClassID As Long
Dim dontbuild As Integer

Dim cleandata As Boolean
cleandata = True

Set dbs = CurrentDb
Set db = CurrentDb

Thanks for your time and guidance!

 

[Darth Vodka]

take whatever

Debug.Print (strS5)

gives you and copy that into query bulder in the sql design...then change that till it works

 

[Rabbie]

Try using

courseid between 5053000 and 5059000

It worked for me when the field is integer type.

 

[Lrn2Code]

Thank you for your guidance. I ended up grabbing a co-worker to help me with the formatting issue. We ran the SQL in a query and came up with the following - Select * from tblTeacherCourses where (EducatorID = " & lngThisID & ") And (CourseID > 50529999 and CourseID < 50600000)

I had to change the EducatorID reference to be dynamic to the loop it is in and the course ID didn't have enough digits in it to work right. From what I can gather it is reading the code correctly now, at least I'm not getting the syntax error anymore.

Do you see any other issues with it?

Thanks for your reply!

 

[gemma-the-husky]

(CourseID > 50529999 and CourseID < 50600000)

you can also say
CourseID between 50529999 and 50600000

 

[Rabbie]

you can also say
CourseID between 50529999 and 50600000

Think I already said that in post 3.

 

[Lrn2Code]

Thank you both. I get so confused trying to understand how items are referenced in code, sometimes it's '" & & "' sometimes it's just the item itself, sometimes it's () with [ ] and " ". Just can't seem to get a grip on all that yet!

Can't thank you enough for your quick replies. Sure hope that someday soon I can offer assistance to others who are struggling.

Have a great day!

 

[Rabbie]

It's not as complicated as it looks at first. Numbers don't have quotes round them - Character strings do.

[] are used to surround field names especially needed if you have spaces in them which shouldn't be done.

() are used to indicated the order you want an expression evaluated - starting inside the brackets and working out.