Select query help ( Using switch ) 1

[nithink]

Hi, very new to MS-Access. Help pls.

I'm creating an extract(.txt file) by running a select
query on 2 tables swarc, swsr. One particular field
(g_total) in the extract has to be retrieved upon a
condition.

The condition is,

if swsr.id_1 is null
then
g_total should be swarc.ret_val
else
g_total = null

I tried to use a Switch. But it doesnt work. Below is the
query with just that field alone.

SELECT
Switch([swsr.id_1] is null,[swarc.ret_val]) AS g_total
from swarc, swsr

Can you pls help me out. I'm not sure whether I should use
the switch.
Thanks,
Nithin

 

[dhookom]

Try:
SELECT IIf(IsNull([Swsr].[ID_1]), [swarc].[ret_val],Null) as G_Total
FROM swarc, swsr

I'm surprised there is no join between the two tables and there is only one field. Be carefull how you pair your []s. These must be used around individual field or table names if the names contain spaces.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[nithink]

Thanks Duane. It worked fine. I forgot to add the join conditions. And I've one more question on IIf.

If I wanted to test if the field has a value "N" then how should the syntax be ? I tried the below syntax but its giving me error.

SELECT IIf(Val([Swsr].[ID_1])="N",[swarc].[ret_val],Null) as N_Total
FROM swarc, swsr

And since I'm new to Access, where can I find all these
SQL and all related syntaxes ? any good links...

Thanks much...

 

[dhookom]

The Val() function attempts to return a numeric value. Therefore, comparing it to text such as "N" will fail. Try remove the Val().

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[nithink]

Hi, I tried removing the Val(). Its not working.
This is how I used,

IIf(([swsr].[gim2000_ID_3])="N",[swarc].[ret_val],Null) AS n_total

Pls advise. How to check for a text value ?

Thanks much.

 

[dhookom]

Pls advise... what do you mean by "Its not working"? Is it possible for gim2000_ID_3 to contain a null value?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[nithink]

i think i didnt explain it properly in the previous post..

In my select query ,

one of the field (g_total) is populated by swarc.ret_val, if swsr.gim2000_id_3 field is null,

and another field (n_total) is populated by swarc.ret_val, if swsr.gim2000_id_3 field is "N",


So for g_total, I used it as you said given below,

SELECT IIf(IsNull([Swsr].[gim2000_ID_3]), [swarc].[ret_val],Null) as G_Total
FROM swarc, swsr

and the gim2000_id_3 contains null values too...

this is how,given below, I tried for checking if "N",

IIf(([swsr].[gim2000_ID_3])="N",[swarc].[ret_val],Null) AS n_total

Hope this time I explained it correctly.

Thanks much.

 

[dhookom]

You still didn't explain what you mean by "Its not working". You should describe this by providing an error message or the wrong and correct values. It doesn't take very much effort to type 5-10 sample records into a reply with the actual field values and what you expect to have returned in your query. This would make a reply very easy.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[nithink]

Hi,
Actually I'm getting a blank output from the query. It is just blank and no errors too.

SELECT IIf(([swsr].[ID_3])="N", [swarc].[ret_val],Null) as G_Total
FROM swarc, swsr
where swsr.acct_num = swarc.acct_num

and I even tried without any joins to other tables like given below. Then also, no values in the output, just blank.
Actually there are 105 rows with values "N" in the table.


SELECT
IIf(([swsr].[ID_3])="N","V",Null) as G_Total
FROM swsr

p.s : ID_3 has null values too.
Sorry for the late post. Got caught up with something else.

Thanks much. Really dont know whats happening.

 

[dhookom]

Again...[red]It doesn't take very much effort to type 5-10 sample records into a reply with the actual field values and what you expect to have returned in your query. This would make a reply very easy.
[/red]
What do you see if you run this sql:

SELECT [swsr].[ID_3], [swarc].[ret_val],
IIf(([swsr].[ID_3])="N", [swarc].[ret_val],Null) as G_Total
FROM swarc, swsr
where swsr.acct_num = swarc.acct_num

What is the datatype of swsr.ID_3?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[nithink]

Thanks much Duane. Below is the sample output.

id_3 ret_val Expr1002
-------------------------
N 6.23 6.23
N -10.14 -10.14
N -0.28 -0.28
N -6.92 -6.92
N 5.54 5.54
N 6.21 6.21
N -4.36 -4.36
-1.68 -1.68
-0.45 -0.45
-0.32 -0.32
7.98 7.98
6.68 6.68
2.28 2.28
2.96 2.96

Previously I think I did something with the join conditions. So it didnt return any values. Now its OK. Thanks.

 

[dhookom]

Apparently you have G_Total somewhere else in the tables since the G_Total column has a heading of "Expr1002".
Also, it looks like there is an issue when ID_3 is null, try:
SELECT [swsr].[ID_3], [swarc].[ret_val],
IIf(([swsr].[ID_3] & "")="N", [swarc].[ret_val],Null) as G_Total
FROM swarc, swsr
where swsr.acct_num = swarc.acct_num

Duane
MS Access MVP
Find out how to get great answers faq219-2884.