I have an update query that runs off fields entered on a form. However, some of the fields can be left blank, how do i go about running the update query so that it runs only based on the fields that have been entered.
Currently I am using
[Forms]![frmName]![fieldName].value (i have this in four criteria fields)
it does not work when only two fields have been entered in the form, but when all four fields have been entered it works fine.
Well the reason it's not working is because you are passing NULL values to the query, and well, access doesn't like nulls.
you might want to consider using Iif statements in your query... Can't guarentee this will work - will need some clever consideration and testing.
Or you have to go at the problem from another angle...
Have you ever thought of building a dynamic SQL string in VBA and the opening it via a recordset? It's fairly difficult, but it does exactly what you want...
Dim SQL As String
SQL = "SELECT * FROM MyTable "
If x <> "" or NOT IsNull(x) Then
SQL = SQL & "WHERE x = y "