Select Blank values from combobox and find result depend on it

[billa856]

Hi,
I have to use the table(PRODUCTION) already generated in MS Access in which all fields are of TEXT type.fields like (orderdate,palletno,customercode,itemno,pono,carto n,pcs,totalquantity)Now i have already some data in table like 6000 rows.Now in that some fields are blank.

Now I have created one Form in MS Access in which there are three ComboBoxes.1st one is independent,2nd one is dependent on 1st one and,3rd one is dependent on both 1st and 2nd. Now I have to select (custcode) from 1st comboBox after that it will generate list for 2nd combobox.now when i am selecting (PONo) from 2nd combobox it will generate list for 3rd combobox depend on choice of 1st and 2nd combobox.So at the end i have the list of whatever i selected from comboboxes.

but when i am selecting value from 1st combobox there are some blank fields also in that selection.So if I select that blank field it have to show me the list depend on it.but it show me just blank result.

This is the example so u can understand easily
EXAMPLE
Table:-PRODUCTION

custcode PONo ItemNo Price
abc 123 456 500
xyz 423 400
abc 789 500
asd 456 550
abc 450
rat 123 500
abc 456 300
sad 200
jkl 456 798 520
abc 798 450
abc 900

in the 1st combobox i have list
abc
xyz
asd
rat
cat
sad
jkl

now when i am selecting "abc" from 1st combobox it show me the list for 2nd combobox which is
BLANK
123
789

so when i am selecting "BLANK" from 2nd combobox it has to generate list for 3rd combobox which is

BLANK
456
798
(but it shows nothing in my case.)

now when i am selecting "BLANK" from 3rd combobox it has to show me the last result like this
450
900
(but instead of that it will show me nothing)

so what to do to select that blank field.?
I can't change the table because someone already made it and now i have to use it and also my project's data are in that table.so plz if there is any possible solution plz give it to me.Any solution appriciated.plz help me
.

[FishVal]

Hi, there.

That is an issue of tristate logic (True, False, Null) - any expression with Null will return Null which is treated as False in a situation where bivalent logic (True, False) only is applicable.
This causes the situation when two blank (containing Null value) fields are recognized as not equal.

There are several options to overcome it.

• add checking for both Nulls situation
  

  ....... WHERE field1 = field2 OR (IsNull(field1) AND IsNull(field2))
   

• use Nz() function to convert Nulls to some value not occurring as is in the table (e.g. 0 or -1)
  

  ....... WHERE Nz(field1,0) = Nz(field2,0)
   

[billa856]

Oh wow,now its really works.
Thank You Very Much Buddy.

[FishVal]

You are welcome.

Best regards,
Fish

[billa856]

Sorry buddy but now there is another problem.
Whenever i select blank field for fix result then it will show whatever i needed.
But whenever i select anything instead of "BLANK" then it will show me some result but not what i want.


EXAMPLE
Table:-PRODUCTION

custcode PONo ItemNo Price
abc 123 456 500
xyz 423 400
abc 789 500 500
asd 456 550
abc 450
rat 123 500
abc 456 300
sad 200
jkl 456 798 520
abc 789 500 600
abc 900
abc 789 450 300
abc 789 600 200

in the 1st combobox i have list
abc
xyz
asd
rat
cat
sad
jkl

now when i am selecting "abc" from 1st combobox it show me the list for 2nd combobox which is
BLANK
123
789
so when i am selecting "789" from 2nd combobox it has to generate list for 3rd combobox which is

500


now when i am selecting "500" from 3rd combobox it has to show me the last result like this

500
600
(but instead of that it will show me something else)
like
200
300
500
600

it means filter not working.

i m using this query
SELECT *
FROM PRODUCTION
WHERE (((PRODUCTION.CustomerCode)=Forms!search1!Customer Code_Combo1) and ((IsNull(PRODUCTION.ItemNo))=(IsNull(Forms!search1 !ItemNo_Combo1))) and ((IsNull(PRODUCTION.PONo))=(IsNull(Forms!search1!P oNo_Combo1))));

Am i using wrong format?what to do please give me solution.

[FishVal]

SELECT *
FROM PRODUCTION
WHERE (((PRODUCTION.CustomerCode)=Forms!search1!Customer Code_Combo1) and ((IsNull(PRODUCTION.ItemNo))=(IsNull(Forms!search1 !ItemNo_Combo1))) and ((IsNull(PRODUCTION.PONo))=(IsNull(Forms!search1!P oNo_Combo1))));

