Query won't return record with Null fields

cmM
10-13-2004, 09:03 AM
This query:

Set MyRecSet = MyConn.Execute("SELECT * FROM ToolsMain " & _
"WHERE ToolID like '%" & lsToolID & "%'" & _
"AND ToolNumber like '%" & lsToolNumber & "%'" & _
"AND Machine like '%" & lsMachine & "%'" & _
"AND Station like '%" & lsStation & "%'" & _
"AND ToolType like '%" & lsToolType & "%'" & _
"AND BoltOD like '%" & lsBoltOD & "%'" & _
"AND Description like '%" & lsDescription & "%'" & _
"AND APBP like '%" & lsAPBP & "%'")


Only returns records from my Access DB that don't containg Null Values in their fields. All those variables are text from text boxes and drop down lists.

Anyone know why?

couch612
10-13-2004, 11:54 PM
change your ANDs to ORs.

NEOLLE
10-14-2004, 12:26 AM
It wont return a record with a null value if that record does not meet the condition stated in you Query Statement.

Just a suggestion. User Your RecordSet Object in Query.
Ex. rs.Open strSQL,dbConnection,AddOpenForwardOnly,AdLockReadOnly

Use you Connection Object when using DML Commands (INSERT,UPDATE and DELETE). :)

NEOLLE
10-14-2004, 12:29 AM
change your ANDs to ORs.

Replacing ANDs to ORs will will change the meaning of you condition! Be careful, you might end up retrieving a different result. :)

Norenca
10-14-2004, 08:21 AM
it's very simple: you query the table for records where one of the fields contain the words in your query. "Null" does not have any of those words, so you don't get the records with "Null" in them.

You actually ASK in your sql-query not to show records where the value is set to null. You could solve this by adding something like " OR field = Null" or " OR field = '' "

cmM
10-14-2004, 09:41 AM
I am querrying the database for records based on input boxes, 8 of them.
I am retrieving records WHERE fields LIKE %inputbox% AND...
Initially my input boxed are empty (Null)... shouldn't that retrieve all records including ones containing Null values?

If I change AND to OR, then the querry will return all the records, no matter what.
This:
Set MyRecSet = MyConn.Execute("SELECT * FROM ToolsMain " & _
"WHERE ToolID like '%" & lsToolID & "%'" & _
"AND ToolNumber = Null OR ToolNumber like '%" & lsToolNumber & "%'" & _
"AND Machine = Null OR Machine like '%" & lsMachine & "%'" & _
"AND Station = Null OR Station like '%" & lsStation & "%'" & _
"AND ToolType = Null OR ToolType like '%" & lsToolType & "%'" & _
"AND BoltOD = Null OR BoltOD like '%" & lsBoltOD & "%'" & _
"AND Description = Null OR Description like '%" & lsDescription & "%'" & _
"AND APBP = Null OR APBP like '%" & lsAPBP & "%'")
(adding the FieldName = Null) doesn't do anything.

Granty
10-14-2004, 09:50 AM
Try....



Set MyRecSet = MyConn.Execute("SELECT * FROM ToolsMain " & _
"WHERE ToolID like '%" & lsToolID & "%'" & _
"AND (ToolNumber IS Null OR ToolNumber like '%" & lsToolNumber & "%')" & _
"AND (Machine IS Null OR Machine like '%" & lsMachine & "%')" & _
"AND (Station IS Null OR Station like '%" & lsStation & "%')" & _
"AND (ToolType IS Null OR ToolType like '%" & lsToolType & "%')" & _
"AND (BoltOD IS Null OR BoltOD like '%" & lsBoltOD & "%')" & _
"AND (Description IS Null OR Description like '%" & lsDescription & "%')" & _
"AND (APBP IS Null OR APBP like '%" & lsAPBP & "%')")

cmM
10-14-2004, 10:16 AM
I tried that, and I get a runtime error:
"No value given for one or more required parameters"

Either way, I'm not sure adding "OR" is the way to go. If I do that, then wouldn't the query result in records that either meet the criteria (the input box), or are empty. When I enter something in the text box, I want the query to return the records that match that, not those records AND all the null ones. I wans it to return the records that match that box even though some of those records contain null values in other fields.

here's how it looks like, to give you a better idea of what I am trying to accomplish:
http://www.cmuntean.net/files/bmdb.jpg

Granty
10-14-2004, 10:22 AM
Ahhh you are doing a search screen?

You need to build up your SQL string dynamically...

So you start with your opening strSQL = "SELECT * FROM MyTable WHERE "

Then you go thru each of your boxes and if any of them contain criteria, add that criteria to the WHERE condition.


If not MyTextbox IS NULL and MyTextbox <> "" THEN

strSQL = strSQL & "MyField = '" & MyTextbox & "' AND "

End if



Make sure to chop off the last " AND "

cmM
10-14-2004, 10:51 AM
I did that and I get a runtime error:
"object required" (if statement highlited)

strSQL = "SELECT * FROM ToolsMain WHERE "

If Not txtToolID Is Null And txtToolID <> "" Then
strSQL = strSQL & "ToolID = '" & lsToolID & "' AND "
End If

If Not txtToolNumber Is Null And txtToolNumber <> "" Then
strSQL = strSQL & "ToolNumber = '" & lsToolNumber & "' AND "
End If

If Not cmbMachine Is Null And cmbMachine <> "" Then
strSQL = strSQL & "Machine = '" & lsMachine & "' AND "
End If

If Not cmbStation Is Null And cmbStation <> "" Then
strSQL = strSQL & "Station = '" & lsStation & "' AND "
End If

If Not cmbToolType Is Null And cmbToolType <> "" Then
strSQL = strSQL & "ToolType = '" & lsToolType & "' AND "
End If

If Not txtBoltOD Is Null And txtBoltOD <> "" Then
strSQL = strSQL & "BoltOD = '" & lsBoltOD & "' AND "
End If

If Not txtDescription Is Null And txtDescription <> "" Then
strSQL = strSQL & "Description = '" & lsDescription & "' AND "
End If

If Not txtAPBP Is Null And txtAPBP <> "" Then
strSQL = strSQL & "APBP = '" & lsAPBP & "'"
End If

Set MyRecSet = MyConn.Execute(strSQL)
:confused: :(

Granty
10-14-2004, 10:58 AM
What are these lsToolID, lsToolNumber etc things?

cmM
10-14-2004, 11:14 AM
What are these lsToolID, lsToolNumber etc things?
lsToolID = txtToolID.Text
lsToolNumber = txtToolNumber.Text
lsMachine = cmbMachine.Text
lsStation = cmbStation.Text
lsToolType = cmbToolType.Text
lsBoltOD = txtBoltOD.Text
lsDescription = txtDescription.Text
lsAPBP = txtAPBP.Text

string variables

Granty
10-14-2004, 11:30 AM
Ok, cool you have string variables :)

If lsToolID <> "" Then
strSQL = strSQL & "ToolID = '" & lsToolID & "' AND "
End If

HardCode
10-14-2004, 12:08 PM
Your SQL should only check where ToolID equals the value on the form. If you are having the user supply all of the data, then what is the purpose of a search?

cmM
10-14-2004, 12:50 PM
HardCode, that ToolID is my primary Key. It would be ideal searching by that, but unfortunately that won't be available many times. That's why I have the other fields to search by and filter the results as much as possible.

Granty, you are a friggin genius! Thank you very much
I had to add a nonsense condition to my querry though because otherwise it would complain if the last text box is empty.
strSQL = strSQL & "ToolID > 0"

Thanks a lot for takin the time and helping me out.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum