Query with a condition 1

[MA04]

Hi all,

I am not sure if i can do this with sql. I was wondering if it was possible to have a conditional query that checks if one field is null if it is then adds another field (a populated one) to it. The query i have at present combines 2 fields together to create one field (creates 3 new fields like this).

A1 (new field created by fields
PRMF,Strd,STR
A2 (new field created by fields
Locd,Locdd
A3 (new field created by fields
PTN,CNT
A4
Postcode

Now i only want to add the condition to 1 field the A2, so for a record if Locd and Locdd are null add field STR to A3 only if Strd is not null for that record if Strd is null leave it as it is. The user is prompted to enter a postcode when the query is run.

As i said i am not sure if i can do this or not with sql, any help or advice very much appreciated, thanks in advance,

M-.

 

[KenReay]

Hi

You can have IIF() expression in query, so you could do it that way, not sure if you are talking od an update query or a select query, but eitehr way IIF() should allow you to do it, also worth taking a look at the nz() function

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[ZmrAbdulla]

Just for a start..
You can use Trim Function to skip the empty fields
something like this..

A1: Trim([Table1]![Field1] & [Table1]![Field2] & [Table1]![Filed3])

regards

Zameer Abdulla
Visit Me

 

[MA04]

Hi Guys,

Thanks for that what i have at present is both a select and append query, though here i am working with a select (search query), the code i have is:

IIF(IsNull([I1].[Locdd]),[I1].[Locd],[I1].[Locdd] & IIf(IsNull([Locd]),'',', ' & [Locd])) AS A2

The code above adds fields Locd and Locdd together seperated by a comma. I am not sure if an 'iff(IsNull)' is what i should be working with. But what i want it to do is check if Locd and Locdd is empty (null)if so then add field STR to that field thats if Strd is present in A1, else leave A2 as it is. I am not sure how to apply the condition i have just stated to the code above. Hope thats a bit clearer,

M-.

 

[MA04]

Hi all,

Let me know if this is not possible but what i want to do is have condition saying in sql:

if locd and locdd is null and if Strd is not null then add STR to field A1,
Else just leave as it is. Any help appreciated,

M-.

 

[KenReay]

if locd and locdd is null and if Strd is not null then add STR to field A1,

IIF(IsNull([locd]) AND IsNull([locdd]) AND NOT IsNull([strD]),[str] & [A1],[A1])

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MA04]

Hi Ken,

Thanks for that but it was not exactly what i was after, though this was my fault as the condition i gave you was a bit vague. What i was trying to achieve was:

Add fields Locd and Locd,
if locd and locdd is null and if Strd is not null then add STR to create field A2,


At present my code adds fields Locd and Locdd to create new field A2:
IIF(IsNull([I1].[Locdd]),[I1].[Locd],[I1].[Locdd] & IIf(IsNull([Locd]),'',', ' & [Locd])) AS A2

Hope thats clearer,
M-.

 

[PHV]

Something like this ?
IIf(IsNull([Locd]) And IsNull([Locdd]) And Not IsNull([Strd]), [STR], IIf(IsNull([I1].[Locdd]),[I1].[Locd],[I1].[Locdd] & IIf(IsNull([Locd]),'',', ' & [Locd]))) AS A2

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MA04]

Thats great PHV, that works thanks. Is it possible to put the condition on my first statement that if Locd and Locdd are NOT null and if Strd is null then enter STR NI A1.

What my code does is it enters a split field '(Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1)' then adds fields Strd and STR to it Seperated by commas, But if split field is less than 4 in character than comma is missed out. Is it possible to keep this characteristic and allow it to:

add split field, add Strd,
if Strd is null then add STR or if Strd is not null and if Locdd or Locd is not null add STR to create A1.
don't add STR if Locd and Locdd are null and if Strd is present
THIS IS WHAT THE CODE LOOKS LIKE AT PRESENT:
IIf(IIf(len(Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1))>0,Len(Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1)),0)<4,Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1) & " " & I1.Strd & I1.STR, Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1) & IIf(IsNull([Strd]),"",", " & [Strd]) & IIf(IsNull([STR]),"",", " & [STR])) AS A2

M-.

 

[MA04]

Hi,

I think it may look something like this but i can't get it to work:

IIf(IIf(len(Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1))>0,Len(Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1)),0)<4,Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1) & " " & I1.Strd & I1.STR, Mid$(';' & I1.PRMF & ';',S1.num+1,S2.num-S1.num-1) & IIf(IsNull([Strd]),"",", " & [Strd]) & IIf(IsNull([Strd]) And Not IsNull([Locdd] & [Locd],"",", " & [STR])) AS A1

I have put in bold what i believe is the problem. What i am trying to say is:

add split field, add Strd, add STR if Locd or Locdd is not null or Strd is null to create A1.

Hope thats clearer,
M-.

 

[MA04]

Hi,

I have changed the bold part above which seems to do the trick but i can't seem to get syntax correct to seperate Strd and STR by a comma, this is the change:

IIf(IsNull([Strd]) OR Not IsNull([Locdd] & [Locd]), [I1].[STR]))

I need to add a coma before it i.e. ,'',', 'but can't seem to get syntax correct.

M-.

 

[MA04]

Hi all,

I think i have managed to resolve the problem this what my syntax looks like:

IIf(IsNull([Strd]) OR Not IsNull([Locdd] & [Locd]), IIf(IsNull([STR]),'',', ' & [I1].[STR])))

Thanks for the help,

M-.