Am i using wrong format?what to do please give me solution.

:)

Try to express the logic of the query filter in words.
Like the following.

Dear Jet, fetch me plz records from table [PRODUCTION] where
the following conditions are all meat:
- Value of table field [CustomerCode] is equal to the value in form [search1] [CustomerCode_Combo1]
- IsNull() function gives the same for both value of table field [ItemNo] and value in form [search1] [ItemNo_Combo1], that means they are either both blank or both not blank
- IsNull() function gives the same for both value of table field [PONo] and value in form [search1] [PONo_Combo1], that means they are either both blank or both not blank

Is that supposed to return expected records?

Regards,
Fish

[billa856]

the logic of the query filter in words.
Like the following.

Dear Jet, fetch me plz records from table [PRODUCTION] where
the following conditions are all meet:
- Value of table field [CustomerCode] is equal to the value in form [search1] [CustomerCode_Combo1]
- value of table field [ItemNo] is equal to the value in form [search1] [ItemNo_Combo1], that means they are either both blank or both not blank.
- value of table field [PONo] is equal to the value in form [search1] [PONo_Combo1], that means they are either both blank or both not blank.


ya i already use this query it is working but it showing all the records based on selection of 1ts combo.,There is no meaning of using 2nd and 3rd combo where i m using IsNull condition.
If I select "abc" from 1st combo then it shows all details of "abc" but i want only perticular details that's why i put 2nd and 3rd combo.
so if i use format for checking Null then Filter not working.and if i don't then i can't select BLANK value.what a trouble?
i already give example in previous post but i think it is not sufficient to explain 2 u.
but plz try it.if u can understand and solve my problem then plz tell me what to do?

[FishVal]

ya i already use this query it is working but it showing all the records based on selection of 1ts combo.,There is no meaning of using 2nd and 3rd combo where i m using IsNull condition.
If I select "abc" from 1st combo then it shows all details of "abc" but i want only perticular details that's why i put 2nd and 3rd combo.
so if i use format for checking Null then Filter not working.and if i don't then i can't select BLANK value.what a trouble?
i already give example in previous post but i think it is not sufficient to explain 2 u.
but plz try it.if u can understand and solve my problem then plz tell me what to do?

Your problem is that you probably think SQL to be some kind of magic spell. But it is just the way to tell to DBMS

.... If I select "abc" from 1st combo then it shows all details of "abc" but i want only perticular details ..."

in a way which could be resolved in terms of formal logic.
Thats why I've suggested you to express the logic of the solution in words.

Dear Jet, fetch me plz records from table [PRODUCTION] where
the following conditions are all meet:
- Value of table field [CustomerCode] is equal to the value in form [search1] [CustomerCode_Combo1]
- value of table field [ItemNo] is equal to the value in form [search1] [ItemNo_Combo1], that means they are either both blank or both not blank.
- value of table field [PONo] is equal to the value in form [search1] [PONo_Combo1], that means they are either both blank or both not blank.

That is your query translated from SQL to human words. If you follow up this logic you'll see that Jet does exactly what do you mean by the query.

Following the first criterion only those records where [CustomerCode] is equal to [search1].[CustomerCode_Combo1] will be fetched. So far so good.
But the 2nd criterion will be met when both [ItemNo] and [search1].[ItemNo_Combo1] give the same result on check for Null. This will be True when:

• both them are Nulls
• neither of them is Null

The 3rd criterion is just the same.
A record for which all 3 criteria are met is being fetched.

Any ideas concerning more lucky logic?

[billa856]

Thit is my query translated from SQL to human words.

Following the first criterion only those records where [CustomerCode] is equal to [search1].[CustomerCode_Combo1] will be fetched. So far so good.
But the 2nd criterion will be met when both [ItemNo] and [search1].[ItemNo_Combo1] give the same result on check for Null. This will be True when:

• both them are Nulls
• neither of them is Null

The 3rd criterion is just the same.
A record for which all 3 criteria are met is being fetched.

Ok now i get it.Thank you for ur advice.
In 2nd and 3rd both criterian i only need one thing, if I select BLANK from 2nd and 3rd combobox then it show me result for only fields having value NULL and if I select something else then it show me results for only fields having that value.
I don't want both Results,contain NULL and another value also, at the same time.so what changes i have to make to my query?
Now if u have any magical logic which will satisfy my need then give it to me.

