asked on

SQL between query and MS Access Help in VB

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
Else
txtResults.SelText = myRS!CFIRST + " " + myRS!CSURNAME + " " + myRS!Street + " " + myRS!Street2 + vbCrLf
End If
myRS.MoveNext
Loop

myRS.Close

Thanks

Nothern_Lights

rockiroads

try using hashes

sqlstring = "SELECT CLIENT.CSURNAME,CLIENT.CFIRST,CLIENT.STREET,CLIENT.STREET2 FROM CLIENT, TREAT WHERE TREAT.DATE BETWEEN #" & CDate(CustomStartDate) & "# AND #" & CDate(CustomEndDate) & "#"

other way is to use format

sqlstring = "SELECT CLIENT.CSURNAME,CLIENT.CFIRST,CLIENT.STREET,CLIENT.STREET2 FROM CLIENT, TREAT WHERE Format(TREAT.DATE,'YYYYMMDD') BETWEEN " & Format(CDate(CustomStartDate),"YYYYMMDD") & " AND " & Format(CDate(CustomEndDate),"YYYYMMDD")

sqlstring = "SELECT CLIENT.CSURNAME,CLIENT.CFIRST,CLIENT.STREET,CLIENT.STREET2 FROM CLIENT, TREAT WHERE Format(TREAT.DATE,'YYYYMMDD') >= " & Format(CDate(CustomStartDate),"YYYYMMDD") & " AND Format(TREAT.DATE,'YYYYMMDD') <= " & Format(CDate(CustomEndDate),"YYYYMMDD")

Nothern_Lights

ASKER

hey rockiroads

The code you gave me is just displaying the whole client table multiple times.

What I need the SQL query to do is to take the customstartdate and the customenddate and search the client and treat table and findout what clients have visited the shop within the dates specified

Nothern_Lights

rockiroads

ok, what links the client and the treat table?
u need to perform the join

SELECT CLIENT.CSURNAME,CLIENT.CFIRST,CLIENT.STREET,CLIENT.STREET2
FROM CLIENT, TREAT
WHERE CLIENT.somefeld = TREAT.somefield

then u do the filter on the TREAT date
use the filter that worked for u, but dont start with WHERE, put a AND as we already have a WHERE

Nothern_Lights

ASKER

So would the query be

sqlstring = "SELECT CLIENT.CSURNAME,CLIENT.CFIRST,CLIENT.STREET,CLIENT.STREET2,MAX(TREAT.DATE) FROM CLIENT, TREAT WHERE CLIENT.CCODE = TREAT.CCODE AND TREAT.DATE BETWEEN #" & CDate(CustomStartDate) & "# AND #" & CDate(CustomEndDate) & GROUP BY CLIENT.CSURNAME,CLIENT.CFIRST,CLIENT.STREET,CLIENT.STREET2 "

rockiroads

Yes, u need to join the two fields and use the appropriate filter on the dates that worked for you

Nothern_Lights

ASKER

Ok I have made my query but it does not fill the recordset

sqlstring = "SELECT CLIENT.CSURNAME,CLIENT.CFIRST,CLIENT.STREET,CLIENT.STREET2 FROM CLIENT, TREAT WHERE CLIENT.CCODE = TREAT.CCODE AND TREAT.DATE >= " & CDate(CustomStartDate) & " AND TREAT.DATE <= " & CDate(CustomEndDate) & " GROUP BY CLIENT.CSURNAME,CLIENT.CFIRST,CLIENT.STREET,CLIENT.STREET2"

and i have tried

sqlstring = "SELECT CLIENT.CSURNAME,CLIENT.CFIRST,CLIENT.STREET,CLIENT.STREET2 FROM CLIENT, TREAT WHERE CLIENT.CCODE = TREAT.CCODE AND TREAT.DATE >= #" & CDate(CustomStartDate) & "# AND TREAT.DATE <= #" & CDate(CustomEndDate) & "# GROUP BY CLIENT.CSURNAME,CLIENT.CFIRST,CLIENT.STREET,CLIENT.STREET2"

HELP btw i have never used SQL b4

Nothern_Lights

ASKER CERTIFIED SOLUTION

rockiroads

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

taycuong76

Rockiroads's tutorial is clearly and easy to understand.

Anthony Perkins

>>.Execute is used for action queries like insert/delete for select's u need to use Open<<
You may want to double check this. There is nothing wrong with using Execute to return a recordset. In fact, providing a firehose (forward-only, read-only) cursor can be used it will give you the best performance.

Nothern_Lights

ASKER

Hey there rockiroads

I put in the code you posted and added my sqlstring i got an error at myRS.Open sqlstring saying

Run-time error `3709`:

"The connection cannot be used to perform this operation. It is wither closed or invalid in this context"

rockiroads

Sorry mate, I stopped using EE for a short while then had a massive catch up.

Regarding this error, I forgot to add the connection object to when u open the recordset

it should be

myRS.Open sqlstring, myConnection