Null field in append query

[Cyberwolf]

I have a form with 4 unbound text boxes that get added to a table via an
append query. If I leave one of the fields blank, the record I am trying to
append does not. I have set the properties of the fileds in the table I am
appending to to allow null values, and not be required, but htis does not
help. It seems I need to have some sort of value to alow the record to be
appended. Is there a way to set it so the field can be null and still append?

Thanks,

 

[Jerry Whittle]

1. Show use the SQL.

2. Any error messages?

3. Are any or all of those fields part of a primary key or unique index?

 

[Cyberwolf]

Here is the SQL

INSERT INTO [Dillards Journal Entries] ( [Date], [Line #], [Store #], [Ref
#], Debit, Net, Reason, FOB, Div, OrderNo, Remarks, [Entered By] )
SELECT [Dillards Current TB].[REF DTE], [Dillards Current TB].[LINE#],
[Dillards Current TB].STORE, [Dillards Current TB].[REF#], [Dillards Current
TB].Amount AS [Debit Amt], [Dillards Current TB].Amount AS Net,
[forms]![frmIllards Current TB]![txtARCode] AS Reason,
[forms]![frmillards Current TB]![txtFOB] AS FOB, [forms]![frmillards
Current TB]![txtDiv] AS Division, [forms]![frmillards Current
TB]![txtOrderNo] AS OrderNo, [forms]![frmillards Current TB]![txtRemarks]
AS Rem, fosusername() AS [Entered By]
FROM [Dillards Current TB]
WHERE ((([Dillards Current TB].[LINE#])=[forms]![frmillards Current
TB]![LineNo]));


There are no errors.

These are not part of any key fields.

 

[Jerry Whittle]

For testing I'd try a couple of things.

1. Replace all the selects to the form with Null. instead of
[forms]![frmIllards Current TB]![txtARCode] AS Reason,
put Null,

If that works, wrap them with an IIf statement. Try putting in a Null as the
second argument or a bogus amount.

IIF(isnull([forms]![frmIllards Current TB]![txtARCode]),
Null,([forms]![frmIllards Current TB]![txtARCode]) AS Reason

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here is the SQL

INSERT INTO [Dillards Journal Entries] ( [Date], [Line #], [Store #], [Ref
#], Debit, Net, Reason, FOB, Div, OrderNo, Remarks, [Entered By] )
SELECT [Dillards Current TB].[REF DTE], [Dillards Current TB].[LINE#],
[Dillards Current TB].STORE, [Dillards Current TB].[REF#], [Dillards Current
TB].Amount AS [Debit Amt], [Dillards Current TB].Amount AS Net,
[forms]![frmIllards Current TB]![txtARCode] AS Reason,
[forms]![frmillards Current TB]![txtFOB] AS FOB, [forms]![frmillards
Current TB]![txtDiv] AS Division, [forms]![frmillards Current
TB]![txtOrderNo] AS OrderNo, [forms]![frmillards Current TB]![txtRemarks]
AS Rem, fosusername() AS [Entered By]
FROM [Dillards Current TB]
WHERE ((([Dillards Current TB].[LINE#])=[forms]![frmillards Current
TB]![LineNo]));


There are no errors.

These are not part of any key fields.
--
James Gaylord
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf

1. Show use the SQL.

2. Any error messages?

3. Are any or all of those fields part of a primary key or unique index?

 

[Cyberwolf]

Worked like a charm. Thanks a lot!!!!!!!!!
--
James Gaylord
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf

For testing I'd try a couple of things.

1. Replace all the selects to the form with Null. instead of
[forms]![frmIllards Current TB]![txtARCode] AS Reason,
put Null,

If that works, wrap them with an IIf statement. Try putting in a Null as the
second argument or a bogus amount.

IIF(isnull([forms]![frmIllards Current TB]![txtARCode]),
Null,([forms]![frmIllards Current TB]![txtARCode]) AS Reason

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here is the SQL

INSERT INTO [Dillards Journal Entries] ( [Date], [Line #], [Store #], [Ref
#], Debit, Net, Reason, FOB, Div, OrderNo, Remarks, [Entered By] )
SELECT [Dillards Current TB].[REF DTE], [Dillards Current TB].[LINE#],
[Dillards Current TB].STORE, [Dillards Current TB].[REF#], [Dillards Current
TB].Amount AS [Debit Amt], [Dillards Current TB].Amount AS Net,
[forms]![frmIllards Current TB]![txtARCode] AS Reason,
[forms]![frmillards Current TB]![txtFOB] AS FOB, [forms]![frmillards
Current TB]![txtDiv] AS Division, [forms]![frmillards Current
TB]![txtOrderNo] AS OrderNo, [forms]![frmillards Current TB]![txtRemarks]
AS Rem, fosusername() AS [Entered By]
FROM [Dillards Current TB]
WHERE ((([Dillards Current TB].[LINE#])=[forms]![frmillards Current
TB]![LineNo]));


There are no errors.

These are not part of any key fields.
--
James Gaylord
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf

1. Show use the SQL.

2. Any error messages?

3. Are any or all of those fields part of a primary key or unique index?
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a form with 4 unbound text boxes that get added to a table via an
append query. If I leave one of the fields blank, the record I am trying to
append does not. I have set the properties of the fileds in the table I am
appending to to allow null values, and not be required, but htis does not
help. It seems I need to have some sort of value to alow the record to be
appended. Is there a way to set it so the field can be null and still append?

Thanks,
--
James Gaylord
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf