SQL statement using the "OR" 1

[HebieBug]

Have looked through the books once again.. Funny how they seem to just breeze through the topic that you want to look at.. They mention it but don't show an example
Have been working with a reporting system which gives on screen displays insteed of printing out pages of reports..
Using an Access database.. One of the fileds we would like to show if the field = 1 or = 6.. So I tried it as many ways as I could it seems to work with O_Options with no problem but as soon as e add the other fields to check then it will not work..
Anyone have any suggestions
Coe is shown below
strSql = &quot;Select * from User_Details2 WHERE _ (O_Options = 1 OR O_Options = 6) and Inactive <> 2 and _ FolderType <> White &quot;

 

[lionelbrown]

Are you receiving an error message? If so, what is it?


If the above code is exactly as you have it in your app, and FolderType is a text field in the database, then you will have to put quotes around White (FolderType <> 'White').

 

[HebieBug]

error message:
Run-time error '-2147217904(80040e10)':
No value given for one or more parameters.
The error will appear on the connection string.. If I remove the OR statement and leave the rest then it will work perfectly, Or if the OR statement stays and I delete the rest of the SQL statement it works... It is just bringing it all togeather
strSql = &quot;Select * from User_Details2 WHERE _
(O_Options = 1 OR O_Options = 6) and _
(Inactive <> 2) and (FolderType <>_ White)&quot;
aMainrecord.CursorLocation = adUseClient
aMainrecord.Open _ strSql, &quot;Provider=Microsoft.Jet.OLEDB.4.0;&quot; _
& &quot;Data Source=c:\CTSDATA.mdb&quot;, adOpenStatic, adLockUnspecified --> error message appears on this line of code
No problems with the connection string it seems to me that it is the representation of the SQL statement

 

[lionelbrown]

I set up a quick test and ran your code. Is FolderType a text field in the database?? It worked fine for me. I assumed that folder type was a text field and quoted it in strSql (strSql = &quot;Select * from User_Details2 WHERE (O_Options = 1 OR O_Options = 6) and (Inactive <> 2) and (FolderType <> 'White')&quot;

When I removed the '' around White I got the same error message you are receiving due to the data type mismatch when you try to run the query. Are you building strSql dynamically? If so, put a message box (or trace the code) to see strSql before you try to open the recordset. You will then be able to determine if it is generated a valid sql statement. You could always paste strSql into an access query to see if it will run from there, so you can pinpoint if the problem is with the syntax or an issue with VB passing the query to access. By the way, I ran it on an Access 97 db.

 

[HebieBug]

Bingo.. Working perfectly
Lovley to see the internet gangsters are fully operational

 

[Guest_imported]

I got the error
Run time error '-2147217904(80040e10)':
No Value given for one or more required parameters.
and my query statement is like this
sql = &quot;select name from distributor &quot;
sql = &quot;where code = &quot; & txtagentcode.text

please help me regarding this

 

[dpaulson]

Two errors. You have to concentate the sql strings. Strings must be enclosed in the single ticks.

sql = &quot;select name from distributor &quot;
sql = sql & &quot;where code = '&quot; & txtagentcode.text & &quot;'&quot;


David Paulson