vba search $ sign ($2000 - $3000)

[Astro]

Hello there,

i've a set of code to search through table containing column values like $2000 - $3000, $5000 - $6000 and so on.

If i were to enter something like $2000 - $5500, both results would be displayed.

unfortunately, the set of code works only when the values are without the $ sign.

Similarly, i have other values for [Age Group] which has values like 30 - 50 and etc. (without the $ sign) and... it works perfectly.

How can i alter the codes to accomodate the $ sign?

sMHouse = Me.TextMHouse
sMH = Split(sMHouse, " ")
 
sMHouse = "  and ( "
        
        Do While sMH(0) <= sMH(1)
            
            sMHouse = sMHouse & " ( [Monthly Household] like '%" & sMH(0) & "%' ) or "
            sMH(0) = sMH(0) + 1
        Loop
        
         sMHouse = Mid(sMHouse, 1, Len(sMHouse) - 5)
         
         sMHouse = sMHouse & " or [Monthly Household] not like '%" & sMH(0) & "%' or [Monthly Household] not like '%" & sMH(1) + 1 & "%') and [Monthly Household] is not null ) "

Thank You

Astro

[si_the_geek]

Is this a numeric field in Access?

If so, the data does not contain the $ sign (that is purely for display purposes), so a search containing it will not work - and you should remove the $ sign from your search criteria.

[Astro]

Hey si_the_geek

It is in text format because the application user ocasionally enters some text, her database is very inconsistent.

ive tried doing a replace but still doesn't work.

sMHouse = Me.TextMHouse
 
sMH = Split(sMHouse, " ")
 
sMHouse = "  and ( "
        
        Do While sMH(0) <= sMH(1)
            
            sMHouse = sMHouse & " ( Replace([Monthly Household],'$','') like '%" & sMH(0) & "%' ) or "
            sMH(0) = sMH(0) + 1
        Loop
        
         sMHouse = Mid(sMHouse, 1, Len(sMHouse) - 5)
         
         sMHouse = sMHouse & " or Replace([Monthly Household],'$','') not like '%" & sMH(0) & "%' or Replace([Monthly Household],'$','') not like '%" & sMH(1) + 1 & "%') and [Monthly Household] is not null ) "
MsgBox sMHouse

[si_the_geek]

I don't understand why you cant search for text including $, I've never had a problem with it before.

You put the Replace in completely the wrong place, you want to update in the Input (ie: sMH(0) and sMH(1)) rather than the fields.

[Astro]

Hey si_the_geek

sorry for the late reply. ill give it a shot again. thanks for the suggestion.

=)

Astro