Null values while Insert into Table

[ashok]

Hi,
I'm trying to insert values in a table. While inserting user not providing values for couple of entires.It showing an alert null values violation.

example:
Sql = "INSERT INTO table1(EXPID....) values ('" & Nz(txtRemarks.Value, "NA") & "'

Please help me out how to put some value incase user not enter values for it.

Thanks

 

[aqif]

What you are doing seems to be right. Why don't you try setting the Allow Zero Length=Yes property of all the fields you suspect will have null values. Then you will not even have to handle null value through NZ

 

[Pat Hartman]

If you are trying to refer to a form field, your syntax is incorrect:

Sql = "INSERT INTO table1(EXPID....) values ('" & Nz(Forms!frmYourForm!txtRemarks, "NA") & "'

You need to include the collection name and the form name and you may not use the property name. Within an SQL statement, you can only reference the default property of a control so you may not include the property name (.value) in the reference.

 

[ashok]

Hi,
Thanks for your responce. I'm trying to see " Allow Zero Length" property for a text box and failed to find it. Can you advice me is there any other name for this.

FYI : I'm seeing only "Auto correct" property. Mean while I set the in the table level " Required" property as "NO".

Please advice.

 

[aqif]

Allow Zero Length property resides at table level. Open the table in design view that you are trying to insert values into and then one by one click on all the fields that are mentioned in the Insert query and set the Allow Zero length properties to yes

 

[ashok]

Thank You. This property is availble only if the datatype is "text". How to handle Number and Date/time data type.

Thanks.

 

[Pat Hartman]

Zero Length STRING - numbers and dates are not strings.