Insert empty numeric value

[hon123456]

Dear all,

In VB, I have three textbox which are amount1,amount2 and
amount3. After user enter
the value in the textbox, I will insert the value into Access table.
The table have three columns
amount1 , amount2 and amount3, and all are nummeric Type. However, if
the user do not enter
any value in textbox . The insert statement will become as follows:

Insert into table1 (amount1,amount2,amount3) values (,,) Then access
complain that
there is syntax error in insert statement. Does that mean I cannot
insert empty value for the
numeric value in access.? How to solve this problem. Thanks.

 

[Ken Sheridan]

Firstly the amount columns in the table must allow Nulls if you want them to
be empty rather than contain zeros. In Access this means the column's
Required properties must be False.

You'd build the SQL statement to include NULL if no value is entered into a
text box. In VBA in Access you can use the Nz function, but I don't think VB
supports this function. You should be able to use something along the
following lines, however:.

Dim strAmount1 As String
Dim strAmount2 As String
Dim strAmount3 As String
Dim strSQL As String

If IsNull(Amount1) Then
strAmount1 = "NULL"
Else
strAmount1 = Amount1
End If

If IsNull(Amount2) Then
strAmount2 = "NULL"
Else
strAmount2 = Amount2
End If

If IsNull(Amount3) Then
strAmount3 = "NULL"
Else
strAmount3 = Amount3
End If

strSQL = INSERT INTO Table1" & _
"(amount1,amount2,amount3) " & _
"VALUES(" & strAmount1 & _
"," & strAmount2 & "," & _
strAmount3 & ")"

''''code to execute strSQL statement goes here''''

Often its is preferable to avoid Nulls, however, particularly with numeric
or currency data and use a default value of zero. Nulls are not values but
the absence of a value and are therefore semantically ambiguous. Take the
example of a CreditLimit column in a Customers table. What would Null mean?
Zero credit? Unlimited credit? There is no way of knowing from the data
per se, it’s a matter of interpretation. The nearest one can get to a
meaning for Null is 'unknown'.

Ken Sheridan
Stafford, England

 

[Pieter Wijnen]

Try

Dim Db As DAO.Database
Dim Qdef As DAO.QueryDef
Dim i As long

Set Db = Access.CurrentDb
Set Qdef = Db.CreateQueryDef(VBA.vbNullString,VBA.vbNullString)
Qdef.SQL = "PARAMETERS pAmount1 Double, pAmount2 Double, pAmount3 Double;" &
VBA.vbcrlf & _
"INSERT INTO TABLE1 (Amount1, Amount2, Amount3)" & VBA.vbcrlf & _
"VALUES( pAmount1, pAmount2, pAmount3)"

For i = 1 To 3
Qdef.Parameters("pAmount" & i).Value = Me.Controls("Amount" & i).Value
Next

Qdef.Execute DAO.dbSeechanges
Set Qdef = Nothing
Set Db = Nothing

HtH

Pieter