Convert Nulls in Microsoft Query 2002

  • Thread starter Thread starter David Weilmuenster
  • Start date Start date
D

David Weilmuenster

Hi, I'm looking for a way in Microsoft Query 2002 to
convert Null values in a field to "0" before exporting to
Microsoft Excel 2002.

For example, in the following query, I need to set
`Tables$TM_ETF_Scan`.`'ETF 1Wk RS'` and similar fields
to "0" before exporting to Excel.

SELECT `Tables$Swing_B`.`TICKER SYMBOL`,
`Tables$Swing_B`.`SECURITY NAME`, `Tables$Swing_B`.`SWING
WAVE`, `Tables$Swing_B`.`CONNORS WINDOW`,
`Tables$Swing_B`.`'EPS RS'`, `Tables$Swing_B`.`'Stk 3 Mo
RS'`, `Tables$Swing_B`.`'Stk 6 Mo RS'`,
`Tables$Swing_B`.`'Stk 12 Mo RS'`,
`Tables$TM_ETF_Scan`.`'ETF 1Wk RS'`,
`Tables$TM_ETF_Scan`.`'ETF 1Mo RS'`,
`Tables$TM_ETF_Scan`.`'ETF 3Mo RS'`,
`Tables$TM_ETF_Scan`.`'ETF 6Mo RS'`,
`Tables$TM_ETF_Scan`.`'ETF 12Mo RS'`
FROM {oj `Tables$Swing_B` `Tables$Swing_B` LEFT OUTER JOIN
`Tables$TM_ETF_Scan` `Tables$TM_ETF_Scan` ON
`Tables$Swing_B`.`TICKER SYMBOL` =
`Tables$TM_ETF_Scan`.Symbol}


Thanks,
David
 
Hi David,

If you're using SQL Server, you can utilize the IsNull function:

IsNull(`Tables$TM_ETF_Scan`.`'ETF 1Wk RS'`,0),

This will return the value if not null and 0 if null.

If you're using Access or something else, you may be able to use a similar
function. I believe in Access you can use something like this:

IIf(IsNull(`Tables$TM_ETF_Scan`.`'ETF 1Wk RS'`),0,
`Tables$TM_ETF_Scan`.`'ETF 1Wk RS'`)

....but I'm not positive.

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Jake, thank you very much.

The IIF statement did the trick. I am using Microsoft
Query 2002, which I'm guessing is a subset of Access 2002.

By the way, do you know of any help file or online
download that explains the Microsoft Query functions such
as IIF. Using Help from within Microsoft Query gives me
absolutely no information on these functions. Can't even
find IIF with a search.

Regards,
David

-----Original Message-----
Hi David,

If you're using SQL Server, you can utilize the IsNull function:

IsNull(`Tables$TM_ETF_Scan`.`'ETF 1Wk RS'`,0),

This will return the value if not null and 0 if null.

If you're using Access or something else, you may be able to use a similar
function. I believe in Access you can use something like this:

IIf(IsNull(`Tables$TM_ETF_Scan`.`'ETF 1Wk RS'`),0,
`Tables$TM_ETF_Scan`.`'ETF 1Wk RS'`)

....but I'm not positive.

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


David said:
Hi, I'm looking for a way in Microsoft Query 2002 to
convert Null values in a field to "0" before exporting to
Microsoft Excel 2002.

For example, in the following query, I need to set
`Tables$TM_ETF_Scan`.`'ETF 1Wk RS'` and similar fields
to "0" before exporting to Excel.

