Using IIF to count the is not null records in a table

[KFox via AccessMonster.com]

Using a query, I'm trying to count the number of records in a table that are
not null. It looks as though there's actually a space in the fields, so I've
tried all these combinations to no avail. The values in the field are text,
but it doesn't matter to me what text it is, I just need to know how many
fields are "populated".

QS: Sum(IIf(Not IsNull([QS Code]),1,0))
QS: Sum(IIf(Not IsNull([QS Code] Or Not " "),1,0))
QS: Sum(IIf(Nz([QS Code],0),1,0))
QS: Count(IIf([QS Code]=" " Or [QS Code] Is Not Null,1,0))

Thanks in advance!
Kellie

 

[Duane Hookom]

Try:

QS: Sum( Abs( Trim([QS Code] & "") = "") )

 

[KFox via AccessMonster.com]

Thanks! I tried that and received this message:
"The expression you entered contains invalid syntax. You may have entered an
operand without an operator."

Try:

QS: Sum( Abs( Trim([QS Code] & "") = "") )

Using a query, I'm trying to count the number of records in a table that
are

[quoted text clipped - 12 lines]

Thanks in advance!
Kellie

 

[Guest]

I know Duane is exceptionally qualified, so I differ with him with
trepidation. His version uses the Abs function. I don't quite understand,
because Abs requires a numeric argument. I would offer this alternative.

QS: Sum( Abs( Trim([QS Code] & "") = "") )
QS: Sum(Trim(Nz([QS Code],"")) = "")

Thanks! I tried that and received this message:
"The expression you entered contains invalid syntax. You may have entered an
operand without an operator."

Try:

QS: Sum( Abs( Trim([QS Code] & "") = "") )

Using a query, I'm trying to count the number of records in a table that
are

[quoted text clipped - 12 lines]

Thanks in advance!
Kellie

 

[Duane Hookom]

Please reply with the full SQL view of your query.

--
Duane Hookom
MS Access MVP

Thanks! I tried that and received this message:
"The expression you entered contains invalid syntax. You may have entered
an
operand without an operator."

Try:

QS: Sum( Abs( Trim([QS Code] & "") = "") )

Using a query, I'm trying to count the number of records in a table that
are

[quoted text clipped - 12 lines]

Thanks in advance!
Kellie

 

[Guest]

I'd suggest regularizing the table first so that all rows with no text at the
QS Code column position are NULL. You can do this by executing an update
query along thse lines:

UPDATE YourTable
SET [QS Code] = NULL
WHERE TRIM([QS Code]) = "";

Id then amend the table definition to prohibit zero-length strings in the QS
Code column. You can then count the rows with:

SELECT COUNT(*)
FROM YourTable
WHERE [QS Code] IS NOT NULL;

Ken Sheridan
Stafford, England

 

[KFox via AccessMonster.com]

Since this data is being imported in, I only needed to do the following to
convert the spaces. This worked great!

UPDATE YourTable
SET [QS Code] = NULL
WHERE TRIM([QS Code]) = "";

However, I have several columns that I need to do this with. I'm not very
familiar with writing SQL, can you tell me what code to use to remove the
spaces from several different columns? I tried the following, and received
the error, "Characters found at the end of SQL statement". I don't know what
to put between these two SQL statements.

I should add that I'm just typing this into the SQL view of the query grid.

TIA!
Kellie

UPDATE tblException
SET [QS Code] = NULL
WHERE TRIM([QS Code]) = "";

UPDATE tblException
SET [RW Code] = NULL
WHERE TRIM([RW Code]) = "";

 

[John Spencer]

You could try the following.

UPDATE tblException
SET [QS Code] = IIF(TRIM([QS Code]) = "",Null,[QS Code]),
[RWCode] = IIF(TRIM([RWCode]) = "",Null,[RWCode])
WHERE TRIM([QS Code]) = "" OR TRIM([RWCode]) = ""

In Access SQL you can't have multiple separate queries in one SQL statement.
If you want to run separate update queries for each field, then you can use
a macro or vba code to run the queries in sequence.

Since this data is being imported in, I only needed to do the following to
convert the spaces. This worked great!

UPDATE YourTable
SET [QS Code] = NULL
WHERE TRIM([QS Code]) = "";

However, I have several columns that I need to do this with. I'm not very
familiar with writing SQL, can you tell me what code to use to remove the
spaces from several different columns? I tried the following, and
received
the error, "Characters found at the end of SQL statement". I don't know
what
to put between these two SQL statements.

I should add that I'm just typing this into the SQL view of the query
grid.

TIA!
Kellie

UPDATE tblException
SET [QS Code] = NULL
WHERE TRIM([QS Code]) = "";

UPDATE tblException
SET [RW Code] = NULL
WHERE TRIM([RW Code]) = "";

 

[KFox via AccessMonster.com]

I think that worked. I don't have any spaces left in my table to see, but at
least I didn't get any errors.

THANK YOU!!

Kellie

You could try the following.

UPDATE tblException
SET [QS Code] = IIF(TRIM([QS Code]) = "",Null,[QS Code]),
[RWCode] = IIF(TRIM([RWCode]) = "",Null,[RWCode])
WHERE TRIM([QS Code]) = "" OR TRIM([RWCode]) = ""

In Access SQL you can't have multiple separate queries in one SQL statement.
If you want to run separate update queries for each field, then you can use
a macro or vba code to run the queries in sequence.

Since this data is being imported in, I only needed to do the following to
convert the spaces. This worked great!

[quoted text clipped - 24 lines]

SET [RW Code] = NULL
WHERE TRIM([RW Code]) = "";