That's an interesting issue, Jack.
The core of the problem is that the SQL strings are invalid: you cannot
assign a zero-length string to a Number type field, nor to a Date/Time
field. Consequently, the insert *should* fail. Instead, Access assigns what
it can from the insert anyway, and then reports that it failed. Clearly that
result is inconsistent: in my view, the error is that JET is allowing the
invalid string to partially execute, when the append should completely fail.
To avoid the problem, use the dbFailOnError switch, which blocks the invalid
append, and so the RecordsAffected is correctly returned as zero:
Dim dbMyData As Database
Dim strSQL As String
Set dbMyData = CurrentDb
strSQL = "INSERT INTO tblExample (KeyField, TextField) VALUES ('test1',
'');"
dbMyData.Execute strSQL, dbFailOnError 'Insert succeeds
Debug.Print "test1 records affected = " & dbMyData.RecordsAffected '
Reports 1
strSQL = "INSERT INTO tblExample (KeyField, Count) VALUES ('test2',
'');"
dbMyData.Execute strSQL, dbFailOnError 'Insert succeeds
Debug.Print "test2 records affected = " & dbMyData.RecordsAffected '
Reports 0
strSQL = "INSERT INTO tblExample (KeyField, RecvDate) VALUES ('test3',
'');"
dbMyData.Execute strSQL, dbFailOnError 'Insert succeeds
Debug.Print "test3 records affected = " & dbMyData.RecordsAffected
'Reports 0
To get that to run to completion, you will need to add:
On Error Resume Next
To correctly form the SQL strings, use Null instead of a zero-length string:
INSERT INTO tblExample (KeyField, TextField) VALUES ('test1', Null);
INSERT INTO tblExample (KeyField, RecvDate) VALUES ('test3', Null);
BTW, I would also encourage you to set the AllowZeroLength property of your
Text fields to No, so you do not have to constantly test for both a ZLS
(zero-length-string) and a Null, and so your users (who can't see the
difference) don't get confused. When you do, the first of your examples also
fails.
If you are not clear on the difference between null and a zls, see:
http://allenbrowne.com/casu-12.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Jack" <jn***@pcisysSPAM.net> wrote in message
news:pa****************************@pcisysSPAM.net ...
It appears that RecordsAffected reports the wrong value
when a Null integer or date value is inserted using empty quotes.
The insert works fine, but RecordsAffected reports 0 instead of 1.
Here is the code...
--------
Dim dbMyData As Database
Dim strSQL As String
Set dbMyData = CurrentDb
strSQL = "INSERT INTO tblExample (KeyField, TextField) VALUES ('test1',
'');"
dbMyData.Execute (strSQL) 'Insert succeeds
MsgBox ("test1 records affected = " & dbMyData.RecordsAffected) ' Reports
1
strSQL = "INSERT INTO tblExample (KeyField, Count) VALUES ('test2', '');"
dbMyData.Execute (strSQL) 'Insert succeeds
MsgBox ("test2 records affected = " & dbMyData.RecordsAffected) ' Reports
0
strSQL = "INSERT INTO tblExample (KeyField, RecvDate) VALUES ('test3',
'');"
dbMyData.Execute (strSQL) 'Insert succeeds
MsgBox ("test3 records affected = " & dbMyData.RecordsAffected) 'Reports 0
--------
The database has 4 fields, and all except the KeyField allow empty
records.
I just wasted a whole day chasing this down. My data has a lot of null
values that will need to be stored in a variety of fields. If I have to,
I can replace the '' with NULL, but it'll be a real pain because I'll have
to examine the value of every field on every insert.
Why is RecordsAffected lying to me?
Thanks,
Jack