Null field in append query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,
 
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?
 
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]![frm:DIllards Current TB]![txtARCode] AS Reason,
[forms]![frm:Dillards Current TB]![txtFOB] AS FOB, [forms]![frm:Dillards
Current TB]![txtDiv] AS Division, [forms]![frm:Dillards Current
TB]![txtOrderNo] AS OrderNo, [forms]![frm:Dillards Current TB]![txtRemarks]
AS Rem, fosusername() AS [Entered By]
FROM [Dillards Current TB]
WHERE ((([Dillards Current TB].[LINE#])=[forms]![frm:Dillards Current
TB]![LineNo]));


There are no errors.

These are not part of any key fields.
 
For testing I'd try a couple of things.

1. Replace all the selects to the form with Null. instead of
[forms]![frm:DIllards 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]![frm:DIllards Current TB]![txtARCode]),
Null,([forms]![frm:DIllards Current TB]![txtARCode]) AS Reason

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


Cyberwolf said:
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]![frm:DIllards Current TB]![txtARCode] AS Reason,
[forms]![frm:Dillards Current TB]![txtFOB] AS FOB, [forms]![frm:Dillards
Current TB]![txtDiv] AS Division, [forms]![frm:Dillards Current
TB]![txtOrderNo] AS OrderNo, [forms]![frm:Dillards Current TB]![txtRemarks]
AS Rem, fosusername() AS [Entered By]
FROM [Dillards Current TB]
WHERE ((([Dillards Current TB].[LINE#])=[forms]![frm:Dillards 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


Jerry Whittle said:
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?
Click to expand...
Click to expand...
 
Worked like a charm. Thanks a lot!!!!!!!!!
--
James Gaylord
Finder of Paths, Hunter of Prey
Ghost of the Night, Shadow of Day
The Wolf


Jerry Whittle said:
For testing I'd try a couple of things.

1. Replace all the selects to the form with Null. instead of
[forms]![frm:DIllards 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]![frm:DIllards Current TB]![txtARCode]),
Null,([forms]![frm:DIllards Current TB]![txtARCode]) AS Reason

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


Cyberwolf said:
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]![frm:DIllards Current TB]![txtARCode] AS Reason,
[forms]![frm:Dillards Current TB]![txtFOB] AS FOB, [forms]![frm:Dillards
Current TB]![txtDiv] AS Division, [forms]![frm:Dillards Current
TB]![txtOrderNo] AS OrderNo, [forms]![frm:Dillards Current TB]![txtRemarks]
AS Rem, fosusername() AS [Entered By]
FROM [Dillards Current TB]
WHERE ((([Dillards Current TB].[LINE#])=[forms]![frm:Dillards 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


Jerry Whittle said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top