Counting records that are not 0

[Tony Williams]

I have a query that I want to count the number of records where the value of
a field "txtFactClients" is not 0. I have set up an expression "CountFC:
txtFactClients" with Count selected in the Total line and [txtFactClients]>0
in the criteria line but when I run the query it counts every record whether
or not it is 0. I've looked at previous post but they all seem to involve
SQL, is there a way of doing this in the design mode of a query?
TIA
Tony

[Darryl Kerkeslager]

Tony,

Try this before you do anything else: Look at your query in SQL view. The
Query By Design in Access allows you to switch easily between SQL, Design
and Datasheet view. Put it to it's full use. You can do anything in SQL
view that you can do in Design View, and vice-versa (although some things
are easier to do in one than the other).

If you can't work out how to do something, switch to SQL View, copy the SQL
to the ng, and say exactly the results that you want. When you get your
answer, copy the SQL back to a new query in SQL View. Then switch back to
Design View if you want. IMHO, switching back and forth between the two is
one of the best ways to learn. Not that the SQL that Access generates is
perfect, but it works.

Darryl Kerkeslager
"Tony Williams" <[e-mail]> wrote in message
news:cs**********@titan.btinternet.com...

I have a query that I want to count the number of records where the value of a field "txtFactClients" is not 0. I have set up an expression "CountFC:
txtFactClients" with Count selected in the Total line and [txtFactClients]>0 in the criteria line but when I run the query it counts every record whether or not it is 0. I've looked at previous post but they all seem to involve
SQL, is there a way of doing this in the design mode of a query?
TIA
Tony

[Mike]

You need to remove [txtFactClients]>0 from the criteria line of your
field "CountFC:txtFactClients" and add the field txtFactClients,
choosing 'where' in the total line and >0 in the criteria line.

Mike

[Ed Robichaud]

txtFactClients is probably a TEXT field and as such has string values, NOT
NUMBERS.

Try:
[txtFactClients] <>"0"
as your criteria.

-Ed

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message
news:5v********************@comcast.com...

Tony,

Try this before you do anything else: Look at your query in SQL view.
The
Query By Design in Access allows you to switch easily between SQL, Design
and Datasheet view. Put it to it's full use. You can do anything in SQL
view that you can do in Design View, and vice-versa (although some things
are easier to do in one than the other).

If you can't work out how to do something, switch to SQL View, copy the
SQL
to the ng, and say exactly the results that you want. When you get your
answer, copy the SQL back to a new query in SQL View. Then switch back to
Design View if you want. IMHO, switching back and forth between the two
is
one of the best ways to learn. Not that the SQL that Access generates is
perfect, but it works.

Darryl Kerkeslager
"Tony Williams" <[e-mail]> wrote in message
news:cs**********@titan.btinternet.com...

I have a query that I want to count the number of records where the value

of

a field "txtFactClients" is not 0. I have set up an expression "CountFC:
txtFactClients" with Count selected in the Total line and

[txtFactClients]>0

in the criteria line but when I run the query it counts every record

whether

or not it is 0. I've looked at previous post but they all seem to involve
SQL, is there a way of doing this in the design mode of a query?
TIA
Tony

[Ed Robichaud]

txtFactClients is probably a TEXT field and as such has string values, NOT
NUMBERS.

Try:
[txtFactClients] <>"0"
as your criteria.

-Ed

"Tony Williams" <[e-mail]> wrote in message
news:cs**********@titan.btinternet.com...

I have a query that I want to count the number of records where the value
of
a field "txtFactClients" is not 0. I have set up an expression "CountFC:
txtFactClients" with Count selected in the Total line and
[txtFactClients]>0
in the criteria line but when I run the query it counts every record
whether
or not it is 0. I've looked at previous post but they all seem to involve
SQL, is there a way of doing this in the design mode of a query?
TIA
Tony

[Salad]

Mike wrote:

You need to remove [txtFactClients]>0 from the criteria line of your
field "CountFC:txtFactClients" and add the field txtFactClients,
choosing 'where' in the total line and >0 in the criteria line.

Mike

That should work. Or enter
TRUE
in the criteria for the expression
[txtFactClients]>0

[MacDermott]

While I fully agree with your advice to post the SQL of any problematic
query, I'd take a little issue with your "vice-versa" below.

I've had a number of times when I could manipulate joins in SQL view in ways
the Design View couldn't handle.

