Query using database criterion

[Kripa]

I have stored some data in sheet named "Data" in database format with
following headers:
Column Heading Type of data
===== ====== ========
A Supplier_Name Text
B Machine_Model Text
C Table_Length Numeric
D Table_Width Numeric
E Spindle_Diameter Numeric
Some of the data under columns C, D & E are blank. Columns. A is named as
"Data"

The criterion for quering the database is setup as follows in another sheet
named "Query":

Col. A
Col.B Col.C Col.D
Col.E
Row1 Supplier_Name Machine_Model
Table_Length Table_Width Spindle_Diameter
Row2 =IF(ISBLANK(A3),"",A3) =IF(ISBLANK(B3),"",B3)
<----------------Problem here-------------------------->
Row3

The range A1:E2 is defined as Query_Critrerion. Row 2 is hidden and user
input is picked up in Row 3; the formulas under Row 2 convert the user input
for defining the criteria.
Then I use the formula =DCOUNTA(Data,1,Qry_Criterion) in a cell in the
"Query" sheet to get the count of records matching the criteria entered.
The moment user types in his creiteira, immediaely, the cell displays the
record count.

The user should be allowed to input expressions like >5, >=10, etc. under
C3, D3 & D3 (since these are numeric fields).
When the user entry is blank (by pressing the <del> key), all the records
should be selected, since no criterion is specified.
Suprisingly, this works fine for data columns containing text data and not
numeric data!

My problem is formulas under C2, D2 and E2. If I enter the formula
=IF(ISBLANK(B3),"",B3), and the cells C3, D3 & E3 are blank, onlly database
count isshowing zero.
If I use "=" instead of "", only the no. of blank records are displayed, and
if ">0" is used, only count of non-blank records are returned.
Unfortunately, there is no excel function to enter a "blank" in a cell like
NA(), except the usual "" which doesn't make the cell blank.
I wish there is a BLANK() function to be used in a formula to simulate a
blank cell!

I could find a solution when the data contains ONLY ONE numeric filed, by
using two rows for formulas and including them Qry_Criterion range.
*********************************
My database contains three numeric fields!
*********************************

Executing a macro by clicking a command button (after criteria is entered)
and updaing the formulas in Row 2 is a solution; but clumsy.

What is the formula to be used under C2, D2 & E2, so that the database count
is updated automatically the moment the user enters his criteria?

Thanks

Kripa Venkatesh

 

[Shane Devenshire]

Hi,

This worked for me

=IF(D3<>"",D3,"")

Where D3 contained the user entry.

When I clear D3 I get a total count. Remember clearing the cell with a
spacebar is not the same as clearing it with Del.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[Kripa]

It doesn't work with NUMERIC data.

Hi,

This worked for me

=IF(D3<>"",D3,"")

Where D3 contained the user entry.

When I clear D3 I get a total count. Remember clearing the cell with a
spacebar is not the same as clearing it with Del.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[keiji kounoike]

Is there any reason to use formula and hide that row? Does just putting
the criteria directly into row2 has any problem for you?

keiji

 

[Kripa]

The row2 that contains the formula needs to be hidden so that row1
containing the headings and row3 from where the user input is picked up
appear next to each other - for an elegant user-interface.

By the way, hiding/displaying the 2nd row doesn't make any differnce as for
the problem is concerned.

thanx for the interest shown; i badly need a solution to the problem.

 

[keiji kounoike]

I mean that it seems to be useless to use the formula like
=IF(ISBLANK(A3),"",A3). There is almost no diffrence between using
formula like that and directly put criteria into row2 in respect of
user-interface to me. I think only difference is putting criteria into
row2 directly would solve your problem and using formula wouldn't.

keiji

 

[Kripa]

You are right - if the criterion is "equal to the user input".
But I am using multiple field criteria to filter the data based on user
input under many field headings.

Some of the field creitera require the formulas to prefixed and/or suffixed
wild cards (*,?,etc.) the user input.
I do not want to bother the user to input the wild cards; the formula should
contruct the appropriate criterion string.
Hope you got the idea.

****************************************************************************************************
By the way, inputting the formula directly into row2 also doesn't work for
columns containing NUMERIC data, with a few blank cells.
****************************************************************************************************

Just input a few NUMAERIC data under a column, setup a criteria range and
use the DCOUNTA() function to count the total no. of records. The problem
will be clear.
I am using Excel 2003.

 

[keiji kounoike]

Hi Kripa

I'm sorry but I am a bit dense that i can't still make it clear. In
short, i don't get what you are looking for. In your first post, you
said you find the way to count blank cells and also find solution by
using two rows for formulas and including them Qry_Criterion range. but
i can't understand why that solution, though i'm not sure what it is,
does not work in case of three numeric fields? In my poor thought, count
blank cell(by using "=") and count cells with <>0 and count cells with 0
by using OR logic would be equivalent to no criteria. I may be wrong,
but this seems to be able to apply to numeric field more than one.
I hope some experts here would help you and me.

keiji

 

[Kripa]

Ok; let me make it simple.

In the crtiera if "" is used, it selects ALL the records for that particular
field - but only for fields contgaining TEXT data.

But "" does not select records wiht blank entries for columns containing
NUMERIC data. The idea is when a user enters blank (<del>) under any numeric
field critreial, he should be presented ALL the records; sureprisingly this
works only for text data.

Anyway, I have worked out a round about solution by entering 0 in all the
blank cells and hiding zeros using tools-option!

In case you are still intersted, i can e-mail the workbook; it's an
interesting problem.

Thanks for the keen interst and quick repsonse.

 

[keiji kounoike]

Hi Kripa

I don't mind e-mail, But i'm not sure i can help you. my e-mail is as
same as written here only change AT to @.

By the way, if Column A is TEXT data, then criteria "" will select all
the records and if Column C is NUMERIC data, criteria = will select all
blank records and criteria <>0 will select all numeric data with <>0 and
criteria 0 will select all numeric data with 0. so if a user enter <del>
and you want all the records in both text and numeric case, one exsample
is like this.

I guess why "" instead of "*" will select all data in case of TEXT data
has the relation to the function search("","any string") will always
return 1, not #VALUE!.

say row 1 is Header fields, from row 2 to row 4 are formula fields, row
5 is input fields.

if Column A is TEXT data, then input =IF(A5="","",A5) in A2 and Cells
from A3 to A4 leave blank. If Column C is NUMERIC data, then input
=IF(C5="","=",C5) in C2 and =IF(C5="","<>0",C5) in C3 and
=IF(C5="","=0",C5) in C4. input the same formula in other cells
according to the fields type. Change Qry_Criterion range to A1:E4. hide
rows from row2 to row4, and enter <del> in cell A5 and C5 would select
all records. this is what i thought.

keiji

 

[Kripa]

Hai,
I have e-mailed a sample worksheet.
Thanx for the interest shown.