Append Query question regarding nulling data

Nurse Nancy · External Since: Jul 30, 2009 Posts: 17

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
--
Nancy

Mark Andrews · External Since: Oct 19, 2003 Posts: 46

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

"Nurse Nancy" <NurseNancy RemoveThis @discussions.microsoft.com> wrote in message
news:5ED15C01-CCA7-43CF-B0D4-6599F859170F@microsoft.com...
> 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
> --
> Nancy

Nurse Nancy · External Since: Jul 30, 2009 Posts: 17

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

"Mark Andrews" wrote:

> iif(status = "Confirmed",status, Null)
>
> "Nurse Nancy" <NurseNancy.TakeThisOut@discussions.microsoft.com> wrote in message
> news:5ED15C01-CCA7-43CF-B0D4-6599F859170F@microsoft.com...
> > 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
> > --
> > Nancy
>
>
>

Steve Schapel · External Since: Mar 05, 2009 Posts: 14

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

"Nurse Nancy" <NurseNancy.RemoveThis@discussions.microsoft.com> wrote in message
news:2A5EEF78-8BB6-47C2-8AD3-B513CC6FBC70@microsoft.com...
> 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,,,,
>

KARL DEWEY · External Since: Mar 03, 2006 Posts: 1616

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.

"Nurse Nancy" wrote:

> 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
>
>
> "Mark Andrews" wrote:
>
> > iif(status = "Confirmed",status, Null)
> >
> > "Nurse Nancy" <NurseNancy DeleteThis @discussions.microsoft.com> wrote in message
> > news:5ED15C01-CCA7-43CF-B0D4-6599F859170F@microsoft.com...
> > > 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
> > > --
> > > Nancy
> >
> >
> >

Nurse Nancy · External Since: Jul 30, 2009 Posts: 17

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

"KARL DEWEY" wrote:

> 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.
>
>
> "Nurse Nancy" wrote:
>
> > 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
> >
> >
> > "Mark Andrews" wrote:
> >
> > > iif(status = "Confirmed",status, Null)
> > >
> > > "Nurse Nancy" <NurseNancy.RemoveThis@discussions.microsoft.com> wrote in message
> > > news:5ED15C01-CCA7-43CF-B0D4-6599F859170F@microsoft.com...
> > > > 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
> > > > --
> > > > Nancy
> > >
> > >
> > >

John W. Vinson · External Since: Jan 29, 2004 Posts: 2505

On Thu, 15 Oct 2009 09:51:02 -0700, Nurse Nancy
<NurseNancy.DeleteThis@discussions.microsoft.com> wrote:

>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)

--

John W. Vinson [MVP]

Nurse Nancy · External Since: Jul 30, 2009 Posts: 17

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

"Steve Schapel" wrote:

> 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
>
>
> "Nurse Nancy" <NurseNancy.RemoveThis@discussions.microsoft.com> wrote in message
> news:2A5EEF78-8BB6-47C2-8AD3-B513CC6FBC70@microsoft.com...
> > 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 · External Since: Mar 05, 2009 Posts: 14

Nancy,

Yes.

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

--
Steve Schapel, Microsoft Access MVP

"Nurse Nancy" <NurseNancy.RemoveThis@discussions.microsoft.com> wrote in message
news:F0BC7D02-EDC2-4A77-81D5-DC64D61718AF@microsoft.com...
> THANKS it worked,, it was the missing = sign!!!!!
> Can I add additional values with "Confirmed"? do i use an 'or' ?