SQL Parameter Query Code & Nulls

  • Thread starter Thread starter Ben Bayliss
  • Start date Start date
B

Ben Bayliss

Hi All,

I'm having a problem fixing this query.

Basically as the code suggests, I'd like to construct a query's
parameters on the fly depending on what the user chooses to search by.

It works as far as all of the fields that HAVE to contain data in the
table (Catagory, Manufacturer, Product, Building, Room, Status) return
correctly. I'm having problems with the remaining fields, where Null is
a valid entry in the master table (some items dont have serial numbers,
we dont remember where we bought certain items etc..).

Can someone please suggest how to amend this code to return null results
from the table when no parameter is specified. Obviously the amendment
needs to go into the IsNull=TRUE portion of each parameter, since I *DO
NOT* want null results returning if the user has specified a search
parameter.

I don't think it's very hard but I think I've been putting my brackets
in the wrong places etc.. The code is attached below.

Thanks very much,
Ben.
 
Ben,

Instead of putting all the criteria in for those columns that have no
criteria defined, why not just build the query to include only those fields
that have a critiera? The other thing you need to be concerned with is that
if you have entered a criteria and then delete it, the text box might not
test out as ISNULL() = True, it might contain a zero length string (""),
which has zero length but is not NULL.

Try something like the following. This code defines your criteria as a
variant. This allows you to concatenate an " AND " to it using the +
operator. When you do this, if varCriteria is NULL, then the resulting
string will be a Null. Since you don't know whether there will be any
criteria entered, you could go all the way thru all of your criteria boxes
and never have one filled in. If that is the case, then when you
concatenate the "WHERE " to the Criteria (also using the + operator) you may
end up with a SQL string without a WHERE clause. The reason for adding the
"" to the end of each criteria inside the LEN function is that LEN() will
return an error if you pass it a NULL value. By concatenating an empty
string to the value, you will at least get an empty string, which will have
a length of zero.

Dim varCriteria as Variant

varCriteria = NULL

' CATAGORY COMBOBOX
If LEN(Me.Catagory.Value & "") > 0 Then
varCriteria = "Category = " & chr$(34) & me.Catagory & chr$(34)
End IF

' MANUFACTURER COMBOBOX
If LEN(Me.Manufacturer.Value & "") > 0 Then
varCriteria = (varCriteria + " AND ") _
& "Manufacturer = " chr$(34) & Me.Manufacturer.Value &
chr$(34)
End If

' PRODUCT NAME TEXTBOX
If LEN(Me.Product & "") > 0 Then
varCriteria = (varCriteria + " AND ") _
& "Product Like " & chr$(34) & Me.Product & "*" &
chr$(34)
End If

' SERIAL NUMBER TEXTBOX
If LEN(Me.Serial & "") > 0 Then
varCriteria = (varCriteria + " AND ") _
& "Serial Like " & chr$(34) & Me.Serial & "*" &
chr$(34)
End If

' SUPPLIER COMBOBOX
If LEN(Me.Supplier.Value & "") > 0 Then
varCriteria = (varCriteria + " AND ") _
& "Supplier = " & chr$(34) & Me.Supplier.Value &
chr$(34)
End If

' INVOICE TextBox
If LEN(Me.InvoiceNo & "") > 0 Then
varCriteria = (varCriteria + " AND ") _
& "InvoiceNo Like " & chr$(34) & Me.InvoiceNo & "*" &
chr$(34)
End If

' ASSET TextBox
If LEN(Me.AssetNo & "") > 0 Then
varCriteria = (varCriteria + " AND ") _
& "AssetNo Like " & chr$(34) & Me.AssetNo & "*" &
chr$(34)
End If

' PO TextBox
If LEN(Me.PONo & "") > 0 Then
varCriteria = (varCriteria + " AND ") _
& "PONo Like " & chr$(34) & Me.PONo & "*" & chr$(34)
End If

' Date TextBoxes
If LEN(Me.DateFrom & "") > 0 AND LEN(Me.DateTo & "") > 0 Then
varCriteria = (varCriteria + " AND ") _
& "(PurchaseDate Between #" & Me.DateFrom & "# AND #" &
Me.DateTo & "#)"
End If

' Building ComboBox
If LEN(Me.Building.Value & "") > 0 Then
varCriteria = (varCriteria + " AND ") _
& "Building = " & chr$(34) & Me.Building.Value &
chr$(34)
End If

' Room ComboBox
If LEN(Me.Room.Value & "") > 0 Then
varCriteria = (varCriteria + " AND ") _
& "Room = " & chr$(34) & Me.Room.Value & chr$(34)
End If

' Status ComboBox
If LEN(Me.Status.Value & "") > 0 Then
varCriteria = (varCriteria + " AND ") _
& "Status = " & chr$(34) & Me.Status.Value & chr$(34)
End If

' Build the SQL string
strSQL = "SELECT Inventory.* " & _
"FROM Inventory " & _
("WHERE " + varCriteria) & " " & _
"ORDER BY Inventory.Catagory, Inventory.Manufacturer;"

HTH
Dale

**************************
 
Dale said:
Ben,
Click to expand...
*snip*

Dale,

Thank you so much. Your solution works perfectly (tho not first time -
see my PS!) and simplifies the SQL a whole lot, especially when few
parameters are specified. It'll help further debugging alot also!

Of course you're right in suggesting that if there is no preference to
the field contents (therefore nothing input into the search form) then
why feature it at all in the query sql? Thanks for explaining your
alterations, they're almost as useful as the fixed code itself as now I
can actually understand why it didnt work before :)

I've only been using Access for 5 days but picking it up pretty fast.
These newsgroups & Google are a godsend!

Thanks again,
Ben.

PS - Thanks also for inadvertently correcting my spelling of 'Category'.
When your code didnt work I instantly noticed that I'd been spelling it
wrong all along throughout the database so it wasnt finding what it
needed! It's all been corrected now :)
 
Back
Top