INSERT Query - number fields 1

[rsch]

I have written an INSERT query that will let me save a new record from an unbound form to a table.

My issue is that I have two number fields and the INSERT query will not work if they are empty. I have allowed for Zero Length on my text fields, but there doesn't seem to be such an option for number fields.

Any idea how I can solve this issue?

Thank you, in advance.

 

[mndrlion]

Make sure that required is set to "No" for the table field, and write "Null" to that field not an empty string.

 

[rsch]

mndrlion -
Thanks for the quick follow-up. I do have all the fields' Required value set to "No".

In my query, I am pulling from form values to populate the table. One of my number fields is Zip.

So, if I were just trying to save this one value, my query would be:
strSQL = "INSERT INTO tblClientFirm (Zip)" & _
" VALUES('" & me.txtZip & "');"

But, it won't let me do this. So, in this instance, I am trying to write an empty string, but am unsure how to programatically write "Null" to the field, instead.

 

[mndrlion]

Try this:

strSQL = "INSERT INTO tblClientFirm (Zip)" & _
" VALUES(" & _
IIf(IsNull(Me.txtZip), _
"Null", _
"'" & Me.txtZip & "'") & ");"

 

[rsch]

mndrlion - That is perfect!

I added the additional text fields into my query. For reference (anyone following this thread), here is my final result:
strSQL = "INSERT INTO ClientFirm (ClientFirm, Address1, Address2, City, State, Zip)" & _
" VALUES('" & Me.cboClientFirm.Value & "', '" & Me.txtClientAddress1 & "', '" & Me.txtClientAddress2 & "', '" & Me.txtClientCity & "', '" & Me!cboClientState.Column(0) & "'," & _
IIf(IsNull(Me.txtClientZip), _
"Null", _
"'" & Me.txtClientZip & "'") & ");"

Again, I cannot thank you enough. I've been banging my head against a table for a solution (not the best way to get an idea).

Ben