Compare between 2 age value

[Andyleone]

Private Function BuildFilter() As Variant
     Dim varWhere As Variant
     Dim tmp As String
     tmp = """"
 
     varWhere = Null
     
     If Me.TxtGender2 > "" Then
      varWhere = varWhere & "[Gender] like " & tmp & Me.TxtGender2 & tmp & " AND "
      End If
      
     If Me.TxtICD2 > "" Then
      varWhere = varWhere & "[ICD] like " & tmp & Me.TxtICD2 & tmp & " AND "
      End If
      
      If Me.TxtAgeFrom2 > "" Then
        varWhere = varWhere & "[Age] >= " & tmp & Me.TxtAgeFrom2 & tmp & " AND "
      End If
          
      If Me.TxtAgeTo2 > "" Then
        varWhere = varWhere & "[Age] <= " & tmp & Me.TxtAgeTo2 & tmp & " AND "
      End If
             
      If Me.TxtDateFrom2 > "" Then
        varWhere = varWhere & "([Capture_Date_OPD] >= #" & Format(Me.TxtDateFrom2, "mm/dd/yy") & "#) AND "
      End If
      
      If Me.TxtDateTo2 > "" Then
        varWhere = varWhere & "([Capture_Date_OPD] <= #" & Format(Me.TxtDateTo2, "mm/dd/yy") & "#) AND "
      End If
      
      If IsNull(varWhere) Then
          varWhere = ""
      Else
          varWhere = "WHERE " & varWhere
          
          If Right(varWhere, 5) = " AND " Then
              varWhere = Left(varWhere, Len(varWhere) - 5)
          End If
      End If
      BuildFilter = varWhere

could anyone help me on age part. The purpose of age part is to display filtered value between two age eg. 1 to 5 year old. I Already make search on forum and try suggested code but still got error. Any advice?. thank on Advance

 

[pr2-eugin]

What is the type of Age field ? Try the following..

Private Function BuildFilter() As String
    Dim varWhere As Variant
    Dim tmp As String
    tmp = Chr(34)

    varWhere = Null

    If Len(Me.TxtGender2 & vbNullString) <> 0 Then
        varWhere = varWhere & "([Gender] = " & tmp & Me.TxtGender2 & tmp & ") AND "
    End If

    If Len(Me.TxtICD2 & vbNullString) <> 0 Then
        varWhere = varWhere & "([ICD] = " & tmp & Me.TxtICD2 & tmp & ") AND "
    End If

    If Len(Me.TxtAgeFrom2 & vbNullString) <> 0 Then
        varWhere = varWhere & "([Age] >= " & Me.TxtAgeFrom2 & ") AND "
    End If

    If Len(Me.TxtAgeTo2 & vbNullString) <> 0 Then
        varWhere = varWhere & "([Age] <= " & Me.TxtAgeTo2 & ") AND "
    End If

    If Len(Me.TxtDateFrom2 & vbNullString) <> 0 Then
        varWhere = varWhere & "([Capture_Date_OPD] >= " & Format(Me.TxtDateFrom2, "\#mm\/dd\/yyyy\#") & ") AND "
    End If

    If Len(Me.TxtDateTo2 & vbNullString) <> 0 Then
        varWhere = varWhere & "([Capture_Date_OPD] <= " & Format(Me.TxtDateTo2, "\#mm\/dd\/yyyy\#") & ") AND "
    End If

    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    BuildFilter = varWhere
End Function

 

[Andyleone]

If Me.TxtAgeFrom2 > "" Then
varWhere = varWhere & "[Age] >= " & tmp & Me.TxtAgeFrom2 & tmp & " AND "
End If

If Me.TxtAgeTo2 > "" Then
varWhere = varWhere & "[Age] <= " & tmp & Me.TxtAgeTo2 & tmp & " AND "
End If

For this field it was number..

 

[Andyleone]

Got it worked just re edit code and corrected data type in the field and it work..