SQL between query and MS Access Help in VB
Posted on 2006-11-24
Hey there Experts
I have a SQL query that fills a record set and then displays the result in a text box but the query is not working the syntax I think is right but the record set does not get filled.
Here is the code
Dim sqlstring As String
Dim myRS As New ADODB.Recordset
Set myConnection = New ADODB.Connection
myConnection.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;data source=" & App.Path & "\Database\Database.mdb" & ";"
myRS.CursorLocation = adUseClient
sqlstring = "SELECT CLIENT.CSURNAME,CLIENT.CFIRST,CLIENT.STREET,CLIENT.STREET2 FROM CLIENT, TREAT WHERE TREAT.DATE BETWEEN " & CDate(CustomStartDate) & " AND " & CDate(CustomEndDate) & ""
Set myRS = myConnection.Execute(sqlstring)
Do Until myRS.EOF
If IsNull(myRS!Street2) = True Then
txtResults.SelText = myRS!CFIRST + " " + myRS!CSURNAME + " " + myRS!Street + vbCrLf
txtResults.SelText = myRS!CFIRST + " " + myRS!CSURNAME + " " + myRS!Street + " " + myRS!Street2 + vbCrLf