Compare between 2 age value (1 Viewer)

Andyleone

New member
Local time
Tomorrow, 03:34
Joined
Mar 27, 2014
Messages
7
Code:
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
 
Last edited by a moderator:

pr2-eugin

Super Moderator
Local time
Today, 19:34
Joined
Nov 30, 2011
Messages
8,494
What is the type of Age field ? Try the following..
Code:
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

New member
Local time
Tomorrow, 03:34
Joined
Mar 27, 2014
Messages
7
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

New member
Local time
Tomorrow, 03:34
Joined
Mar 27, 2014
Messages
7
Got it worked just re edit code and corrected data type in the field and it work..
 

Users who are viewing this thread

Top Bottom