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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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.
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

morrisboAuthor Commented:
shows PublicorPrivate field as a long integershows PublicorPrivate field as a long integer
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) & ")"
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) & ")"

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.
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.
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.

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

From novice to tech pro — start learning today.