combobox value or text with wildcard as a query criteria

[bubai]

Hi

Sorry but I stick with my opinion that you should have a field for Station and actually store the StationID from your list of Stations.

This would enable you to search quite easily for a specific Station



Luck with your Project

Thanks for your advice mate. But I have no difficulty in querying for stations.
My query generation form "Form1" has multiple comboboxes for multiple query criteria. If I select values for all the comboboxes the query then returns appropriate result. But the problem occurs when I don't want a query result using all criteria. I then fill only the required combobox for the required query result and leave the unwanted comboboxes blank. It then return no result.

[bubai]

bubai

Do not use lookups in tables. Use lookups on forms.

Good luck with your project.

I understand the perils of using lookup field. Few fields in which I have lookup field was created at the initial stage. But they are not in use. I have shifted them to form. I will clear all of them once I finalize the design.
But can you help me with the query generation form problem?

[Bob Fitz]

I understand the perils of using lookup field. Few fields in which I have lookup field was created at the initial stage. But they are not in use. I have shifted them to form. I will clear all of them once I finalize the design.
But can you help me with the query generation form problem?

Perhaps:

SELECT Cons.Import_ID, Cons.Description, Cons.[Brand_ID>], Cons.[Supplier_ID>], Cons.BigQty, Cons.SmallQty, Cons.BankValue, Cons.ActuValue
FROM Cons
WHERE (((Cons.Import_ID) Like "*" & [Forms]![Form1]![Station] & "*" Or [Forms]![Form1]![Station] Is Null) AND ((Cons.Description)=[Forms]![Form1]![Des] Or [Forms]![Form1]![Des] Is Null));

as the query to use when Form1 is open

[bubai]

Perhaps:

SELECT Cons.Import_ID, Cons.Description, Cons.[Brand_ID>], Cons.[Supplier_ID>], Cons.BigQty, Cons.SmallQty, Cons.BankValue, Cons.ActuValue
FROM Cons
WHERE (((Cons.Import_ID) Like "*" & [Forms]![Form1]![Station] & "*" Or [Forms]![Form1]![Station] Is Null) AND ((Cons.Description)=[Forms]![Form1]![Des] Or [Forms]![Form1]![Des] Is Null));

as the query to use when Form1 is open

Thanks mate. But is it possible to plot in the query grid?

[mike60smart]

Hi bubai

What Bob has given you is the Record Source for the Form

If you Paste the SELECT ... statement into the Record Source of your Form in Design View you are then able to access the Query Grid by Clicking on the Elipse (...) at the end of the Record Source Row.

Here is an example Form in Design View

[bubai]

Perhaps:
as the query to use when Form1 is open

Thanks Bob!
I have been able to use the code but there is a catch.

SELECT Cons.Import_ID, Cons.Description, Cons.[Brand_ID>], Cons.[Supplier_ID>], Cons.BigQty, Cons.SmallQty, Cons.BankValue, Cons.ActuValue
FROM Cons
WHERE (((Cons.Import_ID) Like "*" & Forms!Form1!Station & "*" And (Cons.Import_ID) Like "*" & Forms!Form1!Catagory & "*") And ((Cons.Description) Like "*" & Forms!Form1!Des & "*") And ((Cons.[Brand_ID>])=Forms!Form1!Brand_ID Or Forms!Form1!Brand_ID is null));

I have added one more field to the query criteria - "Brnad_ID" and adjusted the code accordingly to fit that criteria. This field is a foreign key to my query table "Cons" which takes its value form the lookup table "_BrandLookup". The value of the criteria if fed from the "Brand_ID" combobox of the form.
The problem is, in my query table (Cons) if any of the record of any query field has empty value (ie NULL), then the query SOMETIMES avoids that record. Although I can't recon on that as I am a newbie to the DB designing world and this is my first project. It'll be helpful to me if you have any conclusive theory on that and could share with me.
Also, can you PLEASE help me by answering the following 2 questions?

A. What is the difference between "ISNULL" and "IS NULL"?
B. Is there any drawback of using foreign key as query criteria?

As "Brnad_ID" which I have later added is a foreign key.

Thanks in advance.

[Bob Fitz]

Thanks Bob!
I have been able to use the code but there is a catch.

