469,071 Members | 1,904 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,071 developers. It's quick & easy.

How do I format sql for number?

147 100+
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?

Expand|Select|Wrap|Line Numbers
  1. Private Sub FilterOvertime_Click()
  2. On Error Resume Next
  3.     Dim strSQL As String, intCounter As Integer
  4.     Dim c As Access.Control
  5.     'Build SQL String
  6.     For intCounter = 1 To 2
  7.         If Me("Filter" & intCounter) <> "" Then
  8.             Set c = Me("Filter" & intCounter)
  9.             If TypeOf c Is Access.ComboBox Then
  10.                  strSQL = strSQL & "[" & c.Tag & "] " & " = " & Chr(34) & c & Chr(34) & " And "
  11.             ElseIf TypeOf c Is Access.TextBox Then
  12.                 strSQL = strSQL & "[" & c.Tag & "] " & " = " & Chr(35) & Format(c, "mm/dd/yyyy") & Chr(35) & " And "
  13.             End If
  14.         End If
  15.     Next
  16.     If strSQL <> "" Then
  17.         'Strip Last " And "
  18.         strSQL = Left(strSQL, (Len(strSQL) - 5))
  19.         'Set the Filter property
  20.         Reports!rptOverTime.Filter = strSQL
  21.         Reports!rptOverTime.FilterOn = True
  22.     End If
  23. End Sub
Mar 3 '09 #1
7 3126
DAHMB
147 100+
opps I ment I need to change just lines 9 and 10 to make it work
Thanks
Mar 3 '09 #2
Megalog
378 Expert 256MB
Try replacing line 10 with:
Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL & "[" & c.Tag & "] " & " = " & Iif(IsNumeric(c),c,Chr(34) & c & Chr(34)) & " And "
Mar 3 '09 #3
DAHMB
147 100+
I got it to work by adding mega code to line 10 and changing my code to read as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub FilterOvertime_Click()
  2. On Error Resume Next
  3.     Dim strSQL As String, intCounter As Integer
  4.     Dim c As Access.Control
  5.     'Build SQL String
  6.     For intCounter = 1 To 2
  7.         If Me("Filter" & intCounter) <> "" Then
  8.             Set c = Me("Filter" & intCounter)
  9.             If c = [Filter1] Then
  10.                  strSQL = strSQL & "[" & c.Tag & "] " & " = " & Chr(34) & c & Chr(34) & " And "
  11.             ElseIf TypeOf c Is Access.TextBox Then
  12.                 strSQL = strSQL & "[" & c.Tag & "] " & " = " & Chr(35) & Format(c, "mm/dd/yyyy") & Chr(35) & " And "
  13.             ElseIf c = [Filter2] Then
  14.                 strSQL = strSQL & "[" & c.Tag & "] " & " = " & IIf(IsNumeric(c), c, Chr(34) & c & Chr(34)) & " And "
  15.             End If
  16.         End If
  17.     Next
  18.     If strSQL <> "" Then
  19.         'Strip Last " And "
  20.         strSQL = Left(strSQL, (Len(strSQL) - 5))
  21.         'Set the Filter property
  22.         Reports!rptOverTime.Filter = strSQL
  23.         Reports!rptOverTime.FilterOn = True
  24.     End If
  25. End Sub
Mar 3 '09 #4
Megalog
378 Expert 256MB
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:

Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL & "(new sql) " & " AND "
use:

Expand|Select|Wrap|Line Numbers
  1. strSQL = strSQL & " AND " & "(new sql)"
Mar 3 '09 #5
Megalog
378 Expert 256MB
(to expand on my last comment)

Expand|Select|Wrap|Line Numbers
  1. Private Sub FilterOvertime_Click()
  2. On Error Resume Next
  3.     Dim strSQL As String, strTemp as String, intCounter As Integer
  4.     Dim c As Access.Control
  5.     'Build SQL String
  6.     For intCounter = 1 To 2
  7.       strTemp = ""
  8.         If Me("Filter" & intCounter) <> "" Then
  9.             Set c = Me("Filter" & intCounter)
  10.             If c = [Filter1] Then
  11.                 strTemp = "[" & c.Tag & "] " & " = " & Chr(34) & c & Chr(34)
  12.             ElseIf TypeOf c Is Access.TextBox Then
  13.                 strTemp = "[" & c.Tag & "] " & " = " & Chr(35) & Format(c, "mm/dd/yyyy") & Chr(35)
  14.             ElseIf c = [Filter2] Then
  15.                 strTemp = "[" & c.Tag & "] " & " = " & IIf(IsNumeric(c), c, Chr(34) & c & Chr(34))
  16.             End If
  17.         End If
  18.         If strTemp <> "" then
  19.           If strSQL <> "" then
  20.             strSQL = strSQL & " AND " & strTemp
  21.           Else
  22.             strSQL = strTemp
  23.           End if
  24.         End if
  25.     Next
  26.     If strSQL <> "" Then
  27.         'Set the Filter property
  28.         Reports!rptOverTime.Filter = strSQL
  29.         Reports!rptOverTime.FilterOn = True
  30.     End If
  31. End Sub
Lines Changed:
3, 7, 11, 13, 15, 18-24
Mar 3 '09 #6
DAHMB
147 100+
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
Mar 4 '09 #7
Megalog
378 Expert 256MB
I doubt the changes I made would be noticeable, performance-wise. It's just another way of doing it. =)
Mar 4 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by martin.mrazek | last post: by
3 posts views Thread by stevek | last post: by
1 post views Thread by renee_ld | last post: by
5 posts views Thread by Macca | last post: by
11 posts views Thread by Grumble | last post: by
11 posts views Thread by shsandeep | last post: by
6 posts views Thread by JFB | last post: by
7 posts views Thread by Michael Howes | last post: by
18 posts views Thread by Dirk Hagemann | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.