SELECT `Tables$Swing_B`.`TICKER SYMBOL`,
`Tables$Swing_B`.`SECURITY NAME`, `Tables$Swing_B`.`SWING
WAVE`, `Tables$Swing_B`.`CONNORS WINDOW`,
`Tables$Swing_B`.`'EPS RS'`, `Tables$Swing_B`.`'Stk 3 Mo
RS'`, `Tables$Swing_B`.`'Stk 6 Mo RS'`,
`Tables$Swing_B`.`'Stk 12 Mo RS'`,
`Tables$TM_ETF_Scan`.`'ETF 1Wk RS'`,
`Tables$TM_ETF_Scan`.`'ETF 1Mo RS'`,
`Tables$TM_ETF_Scan`.`'ETF 3Mo RS'`,
`Tables$TM_ETF_Scan`.`'ETF 6Mo RS'`,
`Tables$TM_ETF_Scan`.`'ETF 12Mo RS'`
FROM {oj `Tables$Swing_B` `Tables$Swing_B` LEFT OUTER JOIN
`Tables$TM_ETF_Scan` `Tables$TM_ETF_Scan` ON
`Tables$Swing_B`.`TICKER SYMBOL` =
`Tables$TM_ETF_Scan`.Symbol}


Thanks,
David
Click to expand...

.
Click to expand...
 
Hi David,

You're welcome - glad it worked. I believe that Microsoft Query is simply a
tool that connects to external data sources and assists you in developing
SQL statements. So your data actually resides in Access or some other type
of database. For help on SQL syntax (like the SELECT statement you used) in
general, you can search online or check Microsoft Access help.

Each database uses (most of) the base ANSI SQL standards, but most databases
add their own customizations/enhancements on top of that. IIf is not an
ANSI standard, so it's probably described in Access help.

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


David said:
Jake, thank you very much.

The IIF statement did the trick. I am using Microsoft
Query 2002, which I'm guessing is a subset of Access 2002.

By the way, do you know of any help file or online
download that explains the Microsoft Query functions such
as IIF. Using Help from within Microsoft Query gives me
absolutely no information on these functions. Can't even
find IIF with a search.

Regards,
David

-----Original Message-----
Hi David,

If you're using SQL Server, you can utilize the IsNull function:

IsNull(`Tables$TM_ETF_Scan`.`'ETF 1Wk RS'`,0),

This will return the value if not null and 0 if null.

If you're using Access or something else, you may be able to use a
similar function. I believe in Access you can use something like
this:

IIf(IsNull(`Tables$TM_ETF_Scan`.`'ETF 1Wk RS'`),0,
`Tables$TM_ETF_Scan`.`'ETF 1Wk RS'`)

....but I'm not positive.

--
Regards,

Jake Marx
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


David said:
Hi, I'm looking for a way in Microsoft Query 2002 to
convert Null values in a field to "0" before exporting to
Microsoft Excel 2002.

For example, in the following query, I need to set
`Tables$TM_ETF_Scan`.`'ETF 1Wk RS'` and similar fields
to "0" before exporting to Excel.

SELECT `Tables$Swing_B`.`TICKER SYMBOL`,
`Tables$Swing_B`.`SECURITY NAME`, `Tables$Swing_B`.`SWING
WAVE`, `Tables$Swing_B`.`CONNORS WINDOW`,
`Tables$Swing_B`.`'EPS RS'`, `Tables$Swing_B`.`'Stk 3 Mo
RS'`, `Tables$Swing_B`.`'Stk 6 Mo RS'`,
`Tables$Swing_B`.`'Stk 12 Mo RS'`,
`Tables$TM_ETF_Scan`.`'ETF 1Wk RS'`,
`Tables$TM_ETF_Scan`.`'ETF 1Mo RS'`,
`Tables$TM_ETF_Scan`.`'ETF 3Mo RS'`,
`Tables$TM_ETF_Scan`.`'ETF 6Mo RS'`,
`Tables$TM_ETF_Scan`.`'ETF 12Mo RS'`
FROM {oj `Tables$Swing_B` `Tables$Swing_B` LEFT OUTER JOIN
`Tables$TM_ETF_Scan` `Tables$TM_ETF_Scan` ON
`Tables$Swing_B`.`TICKER SYMBOL` =
`Tables$TM_ETF_Scan`.Symbol}


Thanks,
David
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top