Why does my insert query bomb?

[Jackvmae]

I have an insert query that adds a new row directly to a table. The query is triggered by code behind an "add new record" form.

One of the fields in the new record that the query is adding to the table is a date field. The insert query works perfectly so long as the new record that is inserted has data in the date field. But if the insert query has no data in the date field, the query bombs.

At the table, the date field is not specified as a required field, so I don't understand why the query bombs if the new record it is adding has an empty date field. Any ideas how to fix it so that the query does not bomb if the date field is empty?

Thanks,
Jack

 

[scriverb]

Post your SQL for the Append query. What value are you entering in the Append query for the date field? This is where I would start looking.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[PaulBricker]

It would help to know if you are getting an error message and what it is also. You should be able to Append a null field from the form, but you may need to requery the table before you see the results. You could use
Me.Requery
after your code to run the Append Query.

Paul

 

[Jackvmae]

My insert query is shown below. It is triggered by a command button on an unbound form. The purpose of the query is to insert a new record into a table.

strSQL = "INSERT INTO tblJournal (NameID, CompanyID, dateDoBy) VALUES ('" & Me.NameID & "', '" & Me.CompanyID & "','" & Me.dateDoBy & "');"


CurrentDb.Execute strSQL


As I said, it works perfectly, but if the date value is empty, it gives me a "missing operator" message.

Thanks,
Jack

 

[PaulBricker]

My suggestion would be to use an append query instead of an SQL string to do the work. You would create the append query and then on the criteria line for your three fields, you would have

=Forms!FormName!NameID
=Forms!FormName!CompanyID
=Forms!FormName!dateDoby

respectively. Then the click event in your button would be

DoCmd.OpenQuery "AppendQueryName"

Bob may have another suggestion.

Paul

 

[amorous]

Why cant you just condition your query. I mean something like this:

if Me.dateDoBy<>"" Then

strSQL = "INSERT INTO tblJournal (NameID, CompanyID, dateDoBy) VALUES ('" & Me.NameID & "', '" & Me.CompanyID & "','" & Me.dateDoBy & "');"

Else

strSQL = "INSERT INTO tblJournal (NameID, CompanyID) VALUES ('" & Me.NameID & "', '" & Me.CompanyID & "');"

End If

Hope that helps


VJ