Error in Append query

[france]

I am battling with appending a table in the access database that contains some null values or '0'. Can anyone help me?

Thank you

 

[NauticalGent]

Wrap the appended fields in your query with the NZ() function: Nz([YourFieldName])

 

[jdraw]

As NG suggested, NZ() is a common method to handle NULL values, but perhaps there is more detail in your situation as to why there are NULLs in your data.

 

[france]

Thank you NG and Jdraw. Let me try it quickly and what happens next.

 

[france]

Hi NG
I have tried your recommendation but it rather displayed an error of "unknown field". Here is my append query:

INSERT INTO BatchedFilesTable ( [Nz(PensionNo)], [Nz(SurnameInitials)], [Nz(ChildID)], [Nz(Type)], [Nz(TDate)], [Nz(ResCode)], [Nz(District)], [Nz(LocalOffice)], [Nz(BRMBarcode)], [Nz(BatchNo)], [Nz(UserID)] )
SELECT Forms!frmFileCapture!TxtPension AS Expr1, Forms!frmFileCapture!TxtSurname AS Expr2, Forms!frmFileCapture!TxtChildID AS Expr3, Forms!frmFileCapture!TxtType AS Expr4, Forms!frmFileCapture!TxtDate AS Expr5, Forms!frmFileCapture!TxtRescode AS Expr6, Forms!frmFileCapture!TxtDistrict AS Expr7, Forms!frmFileCapture!TxtLocalOffice AS Expr8, [Forms]![frmFileCapture]![TxtBRMBarcodeNo] AS Expr9, [Forms]![frmFileCapture]![TxtBatchNo] AS Expr10, [Forms]![frmFileCapture]![TxtUserID] AS Expr11;

Access changed brackets' position. after I tried to run it.

 

[Gasman]

PMFJI, but I believe you should be using NZ() on the sources not the destination fields?

Also not even sure you can select from form controls?
I would be using the Values statement.

 

[france]

Ok Gasman let me change the it and see. thanks

 

[NauticalGent]

Also...do not place the NZ() portion inside the square brackets, only the field names. Look closely at the example I provided...

 

[france]

Thank you, NG. I and worked around it and it is working now.

Guys, you are all a star!

 

[NauticalGent]

You're quite welcome! Also, do yourself a favor and heed JDraw's words regarding the fact that your input contains Nulls. If it is unavoidable, then it is unavoidable, but anything you can do to improve the quality will pay dividends.

 

[france]

Sure, thank you NG