| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert Nulls in Microsoft Query 2002
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert Nulls in Microsoft Query 2002
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 Weilmuenster wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert Nulls in Microsoft Query 2002
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 Weilmuenster wrote: 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 . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Convert Nulls in Microsoft Query 2002
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 Weilmuenster wrote: 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 Weilmuenster wrote: 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 . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
| Query from microsoft query- Excel 2007 | Excel Discussion (Misc queries) | |||
| Microsoft Query is lost when opening Excel 2002 file | Excel Discussion (Misc queries) | |||
| microsoft woord 2002 | Excel Discussion (Misc queries) | |||
| Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
Hybrid Mode
