Handling floating point with decimal comma separator to filter a form

[Sidnei Cordeiro]

How can I filter a form using a numeric field with the standard number
format "0.000,00" and when the number to apply filter is not interger?
Is there a method to convert a number in that format to the "0,000.00"? Or
another workaround is necessary?
I've tryied to pass the number as string, but this only works in a sql
statement, not with the ApplyFilter method of the DoCmd command.

I apprciate your support. Thank you


Sidnei B Cordeiro

 

[Allen Browne]

A different approach may work.

For example, say you have:
- unbound text boxes name txtMin and txtMax,
- the Format property of both set to Standard
(so Access treats them as a number)
- a form with a Number field named MyNum,
- a desire to filter this field so it lies between the 2 numbers
- a command button to apply the filter
you could use code like this in the Click event procedure:

Private Sub cmdFilter_Click()
Dim strWhere As String
If IsNumeric(Me.txtMin) And IsNumeric(Me.txtMax) Then
If Me.Dirty Then Me.Dirty = False 'save any edits
strWhere = "[MyNum] Between " & Me.txtMin & " And " & Me.txtMax
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
Else
MsgBox "Enter both numbers"
End If
End Sub

If that doesn't work out, print the filter string to the Immediate Window
(Ctrl+G), this filter string needs to in the standard US format, just like
the WHERE clause of a query. If that doesn't work, explicitly Format() the
numbers as you concatenate them into the string.

 

[Sidnei Cordeiro]

Sorry Allen, this is no solution for my problem.

Lets say the user wants to filter values between 1,1 and 1,9 (note that the
user wiil enter the number with a comma sparator, not with a dot - and the
Standard format will be of no help here). The string returned after he fills
the criteria (be it filled in a bound or an unbound field) will be "[MyNum]
Between 1,1 And 1,9". And this will result in a "syntax error (comma) in
query expression ...".

What I need is to pass 1.1 (1 dot 1) when the user enters 1,1 (1 comma 1,
that is what will always entered when we work here in Brazil, or in France
or in Holland).

But thanks for your effort


Sidnei

A different approach may work.

For example, say you have:
- unbound text boxes name txtMin and txtMax,
- the Format property of both set to Standard
(so Access treats them as a number)
- a form with a Number field named MyNum,
- a desire to filter this field so it lies between the 2 numbers
- a command button to apply the filter
you could use code like this in the Click event procedure:

Private Sub cmdFilter_Click()
Dim strWhere As String
If IsNumeric(Me.txtMin) And IsNumeric(Me.txtMax) Then
If Me.Dirty Then Me.Dirty = False 'save any edits
strWhere = "[MyNum] Between " & Me.txtMin & " And " & Me.txtMax
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
Else
MsgBox "Enter both numbers"
End If
End Sub

If that doesn't work out, print the filter string to the Immediate Window
(Ctrl+G), this filter string needs to in the standard US format, just like
the WHERE clause of a query. If that doesn't work, explicitly Format() the
numbers as you concatenate them into the string.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

How can I filter a form using a numeric field with the standard number
format "0.000,00" and when the number to apply filter is not interger?
Is there a method to convert a number in that format to the "0,000.00"?
Or another workaround is necessary?
I've tryied to pass the number as string, but this only works in a sql
statement, not with the ApplyFilter method of the DoCmd command

 

[Allen Browne]

You're right: it still uses the comma as separator.

Someone from Europe can probably tell you a much better way, but Replace()
should work, i.e.:
strWhere = "[MyNum] Between " & Replace(Me.txtMin & " And " &
Me.txtMax,",", ".")
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Sorry Allen, this is no solution for my problem.

Lets say the user wants to filter values between 1,1 and 1,9 (note that
the user wiil enter the number with a comma sparator, not with a dot - and
the Standard format will be of no help here). The string returned after he
fills the criteria (be it filled in a bound or an unbound field) will be
"[MyNum] Between 1,1 And 1,9". And this will result in a "syntax error
(comma) in query expression ...".

What I need is to pass 1.1 (1 dot 1) when the user enters 1,1 (1 comma 1,
that is what will always entered when we work here in Brazil, or in France
or in Holland).

But thanks for your effort


Sidnei

A different approach may work.

For example, say you have:
- unbound text boxes name txtMin and txtMax,
- the Format property of both set to Standard
(so Access treats them as a number)
- a form with a Number field named MyNum,
- a desire to filter this field so it lies between the 2 numbers
- a command button to apply the filter
you could use code like this in the Click event procedure:

Private Sub cmdFilter_Click()
Dim strWhere As String
If IsNumeric(Me.txtMin) And IsNumeric(Me.txtMax) Then
If Me.Dirty Then Me.Dirty = False 'save any edits
strWhere = "[MyNum] Between " & Me.txtMin & " And " & Me.txtMax
'Debug.Print strWhere
Me.Filter = strWhere
Me.FilterOn = True
Else
MsgBox "Enter both numbers"
End If
End Sub

If that doesn't work out, print the filter string to the Immediate Window
(Ctrl+G), this filter string needs to in the standard US format, just
like the WHERE clause of a query. If that doesn't work, explicitly
Format() the numbers as you concatenate them into the string.

How can I filter a form using a numeric field with the standard number
format "0.000,00" and when the number to apply filter is not interger?
Is there a method to convert a number in that format to the "0,000.00"?
Or another workaround is necessary?
I've tryied to pass the number as string, but this only works in a sql
statement, not with the ApplyFilter method of the DoCmd command