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
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
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.PublicOrPrivat e)= 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
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.PublicOrPrivat
& "(((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
ASKER
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) & ")"
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.PublicOrPrivat e)= 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.PublicOrPrivat e)= " & 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.
sWhere = "WHERE (((TblNotes.Subject)>= " & Chr(39) & Nme & Chr(39) And " _
& "((TblNotes.PublicOrPrivat
& "(((TblNotes.Subject)>= " & Chr(39) & Nme & Chr(39) And "" _
& "((TblNotes.LoginName)= " & Chr(39) & logname & Chr(39)
You need to change the bolded line to:
& "((TblNotes.PublicOrPrivat
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.
ASKER
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.
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.
ASKER
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
morrisbo
Mike