SQL Query and VB 6 Help - Programmers Heaven

Howdy, Stranger!

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

Categories

SQL Query and VB 6 Help

SkyaxeSkyaxe Posts: 5Member
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

  • dokken2dokken2 Posts: 532Member
    : 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
  • softrickssoftricks Posts: 24Member
    : 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
  • SkyaxeSkyaxe Posts: 5Member
    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.