SQL Query and VB 6 Help

Hey Guys I need help with SQL and VB 6.

What I am trying to do is make a query that tells me how has not visited a shop with a time period. I am using 2 tables in my query. Here is the code I have so far

Dim sqlstring As String
Dim myRS As New ADODB.Recordset
Dim LastMonth As Date
LastMonth = Date - 31

Set myConnection = New ADODB.Connection

myConnection.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;data source=" & App.Path & "DatabaseDatabase.mdb" & ";"

sqlstring = "SELECT C1.CSURNAME, C1.CFIRST FROM CLIENT C1 WHERE NOT EXISTS (SELECT *FROM CLIENT C2, TREAT T WHERE C2.CCODE = C1.CCODE AND T.CCODE = C2.CCODE ANDT.DATE > " & LastMonth & ")"
myRS.CursorLocation = adUseClient

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

But there is an Syntax error in the query somewhere and I cant see it. I cant find any websites to help me!

So you guys are my last hope!!!

Thanks

Skyaxe

Comments

  • : Hey Guys I need help with SQL and VB 6.
    :
    : What I am trying to do is make a query that tells me how has not visited a shop with a time period. I am using 2 tables in my query. Here is the code I have so far
    :
    : Dim sqlstring As String
    : Dim myRS As New ADODB.Recordset
    : Dim LastMonth As Date
    : LastMonth = Date - 31
    :
    : Set myConnection = New ADODB.Connection
    :
    : myConnection.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;data source=" & App.Path & "DatabaseDatabase.mdb" & ";"
    :
    : sqlstring = "SELECT C1.CSURNAME, C1.CFIRST FROM CLIENT C1 WHERE NOT EXISTS (SELECT *FROM CLIENT C2, TREAT T WHERE C2.CCODE = C1.CCODE AND T.CCODE = C2.CCODE ANDT.DATE > " & LastMonth & ")"
    : myRS.CursorLocation = adUseClient
    :
    : 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
    :
    : But there is an Syntax error in the query somewhere and I cant see it. I cant find any websites to help me!
    :
    : So you guys are my last hope!!!
    :
    : Thanks
    :
    : Skyaxe
    :





    look like 2 spaces missing for *FROM and ANDT.DATE

    sqlstring = "SELECT C1.CSURNAME, C1.CFIRST FROM CLIENT C1 WHERE NOT EXISTS (SELECT *FROM CLIENT C2, TREAT T WHERE C2.CCODE = C1.CCODE AND T.CCODE = C2.CCODE ANDT.DATE > " & LastMonth & ")"


    *FROM [should be]-> *_FROM

    ANDT.DATE > " & LastMonth [should be]-> AND_T.DATE > " & LastMonth
  • : Hey Guys I need help with SQL and VB 6.
    :
    : What I am trying to do is make a query that tells me how has not visited a shop with a time period. I am using 2 tables in my query. Here is the code I have so far
    :
    : Dim sqlstring As String
    : Dim myRS As New ADODB.Recordset
    : Dim LastMonth As Date
    : LastMonth = Date - 31
    :
    : Set myConnection = New ADODB.Connection
    :
    : myConnection.Open "PROVIDER=Microsoft.jet.OLEDB.4.0;data source=" & App.Path & "DatabaseDatabase.mdb" & ";"
    :
    : sqlstring = "SELECT C1.CSURNAME, C1.CFIRST FROM CLIENT C1 WHERE NOT EXISTS (SELECT *FROM CLIENT C2, TREAT T WHERE C2.CCODE = C1.CCODE AND T.CCODE = C2.CCODE ANDT.DATE > " & LastMonth & ")"
    : myRS.CursorLocation = adUseClient
    :
    : 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
    :
    : But there is an Syntax error in the query somewhere and I cant see it. I cant find any websites to help me!
    :
    : So you guys are my last hope!!!
    :
    : Thanks
    :
    : Skyaxe
    :

    hi,

    you build your query first as sql view in sql and then if its run ok , copy and paste the sql statement in to your code. OR you can visit this link and search for your request www.softricks.5u.com
  • Hey you guys are the best my query is now working.

    How do i check my SQL syntax in visual basic 6 I found the where to check it but all the query menu is greyed out.

    Thanks again guys ure the best!!!

    Skyaxe
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories