473,287 Members | 1,868 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Select Blank values from combobox and find result depend on it

101 100+
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
.
Nov 20 '07 #1
19 4709
FishVal
2,653 Expert 2GB
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
    Expand|Select|Wrap|Line Numbers
    1. ....... WHERE field1 = field2 OR (IsNull(field1) AND IsNull(field2))
    2.  
  • use Nz() function to convert Nulls to some value not occurring as is in the table (e.g. 0 or -1)
    Expand|Select|Wrap|Line Numbers
    1. ....... WHERE Nz(field1,0) = Nz(field2,0)
    2.  
Nov 21 '07 #2
billa856
101 100+
Oh wow,now its really works.
Thank You Very Much Buddy.
Nov 21 '07 #3
FishVal
2,653 Expert 2GB
You are welcome.

Best regards,
Fish
Nov 21 '07 #4
billa856
101 100+
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.
Nov 27 '07 #5
FishVal
2,653 Expert 2GB
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
Nov 27 '07 #6
billa856
101 100+
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?
Nov 27 '07 #7
FishVal
2,653 Expert 2GB
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?
Nov 28 '07 #8
billa856
101 100+
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.
Nov 28 '07 #9
FishVal
2,653 Expert 2GB
Hi, there.

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

  • add checking for both Nulls situation
    Expand|Select|Wrap|Line Numbers
    1. ....... WHERE field1 = field2 OR (IsNull(field1) AND IsNull(field2))
    2.  
  • use Nz() function to convert Nulls to some value not occurring as is in the table (e.g. 0 or -1)
    Expand|Select|Wrap|Line Numbers
    1. ....... WHERE Nz(field1,0) = Nz(field2,0)
    2.  
Particulary in your case it will be something like:
  • 2nd combo in cascade
    Expand|Select|Wrap|Line Numbers
    1. SELECT *
    2. FROM PRODUCTION
    3. WHERE 
    4. (PRODUCTION.CustomerCode=Forms!search1!Customer Code_Combo1 OR 
    5. (IsNull(PRODUCTION.CustomerCode) AND IsNull(Forms!search1!Customer Code_Combo1)));
    6.  
  • 3rd combo in cascade
    Expand|Select|Wrap|Line Numbers
    1. SELECT *
    2. FROM PRODUCTION
    3. WHERE 
    4. (PRODUCTION.CustomerCode=Forms!search1!Customer Code_Combo1 OR 
    5. (IsNull(PRODUCTION.CustomerCode) AND IsNull(Forms!search1!Customer Code_Combo1)))
    6. AND 
    7. (PRODUCTION.ItemNo=Forms!search1!ItemNo_Combo1 OR 
    8. (IsNull(PRODUCTION.ItemNo) AND IsNull(Forms!search1!ItemNo_Combo1)));
    9.  
  • 4th combo in cascade
    Expand|Select|Wrap|Line Numbers
    1. SELECT *
    2. FROM PRODUCTION
    3. WHERE 
    4. (PRODUCTION.CustomerCode=Forms!search1!Customer Code_Combo1 OR 
    5. (IsNull(PRODUCTION.CustomerCode) AND IsNull(Forms!search1!Customer Code_Combo1)))
    6. AND 
    7. (PRODUCTION.ItemNo=Forms!search1!ItemNo_Combo1 OR 
    8. (IsNull(PRODUCTION.ItemNo) AND IsNull(Forms!search1!ItemNo_Combo1)))
    9. AND
    10. (PRODUCTION.PONo=Forms!search1!PONo_Combo1 OR 
    11. (IsNull(PRODUCTION.PONo) AND IsNull(Forms!search1!PONo_Combo1)));
    12.  
Nov 28 '07 #10
billa856
101 100+
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.
Nov 28 '07 #11
FishVal
2,653 Expert 2GB
Are you sure that wasn't the same cat?
Nov 28 '07 #12
billa856
101 100+
Sorry Buddy but that was the same cat.
He He He.
Nov 29 '07 #13
FishVal
2,653 Expert 2GB
:P

Speaking seriously.
Does the code work?
Nov 29 '07 #14
billa856
101 100+
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.
Nov 29 '07 #15
FishVal
2,653 Expert 2GB
Glad I have helped you.
Good luck.

Best regards,
Fish
Nov 29 '07 #16
billa856
101 100+
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.
Nov 30 '07 #17
NeoPa
32,554 Expert Mod 16PB
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.
Nov 30 '07 #18
billa856
101 100+
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
Dec 3 '07 #19
billa856
101 100+
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.
Dec 5 '07 #20

Sign in to post your reply or Sign up for a free account.

Similar topics

9
by: Bob Bedford | last post by:
I've a form that use a combobox along with other fields. When the user submit the form, many tests are done. If any test fails, then I show the form again with previously entered values. My...
6
by: Marek Mänd | last post by:
I searched google but couldnt find ant decent code for dependant comboboxes script (where the combo values in other combo depend upon the selections in the first combobox). The problem is that...
3
by: jim | last post by:
Hi NG I have a form in which I use a combobox - the content of the combobox depend on the customername shown in a textbox. Now my problem is that I want the content in the combobox to change...
6
by: Johann Blake | last post by:
I fill a table in a dataset with values that will be used by a combobox for the combobox's items. The combobox is a drop down list that only allows the user to select from the list but not enter...
4
by: rdemyan via AccessMonster.com | last post by:
I have the following SQL statement in code that is set to the RowSource of a combobox. The combobox has two columns. SELECT '(ALL)' As Site, '' As , 0 As SortFirst FROM GROUP BY SITE_COMPLEX...
7
oll3i
by: oll3i | last post by:
i want to change the values in two columns one colum is a combobox and the secons column is editable too i want to get the value of that second column and the value of combobox and sent that...
1
by: The.Daryl.Lu | last post by:
Hi, two parts to my problem if someone can help address either one or both: 1. I want to SELECT everything in the table if it matches the criteria when the query button is pressed (this is just...
2
by: billa856 | last post by:
Hi, My Project is in MS Access. In that I have one form in which I have some textboxes,comboboxes and listboxes. Now when I select value from 1st combobox(CustomerID) then it wil generate list for...
4
by: Patrick Nolan | last post by:
I am using javascript to manipulate optgroups in select elements. When the results are displayed in Firefox 2.0 there is an annoying blank line at the top of the multi-line select box. This...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.