Using BETWEEN inSQL

[gvirden]

Do I need to use some special characters in this statement. There is something wrong in the BETWEEN segment.


"SELECT descript30, glaccount FROM accounts WHERE glaccount BETWEEN 3999 AND 7999 ORDER BY descript30"

[Mongo]

If glaccount is a text/string field, you would need to add tick marks or quotes around your values (BETWEEN '3999' AND '7999'), though, were that the case, you should have gotten a data type mismatch error. Could you give a few more details about where and how you're trying to run your query?

[gvirden]

What I am attempting to do is load 8 combo boxes from access, 4 require accounts and 4 require expenses. Many of the accounts are not applicable so I was going to narrow the list. The field glaccount is an integer. I'm guessing this code is unacceptable to VB. Maybe I should break it down to seperate routines? I can get the application to run ok but it will not sort that sSQL statement properly.

Dim rsAcc1 As ADODB.Recordset
Dim rsAcc2 As ADODB.Recordset
Dim rsAcc3 As ADODB.Recordset
Dim rsAcc4 As ADODB.Recordset
Dim rsEx1 As ADODB.Recordset
Dim rsEx2 As ADODB.Recordset
Dim rsEx3 As ADODB.Recordset
Dim rsEx4 As ADODB.Recordset
Dim sSQL As String
Dim sSQL1 As String
Dim sSQL2 As String
Dim sSQL3 As String

sSQL1 = "SELECT vendorname FROM vendor ORDER BY vendorname"
sSQL2 = "SELECT descript30, glaccount FROM accounts WHERE glaccount BETWEEN 3999 AND 7999 ORDER BY descript30"
sSQL3 = "SELECT expense FROM expenses ORDER BY expense"

Set rsVendor = GetRecordSet(sSQL1)
Call ListFillFromRecordset(CmbVendor, rsVendor)

Set rsAcc1 = GetRecordSet(sSQL2)
Call ListFillFromRecordset(cmbacc1, rsAcc1)

Set rsAcc2 = GetRecordSet(sSQL2)
Call ListFillFromRecordset(cmbacc2, rsAcc2)

Set rsAcc3 = GetRecordSet(sSQL2)
Call ListFillFromRecordset(cmbacc3, rsAcc3)

Set rsAcc4 = GetRecordSet(sSQL2)
Call ListFillFromRecordset(cmbacc4, rsAcc4)

Set rsEx1 = GetRecordSet(sSQL3)
Call ListFillFromRecordset(cmbEx1, rsEx1)

Set rsEx2 = GetRecordSet(sSQL3)
Call ListFillFromRecordset(cmbEx2, rsEx2)

Set rsEx3 = GetRecordSet(sSQL3)
Call ListFillFromRecordset(cmbEx3, rsEx3)

Set rsEx4 = GetRecordSet(sSQL3)
Call ListFillFromRecordset(cmbEx4, rsEx4)

[Mongo]

Your problem with the BETWEEN statement has me confused...
Are the correct records returned to your combobox,
but not sorted (sorted property set true)?

[gvirden]

Sorry, the error was caused by a misspelling. One of those days I guess.