need help with append query

[DJTI via AccessMonster.com]

Thank you in advance for help on this problem. I have an append query to
enter data into a table. The below sql statement is what I am using. I need
to edit the query to only append QCashPdValue into [Investment Details] if
the [PurchaseDate] of the Investment is before the beginning of year. If the
PurchaseDate is after the beginning of the year [QcashPdValue] is null.

Is this possible? Am I going about this wrong? Any help is greatly
appreciated.

INSERT INTO [Investment Details] ( CurShareValue, DateTransaction, QDivValue,
QCashPdValue, InvestmentID, PurchaseDate )
SELECT CurrentValuesTmp.CurShareValue, CurrentValuesTmp.DateTransaction,
CurrentValuesTmp.QDivValue, CurrentValuesTmp.QCashPdValue, Investment.
InvestmentID, Investment.PurchaseDate
FROM CurrentValuesTmp, Investment
WHERE (((Investment.InActive)=False));

 

[John Spencer]

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

INSERT INTO [Investment Details] ( CurShareValue, DateTransaction, QDivValue,
QCashPdValue, InvestmentID, PurchaseDate )
SELECT CurrentValuesTmp.CurShareValue
, CurrentValuesTmp.DateTransaction
, CurrentValuesTmp.QDivValue
, IIF(Year([PurchaseDate])< Year(Date()),CurrentValuesTmp.QCashPdValue,Null)
, Investment.InvestmentID, Investment.PurchaseDate
FROM CurrentValuesTmp, Investment
WHERE (((Investment.InActive)=False));


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[DJTI via AccessMonster.com]

John,
Thanks for your help, this worked wonderfully. Also thanks for Step 1 and
Step 2, I had that already down pat, but reminders are always appreciated.
Deb

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

INSERT INTO [Investment Details] ( CurShareValue, DateTransaction, QDivValue,
QCashPdValue, InvestmentID, PurchaseDate )
SELECT CurrentValuesTmp.CurShareValue
, CurrentValuesTmp.DateTransaction
, CurrentValuesTmp.QDivValue
, IIF(Year([PurchaseDate])< Year(Date()),CurrentValuesTmp.QCashPdValue,Null)
, Investment.InvestmentID, Investment.PurchaseDate
FROM CurrentValuesTmp, Investment
WHERE (((Investment.InActive)=False));

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thank you in advance for help on this problem. I have an append query to
enter data into a table. The below sql statement is what I am using. I need

[quoted text clipped - 12 lines]

FROM CurrentValuesTmp, Investment
WHERE (((Investment.InActive)=False));

 

[John Spencer]

I include that alert in most of my postings for update, delete, and insert
queries. Two reasons: people don't know or they forget and if things go wrong
I can say "I TOLD you"

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

John,
Thanks for your help, this worked wonderfully. Also thanks for Step 1 and
Step 2, I had that already down pat, but reminders are always appreciated.
Deb

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

INSERT INTO [Investment Details] ( CurShareValue, DateTransaction, QDivValue,
QCashPdValue, InvestmentID, PurchaseDate )
SELECT CurrentValuesTmp.CurShareValue
, CurrentValuesTmp.DateTransaction
, CurrentValuesTmp.QDivValue
, IIF(Year([PurchaseDate])< Year(Date()),CurrentValuesTmp.QCashPdValue,Null)
, Investment.InvestmentID, Investment.PurchaseDate
FROM CurrentValuesTmp, Investment
WHERE (((Investment.InActive)=False));

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thank you in advance for help on this problem. I have an append query to
enter data into a table. The below sql statement is what I am using. I need

[quoted text clipped - 12 lines]

FROM CurrentValuesTmp, Investment
WHERE (((Investment.InActive)=False));