Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2005

    Exclamation Unanswered: Update Query Problem

    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.

  2. #2
    Join Date
    Jan 2007
    Provided Answers: 12
    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 "
    End If 
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts