Running a query with an "if/than" filter

[AudiA4_20T]

Hello everyone,

First off, how do I donate to this site? It's helped me tremendously at work and I absolutely want to throw a few bucks in.

Ok, so I have a spreadsheet with (2) different columns of "Name" from 2 sources. I want to run a query to say "If column #1 is N/A, display column 2. If column #2 is N/A, filter out"



How do I write that into the query and what type do I use?

TIA!

[pbaldy]

Along the lines of

IIf(Column1 = "N/A", Column2, Column1)

More info in help on the IIf() function. You might also find Switch() appropriate.

[AudiA4_20T]

nvm got it

[pbaldy]

For starters, you've "simplified" it to an invalid syntax; the IIf() function has 3 parts to it.

Second, I wouldn't see that as a criteria. I intended it as a new field in the query. Can you describe what you're trying to accomplish?

[AudiA4_20T]

For starters, you've "simplified" it to an invalid syntax; the IIf() function has 3 parts to it.

Second, I wouldn't see that as a criteria. I intended it as a new field in the query. Can you describe what you're trying to accomplish?

Ok, I'm trying to think of where I'm going wrong here.

1. I have 2 spreadsheets and I want to update data from spreadsheet 2 to spreadsheet 1.
2. Spreadsheet 1 already is about 50% filled
3. I want to ONLY update the ones that are NOT filled in spreadsheet 1.

So, have something that looks like this

Field: TownID
Table: Finance Shell
Update To: [Assets060612].[TownID]
Criteria: see below

so for Criteria, I first though:

1. "Is Null" in my mind that would mean to ONLY update it if there was no information in there. Well that keeps giving me 0 records which is not true.
2. "IIF([Finance Shell].[Town])=Null" but that's not working. I know I have some syntax wrong

[AudiA4_20T]

Ok I realized it wasn't Null it said "Unallocated"

so I changed it to

IIf([Finance Shell].[Town ID]="Unallocated",[Assets060612].[City],[Finance Shell].[Town ID])

[AudiA4_20T]

Now I'm trying another one here updating data in its own spreadsheet, but it's not updating

IIf([Username]=Null,[VZW Put Together],[Username])

[pbaldy]

I don't work with linked spreadsheets much. Typically an update query with a criteria would be the solution:

UPDATE TableName
SET FieldName = Whatever
WHERE SomeField Is Null