SELECT Cons.Import_ID, Cons.Description, Cons.[Brand_ID>], Cons.[Supplier_ID>], Cons.BigQty, Cons.SmallQty, Cons.BankValue, Cons.ActuValue
FROM Cons
WHERE (((Cons.Import_ID) Like "*" & Forms!Form1!Station & "*" And (Cons.Import_ID) Like "*" & Forms!Form1!Catagory & "*") And ((Cons.Description) Like "*" & Forms!Form1!Des & "*") And ((Cons.[Brand_ID>])=Forms!Form1!Brand_ID Or Forms!Form1!Brand_ID is null));

I have added one more field to the query criteria - "Brnad_ID" and adjusted the code accordingly to fit that criteria. This field is a foreign key to my query table "Cons" which takes its value form the lookup table "_BrandLookup". The value of the criteria if fed from the "Brand_ID" combobox of the form.
The problem is, in my query table (Cons) if any of the record of any query field has empty value (ie NULL), then the query SOMETIMES avoids that record. Although I can't recon on that as I am a newbie to the DB designing world and this is my first project. It'll be helpful to me if you have any conclusive theory on that and could share with me.
Also, can you PLEASE help me by answering the following 2 questions?

A. What is the difference between "ISNULL" and "IS NULL"?
B. Is there any drawback of using foreign key as query criteria?

As "Brnad_ID" which I have later added is a foreign key.

Thanks in advance.

A. IsNull() is used with VBA code e.g

If IsNull([SomeFieldOrVarName]] Then
    Code to run if [SomeFieldOrVarName] is a null value
Else
    Code to run if [SomeFieldOrVarName] is NOT a  null value
End If

Is Null is used in SQL to test for a null value in query criteria e.g.
Forms![FormName].[ControlName] Is Null


B. No. It is a good choice.

[bubai]

Dear Bob,
I have had some observations in using Search Form. Access doesn't let use more than 1 criteria field to have Is Null key word. For example I tried to alter the previous code that you supplied use Is Null for 2 fields, but it doesn't work.

SELECT Cons.Import_ID, Cons.Description, Cons.[Brand_ID>], Cons.[Supplier_ID>], Cons.BigQty, Cons.SmallQty, Cons.BankValue, Cons.ActuValue
FROM Cons
WHERE (((Cons.Import_ID) Like "*" & Forms!Form1!Station & "*" And (Cons.Import_ID) Like "*" & Forms!Form1!Catagory & "*") And ((Cons.Description) Like "*" & Forms!Form1!Des & "*") And ((Cons.[Brand_ID>])=Forms!Form1!Brand_ID Or Forms!Form1!Brand_ID is null));

I see that if I try to use IS Null with Cons.Description along with Cons.[Brand_ID>] the code fails. However, this is not my actual point. I was thinking to add criteria for Cons.BankValue field where I could use a range of amount (eg. >= 1000) to filter the query. I know Between ... And is an option. But what if I need to exclude this criteria for some cases and leave that box blank?

[Bob Fitz]

Can you post a copy of the db with just a few fictitious records to illustrate the problem

[bubai]

Can you post a copy of the db with just a few fictitious records to illustrate the problem

Dear Bob,
In its query grid Access rearranges multiple criteria into two sets of alternatives where in the Criteria line it puts references to all Combobox references for which there will be values and in the "Or" line it puts all Combobox references except the field for which there will be Is Null. Then it puts that field name in a new column and puts Is Null to the Or line beneath. I had put the Sql according to your structure but it then altered that according to its own preference. I have used Like "*" wildcard for all of the criteria except for FK_BrandID field. Now if I decide I will have Is Null for the Description field as well instead of Like wild card I will have to put that in another line. But then Access wont take that as valid. If however I put Is Null for Description field in the same "Or" line then I shall have to have both of the combobox value to be Null in order to retrieve any result. But I may need to have blank in only one of the comboboxes. But then it won't work.

My 2nd problem is, I was thinking to have a criteria BankValue field for which I may or may not put any value in the Form's text box. But if I put any value, I then would like to have the query filtered by that value where the result should be greater than that value or between two values or something like that.

I am adding the attachment in zip A.zip format.