For example, suppose one table has a text field, and I want to link it to an
Integer field in another table. (Just for the sake of argument, let's say
these tables are linked from other sources, so I can't change the layout).
I can go into SQL View and write
SELECT * FROM Table1 INNER JOIN Table2 on
Table1.MyField=CStr(Table2.MyField)

This works great in SQL view, but refuses to display in Design View.

- Turtle

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message
news:5v********************@comcast.com...

Tony,

Try this before you do anything else: Look at your query in SQL view. The Query By Design in Access allows you to switch easily between SQL, Design
and Datasheet view. Put it to it's full use. You can do anything in SQL
view that you can do in Design View, and vice-versa (although some things
are easier to do in one than the other).

[Douglas J. Steele]

As well, you cannot do UNION queries in Design View

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"MacDermott" <ma********@nospam.com> wrote in message
news:xs****************@newsread2.news.atl.earthli nk.net...

While I fully agree with your advice to post the SQL of any problematic
query, I'd take a little issue with your "vice-versa" below.

I've had a number of times when I could manipulate joins in SQL view in
ways
the Design View couldn't handle.

For example, suppose one table has a text field, and I want to link it to
an
Integer field in another table. (Just for the sake of argument, let's say
these tables are linked from other sources, so I can't change the layout).
I can go into SQL View and write
SELECT * FROM Table1 INNER JOIN Table2 on
Table1.MyField=CStr(Table2.MyField)

This works great in SQL view, but refuses to display in Design View.

- Turtle

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message
news:5v********************@comcast.com...

Tony,

Try this before you do anything else: Look at your query in SQL view.

The

Query By Design in Access allows you to switch easily between SQL, Design
and Datasheet view. Put it to it's full use. You can do anything in SQL
view that you can do in Design View, and vice-versa (although some things
are easier to do in one than the other).

[Darryl Kerkeslager]

Both points well taken :)

I over-reached with "anything".
Darryl Kerekslager
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote

As well, you cannot do UNION queries in Design View
"MacDermott" <ma********@nospam.com> wrote

While I fully agree with your advice to post the SQL of any problematic
query, I'd take a little issue with your "vice-versa" below.

I've had a number of times when I could manipulate joins in SQL view in
ways the Design View couldn't handle.

For example, suppose one table has a text field, and I want to link it to an
Integer field in another table. (Just for the sake of argument, let's say these tables are linked from other sources, so I can't change the layout). I can go into SQL View and write
SELECT * FROM Table1 INNER JOIN Table2 on
Table1.MyField=CStr(Table2.MyField)

This works great in SQL view, but refuses to display in Design View.

[Ela Bielska]

"MacDermott" <ma********@nospam.com> wrote in message
news:xs****************@newsread2.news.atl.earthli nk.net...

While I fully agree with your advice to post the SQL of any problematic
query, I'd take a little issue with your "vice-versa" below.

I've had a number of times when I could manipulate joins in SQL view in
ways
the Design View couldn't handle.

For example, suppose one table has a text field, and I want to link it to
an
Integer field in another table. (Just for the sake of argument, let's say
these tables are linked from other sources, so I can't change the layout).
I can go into SQL View and write
SELECT * FROM Table1 INNER JOIN Table2 on
Table1.MyField=CStr(Table2.MyField)

This works great in SQL view, but refuses to display in Design View.

- Turtle

This is also true for non-visible SQL such as the following:

[Larry Linson]

"Ela Bielska" <[e-mail]> wrote

This is also true for non-visible SQL such as the following:

Well, the SQL you "posted" is certainly non-visible.

[David Schofield]

[Darryl Kerkeslager]

Was your post design view or SQL view :)

Darryl Kerkeslager

Visible=False

"David Schofield" <d.***************@blueyonder.co.uk> wrote in message
news:41ea5e19.60685481@localhost...

[Tony Williams]

Thanks Mike Ill try both of those
Tony
"Salad" <oi*@vinegar.com> wrote in message
news:a7*****************@newsread3.news.pas.earthl ink.net...

Mike wrote:

You need to remove [txtFactClients]>0 from the criteria line of your
field "CountFC:txtFactClients" and add the field txtFactClients,
choosing 'where' in the total line and >0 in the criteria line.

Mike

That should work. Or enter
TRUE
in the criteria for the expression
[txtFactClients]>0