# Help Writing A Query

#### paul29berks

##### Active Member
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

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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], "")

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

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], ""))

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

Did you try the expression I posted?

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.

Code:
``````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

Replies
1
Views
128
Replies
1
Views
258
Replies
1
Views
173
Replies
3
Views
210
Replies
6
Views
187

1,196,049
Messages
6,013,107
Members
441,748
Latest member
MrBigglesworth

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back