Using "Like operator" on fields of type currency

[GraeagleBill]

In filtering the recordsource from code by setting a filter expression and enabling filtering, I'm having problems when the filter makes reference to a field of data type currency. Two examples:

1) Debit Like "*.20*" No records found

2) Debit Like "*.2*" 35 records found where the currency value ended with ".20"

Perhaps the somewhat generalized search function I wrote needs to examine the data type of the field upon which the filter is being applied, but even if so that still leaves me not knowing how the filter expression should be created/formatted. At first I thought the use of "%" needed to be used instead of "*" but unless I completely missed how to use the "%" I'm still in the dark here.

Suggestions?

Thanks,
Bill

[orange]

Bill,

I think the LIKE only works with text/strings.

[Rainlover]

To add criteria to an Access query, open the query in Design view and identify the fields (columns) you want to specify criteria for.

To include records that use Like try.

Like "*4.99"

This should return 4.99, $4.99, $14.99, $24.99 and so on.

[orange]

After a little more investigation, I found that you can use like with a numerical value provided that
you first create a string/text variable.

If you take the numeric field and concatenate a zero length ("") or empty string(" ") to it, you can use "like" with the new variable.

I found the code below to work in one of my queries.

SELECT Cabinet.Quantity, Cabinet.Cost, Cabinet.Component
, Cabinet.CabinetCode, [Cost] & "" AS A
FROM Cabinet
WHERE ((([Cost] & "") Like "2*"));

Table layout and some values.



Query result.

[GraeagleBill]

Orange is right about the Like being restricted to data types strings and numeric. With that thought in mind, it was fairly straight forward to resolve the issue. Of the six (6) columns/fields subject to sorting and searches, only two are bound to query fields with data type of currency. I simply added two new query fields and let SQL do the work.

For table field "Credit" I added "strCredit: FormatCurrency(Credit)" to the query def.
For table field "Debit" I added "strDebit: FormatCurrency(Debit)" to the query def.

Correspondingly, the related controls Event properties make reference the function that does the work. E.g., =CommonFindFNC("strCredit")

Thanks Orange, you got me going in the right direction.
Bill

[GraeagleBill]

I didn't try it, but if I add code to the "common" search function I use, I could drop use of the trailing "*" when the search argument is of the form ".nn" AND the data type of the field being searched is currency. The solution I posted a bit ago solved the problem without any changes to my search function code so I settled for that and now moving on.

Thanks for your thoughts,
Bill

[Rainlover]

In post number 3, I gave an example of using "Like" on a Currency Field.

This appears to be ignored.

Have I misunderstood the question or what. Can someone clarify where I am going wrong.

Cheers.

[GraeagleBill]

Sorry Rainlover. Didn't mean to leave you hanging regarding your suggestion, it was a matter of how my "find function" is being applied to the app. Were I to use your suggestion, I would have had to add code to my "find function" to examine the data-type of the field involved and modify the query accordingly as the function was applied to a mixture of data-types. By applying the solution described in post #5 there was no need to modify any code at all.

I do very much appreciate everyone that give their time and skills to my posts.
Bill

[Rainlover]

Thanks Bill.

It appears that you can use "Like" but not in your situation.

[GraeagleBill]

Maybe posting a bit of the code here would help to clarify how the "Like" is applied?

Public Function CommonFindRtn(FindField As String)
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' The column heading label controls have invocations of this routine in their "on
' double-click" events where one wants to search for entries within the column
' that INCLUDE the text/value entered in the "tbFindPane", which is made visible
' upon entry into this function.
'=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Me.tbFindPane.Visible = True
Me.lblSearchPrompt.Visible = True
Me.lblEndSearching.Visible = True
Me.tbFindPane = Null
Me.tbFindPane.SetFocus
SearchColumn = FindField

End Function


Private Sub tbFindPane_AfterUpdate()

If Not IsNull(tbFindPane) Then
    Me.Filter = SearchColumn & " Like ""*" & Me.tbFindPane & "*"""
    Me.FilterOn = True
    Me.Requery
End If

End Sub

Each of the column headings in the current app's transaction register form have double-click events making reference to the "common" find function. As you can see from the code, the function is expecting that field associated with the column is to be treated as "String". Having simply added a conversion of the two currency fields within the form's RecordSource query makes it easy for the double-click events for the currency fields to be passed as strings to the "common" find function.

I hope this helps,
Bill