StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>An empty string is not the same thing as Null. When you declare a String variable such as this:</p> <pre><code>Dim strWhere As String </code></pre> <p>strWhere is initialized as an empty string (or "zero length string"). That value is sometimes referred to as a null string, and there is even a VBA constant, <em>vbNullString</em>, which represents the empty string. However, regardless of which name you use, the empty string variable is not Null. Furthermore a VBA String variable can <strong>never</strong> be Null. For example, this code will cause error 94, 'Invalid use of Null':</p> <pre><code>Dim strWhere As String strWhere = Null </code></pre> <p>The reason I am emphasizing this point is because your code tests whether strWhere is Null. That is a logic flaw because strWhere will never be Null. For example, I don't believe this condition can ever be True:</p> <pre><code>If IsNull(strWhere) Then </code></pre> <p>If you want a test to determine when strWhere has not had a value assigned to it (it's still an empty string), use the Len function:</p> <pre><code>If Len(strWhere) = 0 Then </code></pre> <p>Here is a different approach for BuildQuery. It assumes the data type for your [Date] field is String (as your original code suggests). If [Date] is actually Date/Time data type, this code will not work. Also, please note that Date is a reserved word (see <a href="http://allenbrowne.com/AppIssueBadWord.html#D" rel="nofollow noreferrer">Problem names and reserved words in Access</a>). I enclosed the field name in square brackets to avoid ambiguity. If it were my own database, I would change the field name instead. </p> <pre><code>Private Sub BuildQuery() '* Update subform RecordSource based on input from *' '* the user on the Management tab. *' Dim strSql As String ' Main SQL SELECT statement ' Dim strWhere As String ' Optional WHERE clause ' Dim i As Integer Dim strControl As String Dim strField As String strSql = "SELECT * FROM tblOpenJobs AS oj" strWhere = vbNullString For i = 1 To 3 Select Case i Case 1 strControl = "cboShift" strField = "Shift" Case 2 strControl = "cboDepartment" strField = "Department" Case 3 strControl = "txtDate" strField = "[Date]" End Select If Not IsNull(Me.Controls(strControl).Value) Then strWhere = strWhere & _ IIf(Len(strWhere) > 0, " AND ", "") & _ "oj." & strField & " = '" & _ Me.Controls(strControl).Value & "'" End If Next i If Len(strWhere) > 0 Then strSql = strSql & " WHERE " & strWhere End If '* use the name of the subform CONTROL for sfrmJobs *' '* (may not be the name of the subform) *' Me.sfrmJobs.Form.RecordSource = strSql End Sub </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload