MS Access 2000 Query Question

[Shea]

Hello,
I have built a database for my company and have hit a small roadblock allong the way. I am using MS access 2000. I am trying to make a form with 6 drop-down-boxes that corespond to 6 different fields. The user should be able to use the drop-down-boxes to define his search and then click a run query button which will run a macro that executes the query and returns the information. I have all of this running but am having a problem with null inputs.
The database contains testing data for some reasearch we are doing and contains Tests, Samples, Materials, and Counters tables. The form allows you to query by TestID, SampleID, MaterialID, Counter Used, Material Manufacturer, and Lot#. My problem comes when I want to search for say, all the tests on Sample 7 with Counter C. If I set up my query with the fields defined on an OR basis. It will return all tests on sample 7 as well as all tests run on Counter C, but I only want records for tests on sample 7 using counter C.
If I set up my query to search on an AND basis the only way to return a result is to define all fields, but I need to make it so I can define only one or two fields and return all results. If the drop-down-box is left empty the program searches that field for only empty values. So I guess what I'm looking for is some sort of wildcard value that could be used as the default if the field was undefined so that my search would be something like, "return all records using Sample 7, Counter C, wildcard, wildcard, wildcard, wildcard." Does such a function exist, or does anyone know any ways around this problem.
Thanks
Shea

[Rawhide]

Use the NZ() function to tell it to match the field to itself when the form value is null. Can also be done with IIF() and IsNull() and probably a dozen other ways.

Using NZ():

Select .....
From .....
Where Field1 = NZ(Value1, Field1)
And Field2 = NZ(Value2, Field2)
And so on ....


Using IIF() and IsNull():

Select .....
From .....
Where Field1 = IIF(IsNull(Value1), Field1)
And Field2 = IIF(IsNull(Value2), Field2)
And so on ....

Using Or grouped statements:

Select .....
From .....
Where (Field1 = Value1 Or Value1 Is Null)
And (Field2 = Value2 Or Value2 Is Null)
And so on ....

[Shea]

You'll have to excuse my ignorance, I am a lowly physics major with almost no programming and database experience. In what form are the scripts above to be used, in the query itself, as a macro or vba script, or SQL.
Thanks

[Rawhide]

Let's do this the easy way. Post the query that you are currently using, and tell me how you are using it.

[Shea]

I was attempting to execute this within the confines of the queries and macros of Access, I'll try to write up a script using what you posted and put it up.
Thanks a lot
Shea

[Shea]

This is what I have so far, before I go on I was wondering if this is the correct way to reference my combo boxes, if not, what method should be used.
Thank you very much
Shea

SELECT Materials.Manufacturer, Materials.Manufacturer Part #, Materials.Material ID #, Samples.Sample ID #, Samples.Lot #, Tests.Test ID #, Tests.Counter Used, Tests.Count Time, Tests.Number of Counts, Tests.Background Counts, Tests.Alpha Counts, Tests.Calculated Activity FROM Tests, Materials, Samples
WHERE Samples.Sample ID # = NZ(Forms![Search Database]![Combo10], Samples.Sample ID #)

[Rawhide]

Yes, that's the right way, but you have a differnet problem. When an object name contains spaces, you have to enclose the name in square brackets so that Access realizes that the whole thing is a name and not separate words. The same is true if the object name is a Keyword or starts with a symbol or is otherwise not considered a proper object name. For this reason, it is common practice to not use spaces in object names.

