Re: How do I set the default value so that it includes all values?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

My programming experience in Acess is very limited. Is there a way to do it
w/o writing in SQL or Visual Basic? I can probably do a little VB, but not
SQL as far as I know.

"Powderfinger" wrote:


"sept2006" <sept2006@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8856CA90-1089-4038-A8A1-8E380D002B3E@xxxxxxxxxxxxxxxx
I am using a form to enter criteria values in a query. I would like to
make
it so that if I leave a field blank, it will not filter out those values.
How do I do this? I referenced "using parameters with queries and
reports"
when designing my form, so hopefully that is something you can look at if
you
need reference.

Here's how I do it, but there's probably a better way:

Dim strFilter As String

Dim stsLNAMEFilter As String
Dim strFNAMEFilter As String
Dim strCIDFilter As String
Dim strDateFilter As String


If Len(Trim(Me.LName)) > 0 Then strLastNameFilter = "[LNAME] Like '" &
Me.LName & "*'"
If Len(Trim(Me.FName)) > 0 Then strFirstNameFilter = "[FNAME] = '" &
Me.FName & "'"
If IsNumeric(Me.CID) Then strCIDFilter = "[CID] = '" & Me.CID

If IsDate(Me.StartDate) And IsDate(Me.EndDate) then strDateFilter = "[IDate]
Between #" & Format$(Me.StartDate, "mm/dd/yyyy") & "# AND #" &
Format$(Me.EndDate, "mm/dd/yyyy") & "#"

' now that I have all the filters I connect them with an AND:

If Len(Trim(strLastNameFilter)) > 0 Then strFilter = strLastNameFilter
If Len(Trim(strFirstNameFilter)) > 0 Then strFilter = strFilter & " " &
"AND" & " " & strFirstNameFilter
If Len(Trim(strCIDFilter )) > 0 Then strFilter = strFilter & " " & "AND" &
" " & strCIDFilter
If Len(Trim(strDateFilter )) > 0 Then strFilter = strFilter & " " & "AND" &
" " & strDateFilter


' now I have to get rid of the "AND" if it is the first three characters of
the filter

If Left(strFilter , 3) = "AND" Then
strFilter = Right(strFilter , Len(strFilter ) - 3)
End If

We have our filter with just what we want.



.

Relevant Pages

  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • data access page search code
    ... Dim SerVal As String 'Value to search against ... Dim strWhere As String 'The filter to use ... Dim strPrompt As String 'MsgBox prompt to use ...
    (microsoft.public.access.modulesdaovba)
  • RE: Set Filter from Startup form to another form
    ... Private Sub Form_Load ... Dim strPrg As String ... Private Function StartProgramAs String ... Now all that is necessary is to set the filter for each form where you want ...
    (microsoft.public.access.formscoding)
  • Re: Performance von SQL-Abfragen auf Exceltabellen
    ... Nun habe ich es mit SQL auf Exceldaten versucht und konnte das ganze ... Performance konstant bei rund 3 Sekunden. ... Dim iColumnCount As Long ... Dim FirstColumnforSQLexists_jn As String 'j if first column for SQL ...
    (microsoft.public.de.excel)