Conditional Insert Query

[Splatgore]

Hi there,

I've got a query which I'm trying to write which I can't seem to get right.

Basically I am trying to select a set of data from table A and insert it into another table. However, if the set of data I select returns 0 records then I want to insert a default value into the table.

For example:

INSERT INTO ExtrapolateID (FuncLoc)
SELECT IIF(FuncLoc IS NULL, '20098-E002-H00', FuncLoc)
FROM FlocExtract
WHERE FuncLoc LIKE '20098-E002-H*'

That is what I started with and I've been trying to build on it but no matter what I try I can't get it to work properly when there are no values LIKE '20098-E002-H*'.

I'm sure I'm being a complete muppet but I can't figure out what I am doing wrong.

I hope someone can help with this.

Many thanks,

James

 

[John Big Booty]

I'd probably use a DCount() to check if your append query has any records, and then either run a straight append query with those record or an append query with your default record.

 

[Splatgore]

Sorry for the delay! Been crazy busy doing some other work and haven't had time to get back to this particular problem.

I'll have a play with your suggestion and see if I can get it to work. It is driving me nuts but it is such a small issue in relation to the rest of the stuff I have to do I probably shouldn't waste too much time on it.