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
morrisboAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
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
0
morrisboAuthor Commented:
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
0
morrisboAuthor Commented:
The other attachments - don't know if they got uploaded. They show that the column PublicorPrivate in Table Notes is a long integer.jpg shows the column PublicorPrivate. It is a long integerjpg shows the column PublicorPrivate. It is a long integer
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

morrisboAuthor Commented:
shows PublicorPrivate field as a long integershows PublicorPrivate field as a long integer
0
Nick67Commented:
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) & ")"
0
Nick67Commented:
Personally I hate _
I like throwing in msgbox to see what's happening, for one
For another, there are enough ampersands without those necessitated by _
And finally, each line is now a logical phrase, and I think more readable.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Brendt HessSenior DBACommented:
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.
0
morrisboAuthor Commented:
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.
0
morrisboAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.