By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,446 Members | 2,998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,446 IT Pros & Developers. It's quick & easy.

Append Import Accept Null Values

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a
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.
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.