CODEWITHSUNDEEP
vbams-access
Ade Adepoju
Ade Adepoju

Reputation: 11

Insert data into MS Access table via VBA inputbox

I am new to access and trying to insert dates into a table named "Data l Business Date" using the input box functionality. Below is my code but i keep getting syntax error with insert into statement.

Function BusinessDate()

Dim StrSQL As String
StrSQL = ("INSERT INTO Data l Business Date ([T_Date]) SELECT '" & InputBox("Enter todays date") & "';")
DoCmd.RunSQL StrSQL

End Function

Upvotes: 1

Views: 1701

Answers (2)

Gustav
Gustav

Reputation: 55816

You must validate and format the date value:

Function BusinessDate()

    Dim StrSQL As String
    Dim InputText As String
    Dim InputDate As Date
   
    InputText = InputBox("Enter todays date")
    If IsDate(InputText) Then
        InputDate = DateValue(InputText)
        StrSQL = "INSERT INTO [Data l Business Date] ([T_Date]) VALUES (#" & Format(InputDate, "yyyy\/mm\/dd") & "#);"
        CurrentDb.Execute StrSQL
    End If

End Function

Upvotes: 2

Storax
Storax

Reputation: 12167

In access you should use somedthing like

"INSERT INTO tblName (fldName) VALUES ( '" & InputBox("Enter todays date") & "' )"

Your function should look like

Function BusinessDate()

Dim StrSQL As String
StrSQL = "INSERT INTO tblName (fldName) VALUES ( '" & InputBox("Enter todays date") & "' )"
DoCmd.RunSQL StrSQL

End Function

Further reading Insert Into

PS If you have a space in the tablename, you need to use [ ...], i.e.

"INSERT INTO [tbl name] (fldName) VALUES ( '" & InputBox("Enter todays date") & "' )"

Upvotes: 0

Related Questions