exclude zeroes in normalizing union query

  • Thread starter Thread starter mb
  • Start date Start date
M

mb

Hi,

I a running a normalizing union query to transform
fields "1", "2" and "3" into a field
called "ResourceImpact". The last line of my SQL is:

WHERE [1] IS NOT NULL

This gives me records in the query where [1] is zero
because this spreadsheet was originally done in Excel and
contains zeroes. I tried:

WHERE [1] IS NOT NULL OR "0"

but this did not work. Does anyone have hints on how to
exclude these records where [1] has a value of zero?
Thank you for your help!

The record also contains the fields of "SiteNumber"
and "ObservationDate", but the entire record will not be
needed because the value of [1] is zero.
 
If your Field [1] is a Numeric Field

.... WHERE ([1] Is Not Null) And ([1] <> 0)

If your Field [1] is of Text Type:

.... WHERE ([1] Is Not Null) And ([1] <> "0")

HTH
Van T. Dinh
MVP (Access)
 
Thank you!
-----Original Message-----
If your Field [1] is a Numeric Field

.... WHERE ([1] Is Not Null) And ([1] <> 0)

If your Field [1] is of Text Type:

.... WHERE ([1] Is Not Null) And ([1] <> "0")

HTH
Van T. Dinh
MVP (Access)



Hi,

I a running a normalizing union query to transform
fields "1", "2" and "3" into a field
called "ResourceImpact". The last line of my SQL is:

WHERE [1] IS NOT NULL

This gives me records in the query where [1] is zero
because this spreadsheet was originally done in Excel and
contains zeroes. I tried:

WHERE [1] IS NOT NULL OR "0"

but this did not work. Does anyone have hints on how to
exclude these records where [1] has a value of zero?
Thank you for your help!

The record also contains the fields of "SiteNumber"
and "ObservationDate", but the entire record will not be
needed because the value of [1] is zero.
Click to expand...


.
Click to expand...
 
Thank you!
-----Original Message-----
WHERE [1] IS NOT NULL AND [1] <> "0"

P


Hi,

I a running a normalizing union query to transform
fields "1", "2" and "3" into a field
called "ResourceImpact". The last line of my SQL is:

WHERE [1] IS NOT NULL

This gives me records in the query where [1] is zero
because this spreadsheet was originally done in Excel and
contains zeroes. I tried:

WHERE [1] IS NOT NULL OR "0"

but this did not work. Does anyone have hints on how to
exclude these records where [1] has a value of zero?
Thank you for your help!

The record also contains the fields of "SiteNumber"
and "ObservationDate", but the entire record will not be
needed because the value of [1] is zero.
Click to expand...


.
Click to expand...
 
Back
Top