Howdy, Stranger!

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

Sign In with Facebook Sign In with Google Sign In with OpenID

Categories

We have migrated to a new platform! Please note that you will need to reset your password to log in (your credentials are still in-tact though). Please contact lee@programmersheaven.com if you have questions.
Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

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.