How do I format sql for number?

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • DAHMB
    New Member
    • Nov 2007
    • 147
    #1

    How do I format sql for number?

    I have the following code and it works with characters I want to change lines 9, 10 and 11 to work with a number. How do I format it to work?

    Code:
    Private Sub FilterOvertime_Click()
On Error Resume Next
    Dim strSQL As String, intCounter As Integer
    Dim c As Access.Control
    'Build SQL String
    For intCounter = 1 To 2
        If Me("Filter" & intCounter) <> "" Then
            Set c = Me("Filter" & intCounter)
            If TypeOf c Is Access.ComboBox Then
                 strSQL = strSQL & "[" & c.Tag & "] " & " = " & Chr(34) & c & Chr(34) & " And "
            ElseIf TypeOf c Is Access.TextBox Then
                strSQL = strSQL & "[" & c.Tag & "] " & " = " & Chr(35) & Format(c, "mm/dd/yyyy") & Chr(35) & " And "
            End If
        End If
    Next
    If strSQL <> "" Then
        'Strip Last " And "
        strSQL = Left(strSQL, (Len(strSQL) - 5))
        'Set the Filter property
        Reports!rptOverTime.Filter = strSQL
        Reports!rptOverTime.FilterOn = True
    End If
End Sub
    Tags: access
    • DAHMB
      New Member
      • Nov 2007
      • 147
      #2
      opps I ment I need to change just lines 9 and 10 to make it work
      Thanks

        Comment

        • Megalog
          Recognized Expert Contributor
          • Sep 2007
          • 378
          #3
          Try replacing line 10 with:
          Code:
          strSQL = strSQL & "[" & c.Tag & "] " & " = " & Iif(IsNumeric(c),c,Chr(34) & c & Chr(34)) & " And "

            Comment

            • DAHMB
              New Member
              • Nov 2007
              • 147
              #4
              That's it! Thanks

              I got it to work by adding mega code to line 10 and changing my code to read as follows:

              Code:
              Private Sub FilterOvertime_Click()
On Error Resume Next
    Dim strSQL As String, intCounter As Integer
    Dim c As Access.Control
    'Build SQL String
    For intCounter = 1 To 2
        If Me("Filter" & intCounter) <> "" Then
            Set c = Me("Filter" & intCounter)
            If c = [Filter1] Then
                 strSQL = strSQL & "[" & c.Tag & "] " & " = " & Chr(34) & c & Chr(34) & " And "
            ElseIf TypeOf c Is Access.TextBox Then
                strSQL = strSQL & "[" & c.Tag & "] " & " = " & Chr(35) & Format(c, "mm/dd/yyyy") & Chr(35) & " And "
            ElseIf c = [Filter2] Then
                strSQL = strSQL & "[" & c.Tag & "] " & " = " & IIf(IsNumeric(c), c, Chr(34) & c & Chr(34)) & " And "
            End If
        End If
    Next
    If strSQL <> "" Then
        'Strip Last " And "
        strSQL = Left(strSQL, (Len(strSQL) - 5))
        'Set the Filter property
        Reports!rptOverTime.Filter = strSQL
        Reports!rptOverTime.FilterOn = True
    End If
End Sub

                Comment

                • Megalog
                  Recognized Expert Contributor
                  • Sep 2007
                  • 378
                  #5
                  You can also remove those & " AND " statements from the end of your sql strings, and put them in the FRONT.. That way you dont have to strip off your trailing AND at the end of the routine.

                  So instead of:

                  Code:
                  strSQL = strSQL & "(new sql) " & " AND "
                  use:

                  Code:
                  strSQL = strSQL & " AND " & "(new sql)"

                    Comment

                    • Megalog
                      Recognized Expert Contributor
                      • Sep 2007
                      • 378
                      #6
                      (to expand on my last comment)

                      Code:
                      Private Sub FilterOvertime_Click()
On Error Resume Next
    Dim strSQL As String, strTemp as String, intCounter As Integer
    Dim c As Access.Control
    'Build SQL String
    For intCounter = 1 To 2
	  strTemp = ""
        If Me("Filter" & intCounter) <> "" Then
            Set c = Me("Filter" & intCounter)
            If c = [Filter1] Then
                strTemp = "[" & c.Tag & "] " & " = " & Chr(34) & c & Chr(34)
            ElseIf TypeOf c Is Access.TextBox Then
                strTemp = "[" & c.Tag & "] " & " = " & Chr(35) & Format(c, "mm/dd/yyyy") & Chr(35)
            ElseIf c = [Filter2] Then
                strTemp = "[" & c.Tag & "] " & " = " & IIf(IsNumeric(c), c, Chr(34) & c & Chr(34))
            End If
        End If
	    If strTemp <> "" then
		  If strSQL <> "" then
			strSQL = strSQL & " AND " & strTemp
		  Else
			strSQL = strTemp
		  End if
        End if
    Next
    If strSQL <> "" Then
        'Set the Filter property
        Reports!rptOverTime.Filter = strSQL
        Reports!rptOverTime.FilterOn = True
    End If
End Sub
                      Lines Changed:
                      3, 7, 11, 13, 15, 18-24

                        Comment

                        • DAHMB
                          New Member
                          • Nov 2007
                          • 147
                          #7
                          Thanks for taking the time on this, I really appreciate it. DOes the change you show increase performance or is it another way. I am trying to lrearn.
                          Thanks

                            Comment

                            • Megalog
                              Recognized Expert Contributor
                              • Sep 2007
                              • 378
                              #8
                              I doubt the changes I made would be noticeable, performance-wise. It's just another way of doing it. =)

                                Comment

                                Previous template Next
                                Working...