Link to home
Start Free TrialLog in
Avatar of morrisbo
morrisbo

asked on

vb6 sql statement getting error message vb6 - sql - access 2000 - ado

Having problem constructing sql statement in a vb6 program involving access 2000 in a select, from, where, order construct also using ado.

I have included 7 attachments that hopefully describes the situation in enough detail.

Have worked on this for several hours and just can't seem to make it work.

Any help from more expert advisors will be greatly appreciated.

Also will be more than happy to furnish any additional information to help solve the problem.
error-message-when-sql-code-ran.jpg
query-detail-view.jpg
second-error-message.jpg
sql-view.jpg
TblNotes-design-mode.jpg
TblNotes-table-documentor.jpg
TblNotes-table.jpg
Avatar of Mike Eghtebas
Mike Eghtebas
Flag of United States of America image

remove all parameters to see if it runs okay. Then add one parameter at a time to find which of the parameter causes the error.

Mike
Avatar of morrisbo
morrisbo

ASKER

Still having problems with the complex sql statement.

Here is the most recent sql statement code:

    sSelect = "SELECT TblNotes.TblNotesID, TblNotes.Subject, TblNotes.Comments, TblNotes.RecallDate, TblNotes.LoginName, TblNotes.PublicorPrivate "
    sfrom = "From TblNotes "
   
 ' this works   sWhere = "WHERE TblNotes.Subject >= " & Chr(39) & Nme & Chr(39)
   
     sWhere = "WHERE (((TblNotes.Subject)>= " & Chr(39) & Nme & Chr(39) And "" _
          & "((TblNotes.PublicOrPrivate)= PuborPriv )) OR " _
          & "(((TblNotes.Subject)>= " & Chr(39) & Nme & Chr(39) And "" _
          & "((TblNotes.LoginName)= " & Chr(39) & logname & Chr(39)

'logname = "Morris"
'PuborPriv = 0
 
    sOrder = "ORDER BY TblNotes.Subject;"

    sSQL = sSelect & " " & sfrom & sWhere & " " & sOrder

The error that I am getting suggests a syntax error

"Type mismatch in NotesHelp_DisplayResults procedure"

I get this message as soon as the above select - from - where clause is executed.

Just can't find the correct syntax.

I verified that the code in the routine where the error is occurring is pointing to the correct data base, but pretty sure the problem is with how I have constructed the where clause.
SQL-view-Query.jpg
The other attachments - don't know if they got uploaded. They show that the column PublicorPrivate in Table Notes is a long integer.User generated imageUser generated image
User generated imageUser generated image
Avatar of Nick67
this works   sWhere = "WHERE TblNotes.Subject >= " & Chr(39) & Nme & Chr(39)

Chr(39) is single quotes
Generally, string variables need to be enclosed in Double quotes
That's Chr(34)

I take it you are building up a string to execute in code
I see valid dummy values for
'logname = "Morris"
 'PuborPriv = 0

Can you post one for Nme?

So try this to start.
Access adds craploads of () but they aren't needful except those that delineate your Booleans.
You were missing ampersands near your " AND " bits

     sWhere = "WHERE (TblNotes.Subject>= " & Chr(34) & Nme & Chr(34) & " And " _
           & "TblNotes.PublicOrPrivate = " & PuborPriv & ") OR (" _
           & "TblNotes.Subject >= " & Chr(34) & Nme & Chr(34) & " And " _
           & " TblNotes.LoginName = " & Chr(34) & logname & Chr(34) & ")"

A dummy statement if Nme is something like "Nick67" would be


     sWhere = "WHERE (TblNotes.Subject>= " & Chr(34) & "Nick67& Chr(34) & " And " _
           & "TblNotes.PublicOrPrivate = 0) OR (" _
           & "TblNotes.Subject >= " & Chr(34) & "Nick67" & Chr(34) & " And " _
           & " TblNotes.LoginName = " & Chr(34) & "Morris" & Chr(34) & ")"
ASKER CERTIFIED SOLUTION
Avatar of Nick67
Nick67
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This looks like a forehead slapper, but the error is not actually that uncommon. In your WHERE clause:

sWhere = "WHERE (((TblNotes.Subject)>= " & Chr(39) & Nme & Chr(39) And " _
& "((TblNotes.PublicOrPrivate)= PuborPriv )) OR " _
 & "(((TblNotes.Subject)>= " & Chr(39) & Nme & Chr(39) And "" _
 & "((TblNotes.LoginName)= " & Chr(39) & logname & Chr(39)

You need to change the bolded line to:

& "((TblNotes.PublicOrPrivate)= " & PuborPriv & " )) OR " _

In the first case, you are attempting to compare the string PuborPriv to the field PublicOrPrivate. In the second, you are attempting to compare the value stored in the variable PuborPriv to the field PublicOrPrivate.
Thanks to nick67 and also bhess1.  nick67 , I inserted your code sample suggestion and it worked perfectly. Also, I like your tip about breaking up the sWhere string into several statements. Makes it look much simpler. I had labored over this for several hours. Just not proficient with ms access sql syntax.

Nick67 - I appreciate your comments. I believe with the changes you suggested the code would have worked also.

Your help and expertise provided the solution I needed.

Thanks guys - made my day.
I have posted a new follow up question to this question, concerning adding new filter statements in the where clause to limit the items displayed to those notes qualifying based on the column Recall Date in the TblNotes table. The follow up question was posted today with one response but still trying to correct syntax in the sql where clause. Not sure what the question ID is to the followup post.

morrisbo