Count Function - Text Field

[aMack]

I need to create an update query that will count the number of fields NOT
Blank and enter the result in another field - "Eq Count".

Field Names "Wheels", "Chassis A", "Chassis B", "Chassis C".

If all fields have data the result would be 4, if one is blank, the result
should be 3.

How do I do it??


Thanks

 

[KARL DEWEY]

Try this --
Sum(IIF([Wheels] Is Not Null, 1, 0) + IIF([Chassis A] Is Not Null, 1, 0) +
IIF([Chassis B] Is Not Null, 1, 0) + IIF([Chassis C] Is Not Null, 1, 0))

 

[aMack]

Thanks:

Error message on execution: "You tried to execute a query that does not
include the specified expression "Chassis Count" as part of an aggregate
function.


--
A MACKENZIE, CMA, MBA

Try this --
Sum(IIF([Wheels] Is Not Null, 1, 0) + IIF([Chassis A] Is Not Null, 1, 0) +
IIF([Chassis B] Is Not Null, 1, 0) + IIF([Chassis C] Is Not Null, 1, 0))

I need to create an update query that will count the number of fields NOT
Blank and enter the result in another field - "Eq Count".

Field Names "Wheels", "Chassis A", "Chassis B", "Chassis C".

If all fields have data the result would be 4, if one is blank, the result
should be 3.

How do I do it??


Thanks

 

[KARL DEWEY]

Post your query SQL. Open the query in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.

Thanks:

Error message on execution: "You tried to execute a query that does not
include the specified expression "Chassis Count" as part of an aggregate
function.


--
A MACKENZIE, CMA, MBA

Try this --
Sum(IIF([Wheels] Is Not Null, 1, 0) + IIF([Chassis A] Is Not Null, 1, 0) +
IIF([Chassis B] Is Not Null, 1, 0) + IIF([Chassis C] Is Not Null, 1, 0))

I need to create an update query that will count the number of fields NOT
Blank and enter the result in another field - "Eq Count".

Field Names "Wheels", "Chassis A", "Chassis B", "Chassis C".

If all fields have data the result would be 4, if one is blank, the result
should be 3.

How do I do it??


Thanks

 

[aMack]

UPDATE [Chassis Ingates] SET [Chassis Ingates].[Chassis Count] =
Sum(IIf([Wheels] Is Not Null,1,0)+IIf([Chassis A] Is Not
Null,1,0)+IIf([Chassis B] Is Not Null,1,0)+IIf([Chassis C] Is Not Null,1,0));


Thx
--
A MACKENZIE, CMA, MBA

Post your query SQL. Open the query in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.

Thanks:

Error message on execution: "You tried to execute a query that does not
include the specified expression "Chassis Count" as part of an aggregate
function.


--
A MACKENZIE, CMA, MBA

Try this --
Sum(IIF([Wheels] Is Not Null, 1, 0) + IIF([Chassis A] Is Not Null, 1, 0) +
IIF([Chassis B] Is Not Null, 1, 0) + IIF([Chassis C] Is Not Null, 1, 0))


:

I need to create an update query that will count the number of fields NOT
Blank and enter the result in another field - "Eq Count".

Field Names "Wheels", "Chassis A", "Chassis B", "Chassis C".

If all fields have data the result would be 4, if one is blank, the result
should be 3.

How do I do it??


Thanks

 

[KARL DEWEY]

You are summing a bunch of records from the [Chassis Ingates] table.
Which one of the records of [Chassis Ingates] are you wanting to update?

There is no need and is bad databasing to store calculations in your table.
Now if you had dates with this and you wanted to store a snapshot calculation
you would want to append the point-in-time data to a history file.

If you insist then you can do it in two queries - first one sum and second
update - but it will update all the records with the same information.

UPDATE [Chassis Ingates] SET [Chassis Ingates].[Chassis Count] =
Sum(IIf([Wheels] Is Not Null,1,0)+IIf([Chassis A] Is Not
Null,1,0)+IIf([Chassis B] Is Not Null,1,0)+IIf([Chassis C] Is Not Null,1,0));


Thx
--
A MACKENZIE, CMA, MBA

Post your query SQL. Open the query in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.

Thanks:

Error message on execution: "You tried to execute a query that does not
include the specified expression "Chassis Count" as part of an aggregate
function.


--
A MACKENZIE, CMA, MBA


:

Try this --
Sum(IIF([Wheels] Is Not Null, 1, 0) + IIF([Chassis A] Is Not Null, 1, 0) +
IIF([Chassis B] Is Not Null, 1, 0) + IIF([Chassis C] Is Not Null, 1, 0))


:

I need to create an update query that will count the number of fields NOT
Blank and enter the result in another field - "Eq Count".

Field Names "Wheels", "Chassis A", "Chassis B", "Chassis C".

If all fields have data the result would be 4, if one is blank, the result
should be 3.

How do I do it??


Thanks

 

[aMack]

Thank you for your help.

I am able to place the count in both forms and Reports instead of the
database as you suggeted.

Removing the "Sum" portion of the calc gives me correct data for each line.

Thanks again.
--
A MACKENZIE, CMA, MBA

You are summing a bunch of records from the [Chassis Ingates] table.
Which one of the records of [Chassis Ingates] are you wanting to update?

There is no need and is bad databasing to store calculations in your table.
Now if you had dates with this and you wanted to store a snapshot calculation
you would want to append the point-in-time data to a history file.

If you insist then you can do it in two queries - first one sum and second
update - but it will update all the records with the same information.

UPDATE [Chassis Ingates] SET [Chassis Ingates].[Chassis Count] =
Sum(IIf([Wheels] Is Not Null,1,0)+IIf([Chassis A] Is Not
Null,1,0)+IIf([Chassis B] Is Not Null,1,0)+IIf([Chassis C] Is Not Null,1,0));


Thx
--
A MACKENZIE, CMA, MBA

Post your query SQL. Open the query in design view, click on menu VIEW - SQL
View, highlight all, copy, and paste in a post.

:

Thanks:

Error message on execution: "You tried to execute a query that does not
include the specified expression "Chassis Count" as part of an aggregate
function.


--
A MACKENZIE, CMA, MBA


:

Try this --
Sum(IIF([Wheels] Is Not Null, 1, 0) + IIF([Chassis A] Is Not Null, 1, 0) +
IIF([Chassis B] Is Not Null, 1, 0) + IIF([Chassis C] Is Not Null, 1, 0))


:

I need to create an update query that will count the number of fields NOT
Blank and enter the result in another field - "Eq Count".

Field Names "Wheels", "Chassis A", "Chassis B", "Chassis C".

If all fields have data the result would be 4, if one is blank, the result
should be 3.

How do I do it??


Thanks