Filter Thru Query Results

[bassstuf via AccessMonster.com]

I have a query set-up that that tells me how many of each RMA, part number at
each location. My boss wants me to create a form that he can sort thru (using
list boxes) to get the quantity.

For example how many: of RMA Number 111, and P/N 987987 we have at ____
location.

Thanks

DB

 

[Michel Walsh]

DCount("*", "TableNameHere", "[RAM Number] = 111 AND location = 'New York'
" )

and

DCount("*", "TableNameHere", " [P/N number] = 987987 AND location =
'Amsterdam' " )


If the numbers are themselves strings, rather than real number, they have to
be delimited by quotes too. If the location is a number rather than a
string, only supply the number, no quotes around it.


Vanderghast, Access MVP

 

[bassstuf via AccessMonster.com]

im still pretty confused, and really new to access. Im not sure what do with
what you replied; I need to be able to search thru upto 25 options of each of
the fields.

DCount("*", "TableNameHere", "[RAM Number] = 111 AND location = 'New York'
" )

and

DCount("*", "TableNameHere", " [P/N number] = 987987 AND location =
'Amsterdam' " )

If the numbers are themselves strings, rather than real number, they have to
be delimited by quotes too. If the location is a number rather than a
string, only supply the number, no quotes around it.

Vanderghast, Access MVP

I have a query set-up that that tells me how many of each RMA, part number
at

[quoted text clipped - 8 lines]

DB

 

[Michel Walsh]

DCount("*", "TableNameHere", "[RAM Number] = 111 AND location = 'New York'
" )

as example, can be used where you need the count (number) of records having
their Ram number equals to 111 and their location in New York.

If you need that count in a textbox of a form, then, in the ControlSource
property of the text box control, type the expression, but precede it with
an equal sign, ie:



= DCount("*", "TableNameHere", "[RAM Number] = 7 AND location = 'London' " )


I am not sure what you meant by "25 options (for) each fields". If you mean
the location is not a constant, like 'London', or like 'New York', as
example, but instead, should be the value in the open form MyFrom and in
the control MyLocation, then use:


= DCount("*", "TableNameHere", "[RAM Number] = 7 AND location =
FORMS!MyForm!MyLocation " )



as example.



Vanderghast, Access MVP

im still pretty confused, and really new to access. Im not sure what do
with
what you replied; I need to be able to search thru upto 25 options of each
of
the fields.

DCount("*", "TableNameHere", "[RAM Number] = 111 AND location = 'New York'
" )

and

DCount("*", "TableNameHere", " [P/N number] = 987987 AND location =
'Amsterdam' " )

If the numbers are themselves strings, rather than real number, they have
to
be delimited by quotes too. If the location is a number rather than a
string, only supply the number, no quotes around it.

Vanderghast, Access MVP

I have a query set-up that that tells me how many of each RMA, part
number
at

[quoted text clipped - 8 lines]

DB

 

[bassstuf via AccessMonster.com]

what I mean is by options is, I could have 18 different RAM number, each with
12 Part Numbers and in 7 different location.

DCount("*", "TableNameHere", "[RAM Number] = 111 AND location = 'New York'
" )

as example, can be used where you need the count (number) of records having
their Ram number equals to 111 and their location in New York.

If you need that count in a textbox of a form, then, in the ControlSource
property of the text box control, type the expression, but precede it with
an equal sign, ie:

= DCount("*", "TableNameHere", "[RAM Number] = 7 AND location = 'London' " )

I am not sure what you meant by "25 options (for) each fields". If you mean
the location is not a constant, like 'London', or like 'New York', as
example, but instead, should be the value in the open form MyFrom and in
the control MyLocation, then use:

= DCount("*", "TableNameHere", "[RAM Number] = 7 AND location =
FORMS!MyForm!MyLocation " )

as example.

Vanderghast, Access MVP

im still pretty confused, and really new to access. Im not sure what do
with

[quoted text clipped - 23 lines]

 

[Michel Walsh]

You could use a Total query, like (in SQL view of a query)



SELECT [Ram number], [Part number], location, COUNT(*)
FROM tableNameHere
GROUP BY [Ram number], [Part number], location



using the real fields name.



Vanderghast, Access MVP

what I mean is by options is, I could have 18 different RAM number, each
with
12 Part Numbers and in 7 different location.

DCount("*", "TableNameHere", "[RAM Number] = 111 AND location = 'New York'
" )

as example, can be used where you need the count (number) of records
having
their Ram number equals to 111 and their location in New York.

If you need that count in a textbox of a form, then, in the ControlSource
property of the text box control, type the expression, but precede it with
an equal sign, ie:

= DCount("*", "TableNameHere", "[RAM Number] = 7 AND location = 'London'
" )

I am not sure what you meant by "25 options (for) each fields". If you
mean
the location is not a constant, like 'London', or like 'New York', as
example, but instead, should be the value in the open form MyFrom and in
the control MyLocation, then use:

= DCount("*", "TableNameHere", "[RAM Number] = 7 AND location =
FORMS!MyForm!MyLocation " )

as example.

Vanderghast, Access MVP

im still pretty confused, and really new to access. Im not sure what do
with

[quoted text clipped - 23 lines]

DB