Error in List Count

[ritimajain]

I am new to ACCESS. I need your help. In this form1 is based on Query1 and form1 has 3 combo boxes , in which user have to select EXCHID , then CELL, and then BAND , i want only count of selected GSM Band weather it is GSM900 or GSM1800 band not count of all the bands related to selected CELL in text box and count get decreased by 1 in text box if BLSTATE is MBL . .. How can i do this ..? I m that close to it. Please help.I m uploading my updated db Please have a look on following link


https://app.box.com/s/wnql96ku9wszc4xmwji6


but when i change Combo4 with Combo6 AND CELL with BAND in WHERE clause IT STOP WORKING. There is something wrong in WHERE CLAUSE .. but i dont know what? PLEASE HELP

[June7]

The sql statement you are building in the Combo2 AfterUpdate event does not make sense. I am surprised Combo4 shows any items. Why would Combo4 RowSource reference Combo6? Consider:

Combo2 AfterUpdate event code:
Me.Combo4.Requery

Combo4 RowSource:
SELECT DISTINCT Cell FROM Query1 WHERE Exchid=Combo2;

Combo4 AfterUpdate event code:
Me.Combo6.Requery

Combo6 RowSource:
SELECT DISTINCT Band FROM Query1 WHERE Cell=Combo4;

Listbox RowSource:
SELECT EXCHID AS [SITE ID], CELL AS [CELL SITE], BAND, BLSTATE FROM Query1 WHERE Band=Combo6 ORDER BY CELL;

These queries run very slow.

Options for reducing the count for the records with MBL:

1. VBA code that loops through the listbox lines

2. apply filter criteria to a query object { =Forms!Form1!Combo6 AND <>"MBL" } then do a DCount() expression in a textbox ControlSource

3. instead of listbox, bind the form to query object with the filter criteria in #2, set the form as Continuous view and arrange controls like datasheet, put the comboboxes in form header section, review http://datapigtechnologies.com/flash...tomfilter.html

[ritimajain]

Code you are suggesting is not working properly , by selecting combo6 which is BAND List8 shows list regarding to selected BAND but I want list regarding to selected BAND , CELL, AND EXCHID

[June7]

Then maybe listbox RowSource:

SELECT EXCHID AS [SITE ID], CELL AS [CELL SITE], BAND, BLSTATE FROM Query1 WHERE Exchid=Combo2 AND Cell=Combo4 AND Band=Combo6 ORDER BY CELL;

[ritimajain]

hey i am now able to get count of selected band related to selected cell site by changing listbox row source to
SELECT Query1.EXCHID AS [SITE ID], Query1.CELL AS [CELL SITE], Query1.[BAND] AS [BAND], Query1.BLSTATE AS BLSTATE FROM Query1 WHERE (((Query1.CELL) Like [Forms]![Form1]![Combo4] & "*") AND ((Query1.[BAND]) Like [Forms]![Form1]![Combo6] & "*")) ORDER BY Query1.[BAND];

Give me a way to reduce count in text box by no of times field BLSTATE IS MBL in a listbox related to selected band ?

[June7]

Options already stated in post 2.

[ritimajain]

Now i m able to get count excluding BLSTATE having value MBL related to selected BAND and related CELL by using Expression Builder on row source of LISTBOX 10
SELECT Query1.EXCHID AS [SITE ID], Query1.CELL AS [CELL SITE], Query1.[BAND] AS [BAND], Query1.BLSTATE AS BLSTATE
FROM Query1
WHERE (((Query1.CELL) Like [Forms]![Form1]![Combo4] & "*") AND ((Query1.[BAND]) Like [Forms]![Form1]![Combo6] & "*") AND ((Query1.BLSTATE)<>"MBL" Or ((Query1.BLSTATE) Is Null Or (Query1.BLSTATE)="BLL" Or (Query1.BLSTATE)="BLO")))
ORDER BY Query1.[BAND];


Can i make two text boxes in which one will show BLSTATE count having values MBL related selected BAND and CELL and other will show BLSTATE count having values BLL,BLO,Blank based on list box??

[June7]

Nice, including criteria in the listbox should have been my first suggetion - too obvious I guess.

Okay, maybe a DCount() domain aggregate function on Query1, like:

DCount("Exchid", "Query1", "CELL='" & [Combo4] & "' AND [BAND]='" & [Combo6] & "' AND (BLSTATE<>'MBL' Or IsNull(BLSTATE) Or BLSTATE='BLL' Or BLSTATE='BLO'"))

That expression can be used in VBA or in textbox ControlSource. Be aware, the textbox calc will probably be very slow.

Why are you using LIKE and wildcard? If users are limited to selecting items from the list then just use = sign.

[ritimajain]

The expression of DCount DCount("Exchid", "Query1", "CELL='" & [Combo4] & "' AND [BAND]='" & [Combo6] & "' AND (BLSTATE<>'MBL' Or IsNull(BLSTATE) Or BLSTATE='BLL' Or BLSTATE='BLO'")) you are suggesting giving me error

I have also tried another way out for this in Row source of listbox ii have builded a expression builder .. in query criteria of list box i have added a field BLSTATE one more time AND added COUNT in total field and in CRITERIA I HAVE GIVEN "MBL" that will given total MBL count AND AGAIN BLSTATE IN ANOTHER FIELd AND AGAIN COUNT IN TOTAL FIELD AND IN CRITERIA -- IS NULL OR BLL OR BLO that will give me total count of that but when i run it it give me error that expression is too complicated to run .. i donot know what to do ? please help ?

[June7]

Sorry, paren in wrong place, needs to be within quote marks:

=DCount("Exchid","Query1","CELL='" & [Combo4] & "' AND [BAND]='" & [Combo6] & "' AND (BLSTATE<>'MBL' Or IsNull(BLSTATE) Or BLSTATE='BLL' Or BLSTATE='BLO')")

I put the DCount in textbox ControlSource. Unfortunately, Access choked on this calculation after I selected items in the comboboxes. I expected it to be slow but not to crash the db.

If you use it in query, will need to include prefix referencing the form:

Forms!Form1![Combo4]
Forms!Form1![Combo6]

Then how will you get the value? Bind form to the query and bind textbox to the calculated field?

[ritimajain]

hey ! i got the solution for my problem i have created one list box and in row source criteria of that i have put

SELECT Query1.EXCHID AS [SITE ID], Query1.CELL AS [CELL SITE], Query1.[BAND] AS [BAND], Query1.BLSTATE AS BLSTATE
FROM Query1
WHERE (((Query1.CELL) Like [Forms]![Form1]![Combo4] & "*") AND ((Query1.[BAND]) Like [Forms]![Form1]![Combo6] & "*"))
ORDER BY Query1.[BAND];
and attach this with text box which gives me total count
and one more list box whose row source criteria would be
SELECT Query1.EXCHID AS [SITE ID], Query1.CELL AS [CELL SITE], Query1.[BAND] AS [BAND], Query1.BLSTATE AS BLSTATE
FROM Query1
WHERE (((Query1.CELL) Like [Forms]![Form1]![Combo4] & "*") AND ((Query1.[BAND]) Like [Forms]![Form1]![Combo6] & "*") AND ((Query1.BLSTATE)<>"MBL" Or ((Query1.BLSTATE) Is Null Or (Query1.BLSTATE)="BLL" Or (Query1.BLSTATE)="BLO")))
ORDER BY Query1.[BAND];
and visibility of this list box NO and attach this list box with another text box which will give me count of all except MBL ..

[June7]

Congratulations! Sometimes a solution just needs creative thinking 'outside the box'.