Help Writing A Query

[paul29berks]

I am trying to write a statement within my select query and trying to say:

If [field1] = 1 then [field X] & field [Y], if [field2] = 2 then [field x] & field [Y]


Can anyone suggets how I could write this into my query?

Thanks

 

[Norie]

Paul

Are you trying to concatenate fields X and Y?

What do you want to do when the conditions aren't met?

Iif(Or([Field1]=1, [Field2]=2, [FieldX] & [FieldY], "")

 

[paul29berks]

Thanks for your reply.

just to show the option if field 1 = value 1 then use both field x and y, if field 2 = 2 then use both field a and b

Thanks

 

[Norie]

Paul

You'll need to explain further, you never mentioned A & B and you still haven't indicated what result you would want if the condition(s) weren't met.

Iif([Field1]=1, [FieldX] & [FieldY], Iif([Field2]=2, [FieldA] & [FieldB], ""))

 

[paul29berks]

ADVERTISEMENT

Thanks.

I am basically trying to say:

IF field 1 = value 1 then show me fields a and field b
if field 1 = value 2 then show me fields x and y

Criteria to return different results from either fields a and b or field x and y depending on whether value is 1 or 2.

Hope this helps?

Paul

 

[Norie]

Did you try the expression I posted?

 

[pritcham]

Hi

The expression Norie posted should do the trick for you for concatenating two fields, chich fields depending on the value held in your variable.

If what you're trying to say is that you want to selectively choose which fields are included in your actual query statement based on the value of your 'variable' - i.e.

If field1 = 1 then
Select fielda, fieldb

ElseIf field1 = 2 then 
Select fieldx, fieldy

- if that's the case then I'm not 100% sure as to whether you can carry out this sort of conditional program flow through Access SQL. If that's what you need to do then I'd suggest taking a look at amending the QueryDef dynamically through your VBA code

Hope that helps
Martin