Ignore Null Values in function

[Guest]

Thanks for taking the time to read my question.

I want to create a function that filters and sorts a datasheet form. The
user will choose values from combo boxes and type in values (Dates) into text
boxes. The user, however, may not want to use all of the available options,
and so leave some blank.

Is there a way that I can program my function to allow it to ignore the null
values and only use the populated fields to filter and sort?

IgnoreIfNull or something like that?


example:

Start Date: 10-12-04
End Date: 12-12-04
Customer Name: Bill F
Product Name: (null)

Click button to Filter Datasheet Form on all non null fields

If this is not clear enough please let me know and I'll try to explain
further.

Thanks again,

Brad

 

[Matthias Klaey]

Thanks for taking the time to read my question.

I want to create a function that filters and sorts a datasheet form. The
user will choose values from combo boxes and type in values (Dates) into text
boxes. The user, however, may not want to use all of the available options,
and so leave some blank.

Is there a way that I can program my function to allow it to ignore the null
values and only use the populated fields to filter and sort?

IgnoreIfNull or something like that?


example:

Start Date: 10-12-04
End Date: 12-12-04
Customer Name: Bill F
Product Name: (null)

Click button to Filter Datasheet Form on all non null fields

[...]

You culd use the BuildCriteria function (see online help in VBA)
However, below is a function that is much more flexible and does
exactly what you want, for numeric, string and date types. Comments
are in German, but it should be pretty easy to understand.
"Von" = "From", "Bis" = "To"

There is some line wrapping in the code that you will have to correct.

HTH
Matthias Kläy

===Begin Code===
Public Function kBuildFilterBetween(strField As String, ByVal vVon As
Variant, ByVal vBis As Variant, Optional intType As Integer = dbLong)
As String
' (C) 2003-2004 Kläy Computing AG, Bern, Switzerland
' This code may be used freely for any purpose, but please acknowledge
' the origin properly.

' KCC Bauen eines Filters der Form
' strField & " >= " & strVon & " And " & strField & " <= " & strBis
' mit vernünftiger Interpretation der User Eingabe
' Von und Bis verkehrt - Filter = False
' intType = dbText -> wird als Text behandelt mit ' '
' = dbDate -> Datumsformat #mm/dd/yyyy#
' = alles andere: wird als Zahl behandelt ohne Quotes
'

' Example: kBuildFilterBetween("ARTNR", Me!VONNR, Me!BISNR, dbText)
' kBuildFilterBetween("YourDate", FromDate, ToDate, dbDate)

Dim strVon As String
Dim strBis As String

On Error GoTo kErrLabel

strVon = Trim$(Nz(vVon, vbNullString))
strBis = Trim$(Nz(vBis, vbNullString))

If strVon = vbNullString And strBis = vbNullString Then
kBuildFilterBetween = vbNullString ' vVon und vBis beide = ""
ElseIf strVon = vbNullString And strBis <> vbNullString Then
kBuildFilterBetween = strField & " <= " & kQuoteType(strBis,
intType)
ElseIf strVon <> vbNullString And strBis = vbNullString Then
kBuildFilterBetween = strField & " >= " & kQuoteType(strVon,
intType)
Else ' beide sind <> ""
If strVon > strBis Then
kBuildFilterBetween = "False"
Else
If strVon = strBis Then
kBuildFilterBetween = strField & " = " & kQuoteType(strVon,
intType)
Else
kBuildFilterBetween = strField & " Between " &
kQuoteType(strVon, intType) & " And " & kQuoteType(strBis, intType)
End If
End If
End If

Exit Function
kErrLabel:
Call MsgBox(Err.Number & ": " & Err.Description, , "Run Time Error")
End Function

Public Function kQuoteType(strText As String, intType As Integer) As
String

On Error GoTo kErrLabel

Select Case intType
Case dbText
kQuoteType = "'" & strText & "'"
Case dbDate
kQuoteType = kQueryDate(strText)
Case Else
kQuoteType = strText
End Select

Exit Function
Call MsgBox(Err.Number & ": " & Err.Description, , "Run Time Error")
End Function

Public Function kQueryDate(dDate As Variant) As String
' Liefert Datum in US-Format #mm/dd/yyyy# für Verwendung in SQL
Statements
' Wenn Datum = Null, kommt string "Null" zurück!

On Error GoTo kErrLabel

If IsNull(dDate) Then
kQueryDate = "Null"
Else
kQueryDate = "#" & Month(dDate) & "/" & Day(dDate) & "/" &
Year(dDate) & "#"
End If

Exit Function
kErrLabel:
Call MsgBox(Err.Number & ": " & Err.Description, , "Run Time Error")
End Function
===End Code===

 

[Allen Browne]

This example shows how to build up a filter string from only those boxes
where the user entered something.

Note the delimiters you need in your string:
# for dates;
" for text fields;
none for Number fields.

The dates are explicitly formatted as required for JET (the Access database
engine). The code then works in any country in the world. These text boxes
are unbound, so it is a good idea to set the Format property to Short Date
so that invalid dates are not accepted.

In the case of the text box for entering an ending date (txtEndDate), we ask
for less than the next day. If the field has any time component, it still
picks up the date that way.

Each one ends in AND, so they concatenate correctly, and then we have to
chop off the trailing AND. This makes it very easy to add more if you need
them, because they are all alike.

It ends up something like this:

Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDate] >= "& Format(Me.txtStartDate,
strcConJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDate] < "& Format(Me.txtEndDate + 1,
strcConJetDate) & ") AND "
End If

If Not IsNull(Me.txtCustomer) Then
strWhere = strWhere & "([CustomerName] = """ & Me.txtCustomer & """)
AND "
End If
'etc for other boxes

lngLen =Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria"
Else
If Me.Dirty Then 'Save before filter.
Me.Dirty = False
End If
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

 

[Guest]

Thanks very much to both of you for your help. I followed the code, and I
understand it.

Thanks so much!

Merry Christmas.

Brad