Using decimal values in the where clause of a query

[odrap]

In one of my Access forms i use a groupbox with multiple radiobuttons, to make it possible for the user to build the where clause for his query.
Some of the options are used to build a where clause that must make it possible to search for invoices in a table with an invoice amount that is the same as the value the user entered in a textbox on the form. See the code here below:

Select Case Grp4
Case 1
With cboResultaat
.RowSource = _
"Select Factuurnr,Factuurdatum,Factuurbedrag, Saldo " & _
"From tblFacturatie Where Saldo = >0 And Factuurbedrag = " & Me!txtGezochtItem
Me!cboResultaat = Null
.Requery
Me!cboResultaat.Visible = True
.SetFocus
.Dropdown
End With
Case 2
With cboResultaat
.RowSource = _
"Select Factuurnr,Factuurdatum,Factuurbedrag, Saldo " & _
"From tblFacturatie Where Saldo = 0 And Factuurbedrag = " & Me!txtGezochtItem
Me!cboResultaat.Visible = True
Me!cboResultaat = Null
.Requery
.SetFocus
.Dropdown
End With

End Select
End Sub

When a whole number is entered in the textbox ( txtGezochtitem) and there are invoices with such an amount, this invoices will be found by the query, but when a decimal number is entered and there exists invoices with such an amount, the query can't find them !!!
Somewhere via internet i heard about a solution for this problem using a parameterquery. But how do you make the reference to the value of the textbox as parameter in such a query, and does this really solve the problem?

 

[ChrisO]

A bit of a guess here and I can’t fully test it at the moment.

Try replacing: -

And Factuurbedrag = " & Me!txtGezochtItem

With

And Factuurbedrag = " & Str(Me.txtGezochtItem)

You may be picking up a comma as the decimal separator from Dutch regional settings.
The Str() function will not use regional settings and will return a period instead.

Try to Debug.Print the SQL string and see what you get.

Remember, I did say it was a bit of a guess, but hope that helps.

Chris.

 

[WayneRyan]

odrap,

Also, floating numbers are tough to compare easily.

You might try:

"From tblFacturatie Where Saldo = > 0 And Abs(Factuurbedrag - " & Me!txtGezochtItem & ") < 0.01"


hth,
Wayne