0

I am writing the code below to insert control values into a table 

currentdb.execute "insert into Table (AAA,AAB,AAC) values('"&me.Text1.value&"','"&me.text2.value&"','"&me.text3.value&")"

Sometimes one of these text boxes has no value. Is there a function I can use to write the statement in one sentence?

currentdb.execute "insert into Table (AAA,AAB,AAC) values('"&if(me.Text1.value is null,null,me.Text1.value)&"','"&if(me.text2.value is null, null,me.Text2.value)&"','"&if(me.text3.value is null, null,me.Texte.value)&")"

Because I dont want to construct many if else clause.

if isnull(me.Text1.value) then
...
else
...
end if
braX
  • 11,506
  • 5
  • 20
  • 33
davidzxc574
  • 471
  • 1
  • 8
  • 21

3 Answers3

0

Sounds like you're asking for the Nz Function

However, instead of writing the text box values into the INSERT statement text, you could execute a parameter query. And that query statement text need not change based on whether or not any of the text boxes contain Null.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strInsert As String
strInsert = "INSERT INTO YourTable (AAA, AAB, AAC) VALUES (p1, p2, p3);"
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strInsert)
qdf.Parameters("p1").Value = Me.Text1.Value
qdf.Parameters("p2").Value = Me.Text2.Value
qdf.Parameters("p3").Value = Me.Text3.Value
qdf.Execute dbFailOnError
Debug.Print "rows added: " & db.RecordsAffected
HansUp
  • 95,961
  • 11
  • 77
  • 135
0

Though parameters are neat, the direct answer to your question is my function CSql.

Gustav
  • 53,498
  • 7
  • 29
  • 55
0

Try this - it will insert empty string into table if TextBoxes have no value:

CurrentDb.Execute "INSERT INTO Table (AAA,AAB,AAC) " & _
"VALUES ('" & Nz(Me.Text1.Value, '') & "','" & _
Nz(Me.Text2.Value, '') & " ','" & Nz(Me.Text3.Value, '') & ")"
Vlado
  • 839
  • 6
  • 16