Append Import Accept Null Values

[JOEP]

What do I need to do to allow an append query to post null values to
records in a field of the destination table? Basically I want to allow
records with null values to post to the table. The append query will
not work unless there are values in the data i am attempting to send.
I want the fields in the destination table to accept null and
populated values. Any help here would be gladly accepted.

thanks

[Larry Linson]

You can't override the definition of the destination table. Perhaps you need
to create a temporary table into which you append the records, which does
not have those restrictions. If I have misunderstood your situation, please
post here to clarify.

Larry Linson
Microsoft Access MVP

"JOEP" <jp******@ibtco.com> wrote in message
news:d4************************@posting.google.com ...

What do I need to do to allow an append query to post null values to
records in a field of the destination table? Basically I want to allow
records with null values to post to the table. The append query will
not work unless there are values in the data i am attempting to send.
I want the fields in the destination table to accept null and
populated values. Any help here would be gladly accepted.

thanks

[JOEP]

Did that and now want to insert a given value for the null values
while running the append query. Is it better to use the Nz function of
an IIf statement. Basically I want to insert "NA" for all null values.
Can I insert anything other than "0" when using the Nz function?

Would I write the function Nz([fieldname].[Queryname], valueifnull])?
Insert the function into the field row in the query design view, e.g.
strFundNumber:Nz([strFundNumber].[Queryname], NA])?

I am running into null values in the strfundnumber, strclientacct and
strfundgroup appended data which cause the records to not append to
the destination table. All the fields layouts are the same from the
query fields to the destination table fields. But if the records have
null values in the data coming from the fields above the append fails.

my SQL is below...if that helps

INSERT INTO tblTransactions ( strFundNumber, strclientacct,
strTransactionDescription, datTradeDate, dblBasePrincAmt,
dblShareAmount, dblBasePrice, strFundGroup, dblLocalNetAmt,
strBrokerName, blnExcludeTransaction, dblLocalCommissnAmt,
intClassActionID )
SELECT QryselectWellsPortData.strFundNumber,
QryselectWellsPortData.FundGroupNumber, QryselectWellsPortData.TYPE,
QryselectWellsPortData.TradeDate,
QryselectWellsPortData.BasePriceAmount,
QryselectWellsPortData.ShareAmount, QryselectWellsPortData.BasePrice,
QryselectWellsPortData.strFundGroup, QryselectWellsPortData.loclamt,
QryselectWellsPortData.brk, QryselectWellsPortData.exclud,
QryselectWellsPortData.locnetamt, [enter ClassActionID] AS CAID
FROM QryselectWellsPortData;

"Larry Linson" <bo*****@localhost.not> wrote in message news:<2KXfd.6437$8R.5304@trnddc02>...

You can't override the definition of the destination table. Perhaps you need
to create a temporary table into which you append the records, which does
not have those restrictions. If I have misunderstood your situation, please
post here to clarify.

Larry Linson
Microsoft Access MVP

"JOEP" <jp******@ibtco.com> wrote in message
news:d4************************@posting.google.com ...

What do I need to do to allow an append query to post null values to
records in a field of the destination table? Basically I want to allow
records with null values to post to the table. The append query will
not work unless there are values in the data i am attempting to send.
I want the fields in the destination table to accept null and
populated values. Any help here would be gladly accepted.

thanks

[Pieter Linden]

I wouldn't recommend doing that. You can FORMAT a null value any way
you want in your report. Or you can substitute a value if you use
IsNull(varSomeValue).

Null in Access does NOT mean "NA" to the database engine. But if you
wanna rape you data, use and update query or a series of them to
update each field or whatever.