Append Query question regarding nulling data

[Nurse Nancy]

Hi
I have an append query that is working fine. But for some of the fields, if
they contain certain values, I want them to be nulled out when they are
appended.
For instance if the status is anything other than 'Confirmed', I want it to
be nulled when appended.

Does anyone know how to do this?
thanks

 

[Mark Andrews]

iif(status = "Confirmed",status, Null)

 

[Nurse Nancy]

thanks so much for your quick response, I tried it and it is only appending
the records where the status is 'Confirmed' I wanted it to append the other
records and null out the status,,
Did i do something wrong,,,,

here is the sql


INSERT INTO [RADIO BUY AND POST TABLE] ( [Campaign ID], Keep, [Week Start
Date], [Customer ID], [Product Name], [Buy Status], MISC, CPC, [Flight Start
Date], [Flight End Date], [DR Rep], [Primary Day Part], [Other Day Part],
[Opt In], [Station Rep], [Rotator Day Part], [# Prime Spots], [# Rot Spots],
[# Other Spots], [Prime Rate], [Rot Rate], [Other Rate], [Station Comments],
[Total Calls], [Source Num], [Weekly Budget], STATION )
SELECT RBPT.[Campaign ID], RBPT.Keep, [Forms]![Maintain Products Form]![Week
2] AS [Week Start Date], RBPT.[Customer ID], RBPT.[Product Name], RBPT.[Buy
Status], RBPT.MISC, RBPT.CPC, RBPT.[Flight Start Date], RBPT.[Flight End
Date], RBPT.[DR Rep], RBPT.[Primary Day Part], RBPT.[Other Day Part],
RBPT.[Opt In], RBPT.[Station Rep], RBPT.[Rotator Day Part], RBPT.[# Prime
Spots], RBPT.[# Rot Spots], RBPT.[# Other Spots], RBPT.[Prime Rate],
RBPT.[Rot Rate], RBPT.[Other Rate], RS.[Station Comments],
[M]+[T]+[W]+[Th]+[F] AS [Total Calls], RBPT.[Source Num], RBPT.[Weekly
Budget], RBPT.STATION
FROM ((([Contacts Merged Table] AS CON RIGHT JOIN ([RADIO BUY AND POST
TABLE] AS RBPT LEFT JOIN [RADIO STATIONS TABLE] AS RS ON
RBPT.STATION=RS.[Station Call Letters]) ON CON.[Contact ID]=RS.[Contact ID])
LEFT JOIN [Buy Status Table] ON RBPT.[Buy Status]=[Buy Status Table].[Buy
Status]) LEFT JOIN [MARKETS TABLE] ON RS.[Market ID]=[MARKETS TABLE].[Market
ID]) INNER JOIN [Maintain Campaign Products Form Query] AS MCPFQ ON
RBPT.[Campaign ID]=MCPFQ.[Campaign ID]
WHERE (((RBPT.[Campaign ID])=Forms![Maintain Products Form]![Campaign ID])
And ((RBPT.Keep)=Yes) And ((RBPT.[Buy Status])=IIf([RBPT.Buy
Status]="Confirmed",[Buy Status Table].[Buy Status],Null)) And ((RBPT.[Week
Start Date])=[RBPT.Flight Start Date]));

 

[Steve Schapel]

Nancy,

The expression needs to go in the FROM clause of the query, not the WHERE
clause.

.... SELECT ... RBPT.[Product Name], IIf([Buy Status]="Confirmed",[Buy
Status],Null) AS ConfirmedStatus, RBPT.MISC, ...

 

[KARL DEWEY]

The problem is in this part of the SQL --
And ((RBPT.[Buy Status])=IIf([RBPT.Buy Status]="Confirmed",[Buy Status
Table].[Buy Status],Null))

Remove it completely.

Change this SELECT part --
RBPT.[Buy Status], to
IIF(RBPT.[Buy Status] "Confirmed", RBPT.[Buy Status], Null) AS Buy_Status_Ck,

This is it edited --
INSERT INTO [RADIO BUY AND POST TABLE] ( [Campaign ID], Keep, [Week Start
Date], [Customer ID], [Product Name], [Buy Status], MISC, CPC, [Flight Start
Date], [Flight End Date], [DR Rep], [Primary Day Part], [Other Day Part],
[Opt In], [Station Rep], [Rotator Day Part], [# Prime Spots], [# Rot Spots],
[# Other Spots], [Prime Rate], [Rot Rate], [Other Rate], [Station Comments],
[Total Calls], [Source Num], [Weekly Budget], STATION )
SELECT RBPT.[Campaign ID], RBPT.Keep, [Forms]![Maintain Products Form]![Week
2] AS [Week Start Date], RBPT.[Customer ID], RBPT.[Product Name],
IIF(RBPT.[Buy Status] "Confirmed", RBPT.[Buy Status], Null) AS Buy_Status_Ck,
RBPT.MISC, RBPT.CPC, RBPT.[Flight Start Date], RBPT.[Flight End Date],
RBPT.[DR Rep], RBPT.[Primary Day Part], RBPT.[Other Day Part], RBPT.[Opt In],
RBPT.[Station Rep], RBPT.[Rotator Day Part], RBPT.[# Prime Spots], RBPT.[#
Rot Spots], RBPT.[# Other Spots], RBPT.[Prime Rate], RBPT.[Rot Rate],
RBPT.[Other Rate], RS.[Station Comments], [M]+[T]+[W]+[Th]+[F] AS [Total
Calls], RBPT.[Source Num], RBPT.[Weekly Budget], RBPT.STATION
FROM ((([Contacts Merged Table] AS CON RIGHT JOIN ([RADIO BUY AND POST
TABLE] AS RBPT LEFT JOIN [RADIO STATIONS TABLE] AS RS ON
RBPT.STATION=RS.[Station Call Letters]) ON CON.[Contact ID]=RS.[Contact ID])
LEFT JOIN [Buy Status Table] ON RBPT.[Buy Status]=[Buy Status Table].[Buy
Status]) LEFT JOIN [MARKETS TABLE] ON RS.[Market ID]=[MARKETS TABLE].[Market
ID]) INNER JOIN [Maintain Campaign Products Form Query] AS MCPFQ ON
RBPT.[Campaign ID]=MCPFQ.[Campaign ID]
WHERE (((RBPT.[Campaign ID])=Forms![Maintain Products Form]![Campaign ID])
And ((RBPT.Keep)=Yes) And ((RBPT.[Week Start Date])=[RBPT.Flight Start
Date]));


--
Build a little, test a little.

thanks so much for your quick response, I tried it and it is only appending
the records where the status is 'Confirmed' I wanted it to append the other
records and null out the status,,
Did i do something wrong,,,,

here is the sql


INSERT INTO [RADIO BUY AND POST TABLE] ( [Campaign ID], Keep, [Week Start
Date], [Customer ID], [Product Name], [Buy Status], MISC, CPC, [Flight Start
Date], [Flight End Date], [DR Rep], [Primary Day Part], [Other Day Part],
[Opt In], [Station Rep], [Rotator Day Part], [# Prime Spots], [# Rot Spots],
[# Other Spots], [Prime Rate], [Rot Rate], [Other Rate], [Station Comments],
[Total Calls], [Source Num], [Weekly Budget], STATION )
SELECT RBPT.[Campaign ID], RBPT.Keep, [Forms]![Maintain Products Form]![Week
2] AS [Week Start Date], RBPT.[Customer ID], RBPT.[Product Name], RBPT.[Buy
Status], RBPT.MISC, RBPT.CPC, RBPT.[Flight Start Date], RBPT.[Flight End
Date], RBPT.[DR Rep], RBPT.[Primary Day Part], RBPT.[Other Day Part],
RBPT.[Opt In], RBPT.[Station Rep], RBPT.[Rotator Day Part], RBPT.[# Prime
Spots], RBPT.[# Rot Spots], RBPT.[# Other Spots], RBPT.[Prime Rate],
RBPT.[Rot Rate], RBPT.[Other Rate], RS.[Station Comments],
[M]+[T]+[W]+[Th]+[F] AS [Total Calls], RBPT.[Source Num], RBPT.[Weekly
Budget], RBPT.STATION
FROM ((([Contacts Merged Table] AS CON RIGHT JOIN ([RADIO BUY AND POST
TABLE] AS RBPT LEFT JOIN [RADIO STATIONS TABLE] AS RS ON
RBPT.STATION=RS.[Station Call Letters]) ON CON.[Contact ID]=RS.[Contact ID])
LEFT JOIN [Buy Status Table] ON RBPT.[Buy Status]=[Buy Status Table].[Buy
Status]) LEFT JOIN [MARKETS TABLE] ON RS.[Market ID]=[MARKETS TABLE].[Market
ID]) INNER JOIN [Maintain Campaign Products Form Query] AS MCPFQ ON
RBPT.[Campaign ID]=MCPFQ.[Campaign ID]
WHERE (((RBPT.[Campaign ID])=Forms![Maintain Products Form]![Campaign ID])
And ((RBPT.Keep)=Yes) And ((RBPT.[Buy Status])=IIf([RBPT.Buy
Status]="Confirmed",[Buy Status Table].[Buy Status],Null)) And ((RBPT.[Week
Start Date])=[RBPT.Flight Start Date]));

--
Nancy

iif(status = "Confirmed",status, Null)

 

[Nurse Nancy]

hi, thanks for your help,,
I tried it but i am getting a syntax error,,,
I did not use the exact sql that you gave me,, i tried to replace with your
change, since i have made other changes to the sql since i posted that,,
it's actually 'confirmed flight' instead of 'confirmed' as the buy status,,,
here is my sql

Syntax Error, Missing Operator in query expression IIF(RBPT.[Buy Status]
"Confirmed Flight", RBPT.[Buy Status], Null)

INSERT INTO [RADIO BUY AND POST TABLE] ( [Campaign ID], Keep, [Week Start
Date], [Customer ID], [Product Name], [Buy Status], MISC, CPC, [Flight Start
Date], [Flight End Date], [DR Rep], [Primary Day Part], [Other Day Part],
[Opt In], [Station Rep], [Rotator Day Part], [# Prime Spots], [# Rot Spots],
[# Other Spots], [Prime Rate], [Rot Rate], [Other Rate], [Station Comments],
[Source Num], [Weekly Budget], STATION )
SELECT RBPT.[Campaign ID], RBPT.Keep, [Forms]![Maintain Products Form]![Week
6] AS [Week Start Date], RBPT.[Customer ID], RBPT.[Product Name],
IIF(RBPT.[Buy Status] "Confirmed Flight", RBPT.[Buy Status], Null) AS
Buy_Status_Ck, RBPT.MISC, RBPT.CPC, RBPT.[Flight Start Date], RBPT.[Flight
End Date], RBPT.[DR Rep], RBPT.[Primary Day Part], RBPT.[Other Day Part],
RBPT.[Opt In], RBPT.[Station Rep], RBPT.[Rotator Day Part], RBPT.[# Prime
Spots], RBPT.[# Rot Spots], RBPT.[# Other Spots], RBPT.[Prime Rate],
RBPT.[Rot Rate], RBPT.[Other Rate], RS.[Station Comments], RBPT.[Source Num],
RBPT.[Weekly Budget], RBPT.STATION
FROM ((([Contacts Merged Table] AS CON RIGHT JOIN ([RADIO BUY AND POST
TABLE] AS RBPT LEFT JOIN [RADIO STATIONS TABLE] AS RS ON RBPT.STATION =
RS.[Station Call Letters]) ON CON.[Contact ID] = RS.[Contact ID]) LEFT JOIN
[Buy Status Table] ON RBPT.[Buy Status] = [Buy Status Table].[Buy Status])
LEFT JOIN [MARKETS TABLE] ON RS.[Market ID] = [MARKETS TABLE].[Market ID])
INNER JOIN [Maintain Campaign Products Form Query] AS MCPFQ ON RBPT.[Campaign
ID] = MCPFQ.[Campaign ID]
WHERE (((RBPT.[Campaign ID])=[Forms]![Maintain Products Form]![Campaign ID])
AND ((RBPT.Keep)=Yes));

--
Nancy

The problem is in this part of the SQL --
And ((RBPT.[Buy Status])=IIf([RBPT.Buy Status]="Confirmed",[Buy Status
Table].[Buy Status],Null))

Remove it completely.

Change this SELECT part --
RBPT.[Buy Status], to
IIF(RBPT.[Buy Status] "Confirmed", RBPT.[Buy Status], Null) AS Buy_Status_Ck,

This is it edited --
INSERT INTO [RADIO BUY AND POST TABLE] ( [Campaign ID], Keep, [Week Start
Date], [Customer ID], [Product Name], [Buy Status], MISC, CPC, [Flight Start
Date], [Flight End Date], [DR Rep], [Primary Day Part], [Other Day Part],
[Opt In], [Station Rep], [Rotator Day Part], [# Prime Spots], [# Rot Spots],
[# Other Spots], [Prime Rate], [Rot Rate], [Other Rate], [Station Comments],
[Total Calls], [Source Num], [Weekly Budget], STATION )
SELECT RBPT.[Campaign ID], RBPT.Keep, [Forms]![Maintain Products Form]![Week
2] AS [Week Start Date], RBPT.[Customer ID], RBPT.[Product Name],
IIF(RBPT.[Buy Status] "Confirmed", RBPT.[Buy Status], Null) AS Buy_Status_Ck,
RBPT.MISC, RBPT.CPC, RBPT.[Flight Start Date], RBPT.[Flight End Date],
RBPT.[DR Rep], RBPT.[Primary Day Part], RBPT.[Other Day Part], RBPT.[Opt In],
RBPT.[Station Rep], RBPT.[Rotator Day Part], RBPT.[# Prime Spots], RBPT.[#
Rot Spots], RBPT.[# Other Spots], RBPT.[Prime Rate], RBPT.[Rot Rate],
RBPT.[Other Rate], RS.[Station Comments], [M]+[T]+[W]+[Th]+[F] AS [Total
Calls], RBPT.[Source Num], RBPT.[Weekly Budget], RBPT.STATION
FROM ((([Contacts Merged Table] AS CON RIGHT JOIN ([RADIO BUY AND POST
TABLE] AS RBPT LEFT JOIN [RADIO STATIONS TABLE] AS RS ON
RBPT.STATION=RS.[Station Call Letters]) ON CON.[Contact ID]=RS.[Contact ID])
LEFT JOIN [Buy Status Table] ON RBPT.[Buy Status]=[Buy Status Table].[Buy
Status]) LEFT JOIN [MARKETS TABLE] ON RS.[Market ID]=[MARKETS TABLE].[Market
ID]) INNER JOIN [Maintain Campaign Products Form Query] AS MCPFQ ON
RBPT.[Campaign ID]=MCPFQ.[Campaign ID]
WHERE (((RBPT.[Campaign ID])=Forms![Maintain Products Form]![Campaign ID])
And ((RBPT.Keep)=Yes) And ((RBPT.[Week Start Date])=[RBPT.Flight Start
Date]));


--
Build a little, test a little.

thanks so much for your quick response, I tried it and it is only appending
the records where the status is 'Confirmed' I wanted it to append the other
records and null out the status,,
Did i do something wrong,,,,

here is the sql


INSERT INTO [RADIO BUY AND POST TABLE] ( [Campaign ID], Keep, [Week Start
Date], [Customer ID], [Product Name], [Buy Status], MISC, CPC, [Flight Start
Date], [Flight End Date], [DR Rep], [Primary Day Part], [Other Day Part],
[Opt In], [Station Rep], [Rotator Day Part], [# Prime Spots], [# Rot Spots],
[# Other Spots], [Prime Rate], [Rot Rate], [Other Rate], [Station Comments],
[Total Calls], [Source Num], [Weekly Budget], STATION )
SELECT RBPT.[Campaign ID], RBPT.Keep, [Forms]![Maintain Products Form]![Week
2] AS [Week Start Date], RBPT.[Customer ID], RBPT.[Product Name], RBPT.[Buy
Status], RBPT.MISC, RBPT.CPC, RBPT.[Flight Start Date], RBPT.[Flight End
Date], RBPT.[DR Rep], RBPT.[Primary Day Part], RBPT.[Other Day Part],
RBPT.[Opt In], RBPT.[Station Rep], RBPT.[Rotator Day Part], RBPT.[# Prime
Spots], RBPT.[# Rot Spots], RBPT.[# Other Spots], RBPT.[Prime Rate],
RBPT.[Rot Rate], RBPT.[Other Rate], RS.[Station Comments],
[M]+[T]+[W]+[Th]+[F] AS [Total Calls], RBPT.[Source Num], RBPT.[Weekly
Budget], RBPT.STATION
FROM ((([Contacts Merged Table] AS CON RIGHT JOIN ([RADIO BUY AND POST
TABLE] AS RBPT LEFT JOIN [RADIO STATIONS TABLE] AS RS ON
RBPT.STATION=RS.[Station Call Letters]) ON CON.[Contact ID]=RS.[Contact ID])
LEFT JOIN [Buy Status Table] ON RBPT.[Buy Status]=[Buy Status Table].[Buy
Status]) LEFT JOIN [MARKETS TABLE] ON RS.[Market ID]=[MARKETS TABLE].[Market
ID]) INNER JOIN [Maintain Campaign Products Form Query] AS MCPFQ ON
RBPT.[Campaign ID]=MCPFQ.[Campaign ID]
WHERE (((RBPT.[Campaign ID])=Forms![Maintain Products Form]![Campaign ID])
And ((RBPT.Keep)=Yes) And ((RBPT.[Buy Status])=IIf([RBPT.Buy
Status]="Confirmed",[Buy Status Table].[Buy Status],Null)) And ((RBPT.[Week
Start Date])=[RBPT.Flight Start Date]));

--
Nancy

iif(status = "Confirmed",status, Null)

Hi
I have an append query that is working fine. But for some of the fields,
if
they contain certain values, I want them to be nulled out when they are
appended.
For instance if the status is anything other than 'Confirmed', I want it
to
be nulled when appended.

Does anyone know how to do this?
thanks

 

[John W. Vinson]

hi, thanks for your help,,
I tried it but i am getting a syntax error,,,
I did not use the exact sql that you gave me,, i tried to replace with your
change, since i have made other changes to the sql since i posted that,,
it's actually 'confirmed flight' instead of 'confirmed' as the buy status,,,
here is my sql

Syntax Error, Missing Operator in query expression IIF(RBPT.[Buy Status]
"Confirmed Flight", RBPT.[Buy Status], Null)

You left out Karl's = sign: try

IIF(RBPT.[Buy Status] = "Confirmed Flight", RBPT.[Buy Status], Null)

 

[Nurse Nancy]

THANKS it worked,, it was the missing = sign!!!!!
Can I add additional values with "Confirmed"? do i use an 'or' ?
--
Nancy

Nancy,

The expression needs to go in the FROM clause of the query, not the WHERE
clause.

.... SELECT ... RBPT.[Product Name], IIf([Buy Status]="Confirmed",[Buy
Status],Null) AS ConfirmedStatus, RBPT.MISC, ...

--
Steve Schapel, Microsoft Access MVP

thanks so much for your quick response, I tried it and it is only
appending
the records where the status is 'Confirmed' I wanted it to append the
other
records and null out the status,,
Did i do something wrong,,,,

.

 

[Steve Schapel]

Nancy,

Yes.

IIf([Buy Status]="Confirmed" Or [Buy Status]="Something Else",[Buy
Status],Null)