Solved Allow zero length string property (1 Viewer)

Falcon88

Registered User.
Local time
Today, 17:37
Joined
Nov 4, 2014
Messages
297
Hii all

I use audit trail as the attached sample on some databases but i get read about allow zero length string property on text or memo datatype fields must be No, based on :
http://www.allenbrowne.com/bug-09.html

But when i changes that property to no , there a problem on the action query that play by code .
 

Attachments

  • AuditTrail.zip
    56.5 KB · Views: 88

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:37
Joined
May 21, 2018
Messages
8,463
can you explain how to make the problem happen?
 

Falcon88

Registered User.
Local time
Today, 17:37
Joined
Nov 4, 2014
Messages
297
When changes allow zero length property for text and memo fields , the append query that records that changes on that fields don't work .

See to the audit table on the attachment , and change allow zero length to "no" for memo and text fields and see what happen.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:37
Joined
May 21, 2018
Messages
8,463
I cannot recreate the problem, but here is why. You action queries will create an empty string '' if there is no value
Code:
strSQL = "INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, Field, Prev_Value, New_Value, [Action], Reason)"
             strSQL = strSQL & " SELECT " & cQUOTE & sUser & cQUOTE & ", " & cQUOTE & Now & cQUOTE & " , "
             strSQL = strSQL & cQUOTE & UniqID_Field & cQUOTE & ", " & cQUOTE & UniqID & cQUOTE & ", "
             strSQL = strSQL & cQUOTE & MyForm.Name & cQUOTE & ", " & cQUOTE & ctl.Name & cQUOTE & ", " & cQUOTE & ctl.OldValue & cQUOTE
             strSQL = strSQL & ", " & cQUOTE & nullval & cQUOTE & ", " & cQUOTE & action & cQUOTE & ", " & cQUOTE & gstrReason & cQUOTE & ";"

If I was doing it I would check if the value was null and enter the word NULL in the SQL string but your code tries to enter ''.

Example:
Code:
INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, [Action]) SELECT "Peter", "6/11/2022 1:21:31 PM" , "EmpID", "23", "frmEmployees", "*** New Record ***";
So my guess there are cases that one of those values is null somewhere in your code. Lets say you forget to assign the variable UniqID. Then you would get code like
Code:
INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, [Action]) SELECT "Peter", "6/11/2022 1:21:31 PM" , "EmpID", "", "frmEmployees", "*** New Record ***";
But you really would need is the word NULL without quotes and not "".
Code:
INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, [Action]) SELECT "Peter", "6/11/2022 1:21:31 PM" , "EmpID", NULL, "frmEmployees", "*** New Record ***";

You can rewrite your actions queries to do this. I use this function to do my delimiters.

So my query would look like
Code:
"INSERT INTO tblAudit ( [User], [DateTime], UniqID_Field, UniqID, Form, Field, Prev_Value, New_Value, [Action], Reason)"
             strSQL = strSQL & " SELECT " &csql(sUser)& ", " & csql(Now,sdt_text) & " , "
             strSQL = strSQL & csql(UniqID_Field) ", " & csql(UniqID,sdt_text) ", "
          ...

If a value is passed as a Null the CSQL returns NULL not ""
You can fix it using my CSQL function in your code, but I probably just leave it. This is an audit log and not a data table.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 10:37
Joined
May 21, 2018
Messages
8,463
Bottom line this is an audit trail and not a data table. So you are not doing things you would do in a data table. The ZLS argument is completely irrelevant here. Just know everything is saved as a string to include "empty" values. Previous value, New value, date are all saved as string. No big deal because it is for reporting. So you really should not worry about it.
In data tables it can be confusing because you do not know if there is a Null or a "" when you see an empty box. If you are doing queries then you would have to check " both is Null or = '' ". In an audit table, who cares. Just know every blank fields has "" not Null in it. I would not waste my time changing anything.
 

Users who are viewing this thread

Top Bottom