Re: Handling floating point with decimal comma separator to filter a form

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Sidnei Cordeiro" <perspectiva@xxxxxxxxxxx> wrote in message
news:OQJiRgA8IHA.4928@xxxxxxxxxxxxxxxxxxxxxxx
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

"Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx> escreveu na mensagem news:eE3q12s7IHA.5052@xxxxxxxxxxxxxxxxxxxxxxx
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" <perspectiva@xxxxxxxxxxx> wrote in message
news:exphlTo7IHA.2544@xxxxxxxxxxxxxxxxxxxxxxx
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

.

Loading