0

I am having some trouble figuring out a way to handle null values in a SQL query running in Access VBA.

In short, I am attempting to create a form that allows the end user to enter search values in a series of text and/or combo boxes. The form would progressively search using the box values in a query that populates a subform.

The ideal would be to have multiple WHERE clauses (if that is possible in this case) with an input box for each table field. As in, the user could type in a date and the subform would display everything on that date. They could then type in a usage code and subsequently filter down for only those usage codes on that date. All of the other boxes could be blank and it would ignore those boxes and only search for the ones with entered values.

The AfterUpdate property of the text box automatically runs the SQL search function and updates the subform. The box being used updates a public variant "ID" in this case and that is pasted into the SQL string. Example code is below.

strSQL = "SELECT Raw_Data.[ID], Raw_Data.[LogDate], Raw_Data.[TimeStart], Raw_Data.[TimeStop]," & _
                "Raw_Data.[Hrs],Raw_Data.[UsageCode],Raw_Data.[Serial]," & _
                "Raw_Data.[Complete],Raw_Data.[Reg] " & _
     "FROM Raw_Data " & _
     "WHERE Raw_Data.[ID] = " & ID

This, however, returns the following error when the ID field is left blank: "3075 Syntax error (missing operator) in query expression 'Raw_Data.[ID] = '." When a record ID is typed in, the function executes correctly and the correct record is displayed.

How could I get this to execute as envisioned without a complicated network of If statements to bypass blank text and or combo boxes? Or is that the only way to make this execute correctly?

I have tried to add additional parts to the WHERE statement, and it functions correctly as long as the associated boxes are filled in. Otherwise, similar errors are returned. I cut those from the code for simplicity and just used ID rather than additional filters, as I suspect that the problem is with how I am using Access VBA to pass the SQL query.

Any help would be much appreciated!

EDIT FOLLOWING CLOSE: Thank you for posting the related question. Because of my lack of understanding, I am having trouble seeing how the function in the related answer could solve my root problem.

The function in the related question gives an example function for how to concatenate input values into a SQL string. Would I use this function to pass in each value from the text and combo boxes on my form, then append that into the WHERE statement?

Something along the lines of

"WHERE Raw_Data.[ID] = " & ID_from_function_Csql

I apologize for my lack of understanding of SQL/Access, and thank you for taking the time to review my question and assist.

EDIT FOR CLOSE:

A solution was provided from user June7 in the comments. The search process outlined at http://allenbrowne.com/ser-62.html was precisely what addressed my question.

pflykyle
  • 3
  • 2
  • 2
    Review http://allenbrowne.com/ser-62.html – June7 May 10 '23 at 17:32
  • You would do `"WHERE Raw_Data.[ID] = " & CSql(ID)` ... but that wouldn't work, you would need to filter with `WHERE ... IS NULL`. – Andre May 10 '23 at 17:53
  • Thank you for the comment. Would "WHERE ... IS NULL" work in this case, as the fields in the table being searched are not null themselves? Only the text boxes for user input would be null. – pflykyle May 10 '23 at 18:00
  • Thank you for the comment. I will review the attached link thoroughly! – pflykyle May 10 '23 at 18:00
  • To answer question in your comment - no. This is addressed in Allen Browne article. – June7 May 10 '23 at 19:17
  • From reading the article you listed, it will solve my problem exactly! Thank you very much for your assistance with this. – pflykyle May 10 '23 at 19:22
  • use NZ? https://stackoverflow.com/questions/247858/coalesce-alternative-in-access-sql – xQbert May 10 '23 at 20:46

0 Answers0