SELECT Materials.Manufacturer, Materials.[Manufacturer Part #], Materials.[Material ID #], Samples.[Sample ID #], Samples.[Lot #], Tests.[Test ID #], Tests.[Counter Used], Tests.[Count Time], Tests.[Number of Counts], Tests.[Background Counts], Tests.[Alpha Counts], Tests.[Calculated Activity] FROM Tests, Materials, Samples
WHERE Samples.[Sample ID #] = NZ(Forms![Search Database]![Combo10], Samples.[Sample ID #])

[Shea]

How does this look

SELECT Materials.Manufacturer, Materials.[Manufacturer Part #], Materials.[Material ID #], Samples.[Sample ID #], Samples.[Lot #], Tests.[Test ID #], Tests.[Counter Used], Tests.[Count Time], Tests.[Number of Counts], Tests.[Background Counts], Tests.[Alpha Counts], Tests.[Calculated Activity] FROM Tests, Materials, Samples
WHERE Samples.[Sample ID #] = NZ(Forms![Search Database]![Combo10], Samples.[Sample ID #])
AND Materials.[Material ID #] = NZ(Forms![Search Database]![Combo12], Materials.[Material ID #])
AND Tests.[Counter Used] = NZ(Forms![Search Database]![Combo14], Tests.[Counter Used])
AND Samples.[Lot #] = NZ(Forms![Search Database]![Combo 22], Samples.[Lot #])
AND Materials.Manufacturer = NZ(Forms![Search Database]![Combo 16], Materials.Manufacturer)


Also, and I hope this is my last question so i can quit bugging you. How do i get this script to run using an action button on my search database form?

[Rawhide]

Query looks great!!!

Save the query. When you add the action button to the form, follow the wizard and tell it to run the saved query. This will create an event handler for the button that runs the query. The event handler can then be edited as needed.

[Shea]

Where do i save the query, I just wrote it up in MS word because i didn't know where to put it.

[Rawhide]

Open the Access database, click on the queries tab, click "New", select "Design View", click "OK", close the "Show Table" dialog box, switch to "SQL View" in the query window, paste in your query, and save it.

[Shea]

This is my query exactly as saved

SELECT [Materials].[Manufacturer], [Materials].[Manufacturer Part #], [Materials].[Material ID #], [Samples].[Sample ID #], [Samples].[Lot #], [Tests].[Test ID #], [Tests].[Counter Used], [Tests].[Count Time], [Tests].[Number of Counts], [Tests].[Background Counts], [Tests].[Alpha Counts], [Tests].[Calculated Activity]
FROM Tests, Materials, Samples
WHERE [Samples].[Sample ID #] = NZ(Forms![Search Database]![Combo10], [Samples].[Sample ID #])
AND [Materials].[Material ID #] = NZ(Forms![Search Database]![Combo12], [Materials].[Material ID #])
AND [Tests].[Counter Used] = NZ(Forms![Search Database]![Combo14], [Tests].[Counter Used])
AND [Samples].[Lot #] = NZ(Forms![Search Database]![Combo 22], [Samples].[Lot #])
AND [Materials].[Manufacturer] = NZ(Forms![Search Database]![Combo 16], [Materials].[Manufacturer])

I am getting a compile error. The message is "compile error. in query expression '[Samples].[Sample ID #] = NZ(Forms![Search Database]![Combo10], [Samples].[Sample ID #])
AND [Materials].[Material ID #] = NZ(Forms![Search Database]![Combo12], [Materials].[Material ID #]) ......" and it goes on from there, any ideas?

[Rawhide]

Working with Access forms isn't really my forte, but you may need to save it as a parameterized query. Something like this:

PARAMETERS SampleID Long, MaterialID Long, CounterUsed Long, LotNumber Long, Manufacturer text(255);
SELECT [Materials].[Manufacturer], [Materials].[Manufacturer Part #], [Materials].[Material ID #], [Samples].[Sample ID #], [Samples].[Lot #], [Tests].[Test ID #], [Tests].[Counter Used], [Tests].[Count Time], [Tests].[Number of Counts], [Tests].[Background Counts], [Tests].[Alpha Counts], [Tests].[Calculated Activity]
FROM Tests, Materials, Samples
WHERE [Samples].[Sample ID #] = NZ([SampleID], [Samples].[Sample ID #])
AND [Materials].[Material ID #] = NZ([MaterialID], [Materials].[Material ID #])
AND [Tests].[Counter Used] = NZ([CounterUsed], [Tests].[Counter Used])
AND [Samples].[Lot #] = NZ([LotNumber], [Samples].[Lot #])
AND [Materials].[Manufacturer] = NZ([Manufacturer], [Materials].[Manufacturer]);


And then in the event handler, you'll need to pass in the combo box values as parameters. Something like this:

Dim qdf As QueryDef

Set qdf = CurrentDb.QueryDefs!YourQueryName

qdf.Parameters!SampleID = Combo10.Value
qdf.Parameters!MaterialID = Combo12.Value

and so on .....