Error in Append query (1 Viewer)

france

New member
Local time
Today, 11:39
Joined
Jan 26, 2020
Messages
8
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

Ignore List Poster Boy
Local time
Today, 05:39
Joined
Apr 27, 2015
Messages
6,286
Wrap the appended fields in your query with the NZ() function: Nz([YourFieldName])
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:39
Joined
Jan 23, 2006
Messages
15,364
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

New member
Local time
Today, 11:39
Joined
Jan 26, 2020
Messages
8
:)Thank you NG and Jdraw. Let me try it quickly and what happens next.
 

france

New member
Local time
Today, 11:39
Joined
Jan 26, 2020
Messages
8
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

Enthusiastic Amateur
Local time
Today, 09:39
Joined
Sep 21, 2011
Messages
14,050
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? :confused:
I would be using the Values statement.
 

france

New member
Local time
Today, 11:39
Joined
Jan 26, 2020
Messages
8
Ok Gasman let me change the it and see. thanks
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 05:39
Joined
Apr 27, 2015
Messages
6,286
Also...do not place the NZ() portion inside the square brackets, only the field names. Look closely at the example I provided...
 

france

New member
Local time
Today, 11:39
Joined
Jan 26, 2020
Messages
8
Thank you, NG. I and worked around it and it is working now.

Guys, you are all a star!
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 05:39
Joined
Apr 27, 2015
Messages
6,286
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:39
Joined
Feb 19, 2002
Messages
42,981
You might want to rethink your table design. Optional fields should normally default to null. For example, you have an Order that contains a ShipDate. Since ShipDate isn't known when the order is entered, it should be null. If you use Nz() to populate ShipDate, it will end up as 0 which in the world of dates would be Dec 30, 1899. So, the ShipDate would be earlier than the OrderDate which makes no sense.

Regarding ZeroLengthStrings, I personally never allow them. It gets too confusing having to deal with both ZLS AND Null and I never want a required text field to contain a ZLS because that just doesn't make any sense.
 

Users who are viewing this thread

Top Bottom