[FishVal]

Hi, there.

I'd like to draw your attention to one of my previous posts.

• add checking for both Nulls situation
  

  ....... WHERE field1 = field2 OR (IsNull(field1) AND IsNull(field2))
   

• use Nz() function to convert Nulls to some value not occurring as is in the table (e.g. 0 or -1)
  

  ....... WHERE Nz(field1,0) = Nz(field2,0)
   

Particulary in your case it will be something like:

• 2nd combo in cascade
  

  SELECT *
  FROM PRODUCTION
  WHERE 
  (PRODUCTION.CustomerCode=Forms!search1!Customer Code_Combo1 OR 
  (IsNull(PRODUCTION.CustomerCode) AND IsNull(Forms!search1!Customer Code_Combo1)));
   

• 3rd combo in cascade
  

  SELECT *
  FROM PRODUCTION
  WHERE 
  (PRODUCTION.CustomerCode=Forms!search1!Customer Code_Combo1 OR 
  (IsNull(PRODUCTION.CustomerCode) AND IsNull(Forms!search1!Customer Code_Combo1)))
  AND 
  (PRODUCTION.ItemNo=Forms!search1!ItemNo_Combo1 OR 
  (IsNull(PRODUCTION.ItemNo) AND IsNull(Forms!search1!ItemNo_Combo1)));
   

• 4th combo in cascade
  

  SELECT *
  FROM PRODUCTION
  WHERE 
  (PRODUCTION.CustomerCode=Forms!search1!Customer Code_Combo1 OR 
  (IsNull(PRODUCTION.CustomerCode) AND IsNull(Forms!search1!Customer Code_Combo1)))
  AND 
  (PRODUCTION.ItemNo=Forms!search1!ItemNo_Combo1 OR 
  (IsNull(PRODUCTION.ItemNo) AND IsNull(Forms!search1!ItemNo_Combo1)))
  AND
  (PRODUCTION.PONo=Forms!search1!PONo_Combo1 OR 
  (IsNull(PRODUCTION.PONo) AND IsNull(Forms!search1!PONo_Combo1)));
   

[billa856]

Hey do u know that i did the same thing.And now when i was checking ur post i found the same.
what a co-incidence?
Thank You very very much for help me.

[FishVal]

Are you sure that wasn't the same cat?

[billa856]

Sorry Buddy but that was the same cat.
He He He.

[FishVal]

:P

Speaking seriously.
Does the code work?

[billa856]

Ya buddy,seriously its working properly.
Today I will finish My Projectand I m going to submit my Project to My Company today.
Its nice meeting you.
And by the way again Thank You.

[FishVal]

Glad I have helped you.
Good luck.

Best regards,
Fish

[billa856]

Hi,
I have one more problem now.
This query is working for only data that already exist in table.
whenver I insert new data in table it will insert into table.but when search them using this query it is not showing anything.

the main problem of this happen is whenever I insert data through form it insert into table always in line no 14.
so when I am searching data after insert, it won't show anything.
but when I cut that line I enter through from and paste it in the last line of table and then search data then it show result.

so whenever I insert data through form then it must goto the last line of table.how is this possible?
I already post my problem several times on community but nobody answer it so I am asking you.If u know plz tell me.

[NeoPa]

After you add any record, you need to call a .Requery on each of your ComboBoxes. Until that's been done, they will all keep the values they had when they were originally loaded or the SQL code for them was changed. In your case that will probably be when the form was opened.

[billa856]

I already put that docmd.requery in each comboboxes before this problem accour,but its not the problem.
My code is working for all the data which are already in table,but its not working when I enter new data, bcz whenever I enter new data it will go to the any line of table but not on the last line of table.So after that when I search for new data its not showing anything.But if I cut that rows of new data and paste it at the last line of table and then search, then it will work.This is the problem what I am facing.
So plz if anyone have solution give it to me.

Thanks

[billa856]

Hi,
I have one problem.
This query is working for only data that already exist in table.
whenver I insert new data in table it will insert into table.but when search them using this query it is not showing anything when I select "Blank" value from 3rd combobox.But it will show results when I select "something" instead of "BLANK" value.