Query help!

[gmc5135]

I've attached my database so that you can see what I'm working with. Basically, I want qos to be exactly like qalloy, just for the different table. It won't let me run it. It says "qos" is not a valid name. Let me know if I'm being unclear. Thanks!

 

[YNWA]

Your being unclear.

 

[vbaInet]

Your being unclear.

I was going to say just that haha!

@gmc5135: What are qos and qalloy? And what are you really trying to achieve here?

 

[pr2-eugin]

Hi.. Just checked your file.. WOW !! you have totally lost way..
What you have done is, you have tried to
* Run a Query for a specific information through search form,
** But if they(the person who performs search) do not enter any search items(parameters) it should return the whole stuff..
* So you have used 'Is Null', but in a wrong way.. Is Null is used to Check for Null value for a field.. but in this case you need is a Nz function and a LIKE operator, I have changed the code for you.. Now just copy and paste it into the SQL query window..

I am not sure how qalloy query even worked in the first place..
qAlloy_Qry

SELECT OxSamples.SampleNo, OxSamples.TubeNo, OxSamples.Length, OxSamples.OD, OxSamples.ID, OxSamples.InitialWeight, OxSamples.FinalWeight, OxSamples.HydridingRunNo, OxSamples.Hydrogen1, OxSamples.Hydrogen2, OxSamples.RackPosition
FROM OxSamples
WHERE (((OxSamples.SampleNo) Like Nz([forms]![searchform].[qsampleno],"*")) AND ((OxSamples.TubeNo) Like Nz([forms]![searchform].[qtubeno],"*")) AND ((OxSamples.Length) Like Nz( [forms]![searchform].[qlength], "*")) AND ((OxSamples.OD) Like Nz([forms]![searchform].[qod],"*")) And ((OxSamples.ID) Like Nz([forms]![searchform].[qid],"*")) And ((OxSamples.InitialWeight) Like Nz([forms]![searchform].[qinitialweight],"*")) AND ((OxSamples.FinalWeight) Like Nz([forms]![searchform].[qfinalweight],"*")) AND ((OxSamples.HydridingRunNo) Like Nz([forms]![searchform].[qhydridingrunno],"*")) AND ((OxSamples.Hydrogen1) Like Nz([forms]![searchform].[qhydrogen1],"*")) AND ((OxSamples.Hydrogen2) Like Nz([forms]![searchform].[qhydrogen2], "*")) AND ((OxSamples.RackPosition) Like Nz([forms]![searchform].[qrackposition],"*")));

qOS_Qry

SELECT OxSamples.SampleNo, OxSamples.TubeNo, OxSamples.Length, OxSamples.OD, OxSamples.ID, OxSamples.InitialWeight, OxSamples.FinalWeight, OxSamples.HydridingRunNo, OxSamples.Hydrogen1, OxSamples.Hydrogen2, OxSamples.RackPosition
FROM OxSamples
WHERE (((OxSamples.SampleNo) Like Nz([forms]![searchform].[qsampleno],"*")) AND ((OxSamples.TubeNo) Like Nz([forms]![searchform].[qtubeno],"*")) AND ((OxSamples.Length) Like Nz( [forms]![searchform].[qlength], "*")) AND ((OxSamples.OD) Like Nz([forms]![searchform].[qod],"*")) And ((OxSamples.ID) Like Nz([forms]![searchform].[qid],"*")) And ((OxSamples.InitialWeight) Like Nz([forms]![searchform].[qinitialweight],"*")) AND ((OxSamples.FinalWeight) Like Nz([forms]![searchform].[qfinalweight],"*")) AND ((OxSamples.HydridingRunNo) Like Nz([forms]![searchform].[qhydridingrunno],"*")) AND ((OxSamples.Hydrogen1) Like Nz([forms]![searchform].[qhydrogen1],"*")) AND ((OxSamples.Hydrogen2) Like Nz([forms]![searchform].[qhydrogen2], "*")) AND ((OxSamples.RackPosition) Like Nz([forms]![searchform].[qrackposition],"*")));

 

[gmc5135]

Your being unclear.

@gmc5135: What are qos and qalloy? And what are you really trying to achieve here?

Sorry guys... If you look at the database I attached in the original post, qos and qalloy are both queries. I have the form "searchform" which is where I want the user to be able to enter parameters and to search two tables. qalloy is on the left and is used to search the "Alloys" table. qos is on the right and is used to search the OxSamples table. I hope that clears it up.

Hi.. Just checked your file.. WOW !!

you have totally lost way..
What you have done is, you have tried to
* Run a Query for a specific information through search form,
** But if they(the person who performs search) do not enter any search items(parameters) it should return the whole stuff..
* So you have used 'Is Null', but in a wrong way.. Is Null is used to Check for Null value for a field.. but in this case you need is a Nz function and a LIKE operator, I have changed the code for you.. Now just copy and paste it into the SQL query window..

I am not sure how qalloy query even worked in the first place..
qAlloy_Qry Code:
SELECT OxSamples.SampleNo, OxSamples.TubeNo, OxSamples.Length, OxSamples.OD, OxSamples.ID, OxSamples.InitialWeight, OxSamples.FinalWeight, OxSamples.HydridingRunNo, OxSamples.Hydrogen1, OxSamples.Hydrogen2, OxSamples.RackPosition FROM OxSamples WHERE (((OxSamples.SampleNo) Like Nz([forms]![searchform].[qsampleno],"*")) AND ((OxSamples.TubeNo) Like Nz([forms]![searchform].[qtubeno],"*")) AND ((OxSamples.Length) Like Nz( [forms]![searchform].[qlength], "*")) AND ((OxSamples.OD) Like Nz([forms]![searchform].[qod],"*")) And ((OxSamples.ID) Like Nz([forms]![searchform].[qid],"*")) And ((OxSamples.InitialWeight) Like Nz([forms]![searchform].[qinitialweight],"*")) AND ((OxSamples.FinalWeight) Like Nz([forms]![searchform].[qfinalweight],"*")) AND ((OxSamples.HydridingRunNo) Like Nz([forms]![searchform].[qhydridingrunno],"*")) AND ((OxSamples.Hydrogen1) Like Nz([forms]![searchform].[qhydrogen1],"*")) AND ((OxSamples.Hydrogen2) Like Nz([forms]![searchform].[qhydrogen2], "*")) AND ((OxSamples.RackPosition) Like Nz([forms]![searchform].[qrackposition],"*")));
qOS_Qry Code:
SELECT OxSamples.SampleNo, OxSamples.TubeNo, OxSamples.Length, OxSamples.OD, OxSamples.ID, OxSamples.InitialWeight, OxSamples.FinalWeight, OxSamples.HydridingRunNo, OxSamples.Hydrogen1, OxSamples.Hydrogen2, OxSamples.RackPosition FROM OxSamples WHERE (((OxSamples.SampleNo) Like Nz([forms]![searchform].[qsampleno],"*")) AND ((OxSamples.TubeNo) Like Nz(

I've copy and pasted it. It does not bring back any results. Did it work for you?

 

[vbaInet]

You're doing too many things at one time. Break down your first query into small parts by using only ONE criteria, test it and see if it works. And when you change the Record Source of a form, you need to Reqeury it too.

 

[pr2-eugin]

Not really, because there was no data in the table.. try adding some data and then your search will be fine.. I have uploaded a modified version..

 

[gmc5135]

@pr2-eugin- Works great! Thank you so much.

 

[pr2-eugin]

You are welcome.