help on string manipulation

[keybearer]

hi.. i need some help here.. i have an unbound combo box with values A-H, WAT, DET, etc.. In my table, there's a field named company with the values A to H or WAT or DET or any combination of both like A / H or Det / B.. etc..
currently, when i click on the values in the combobox, it will only display only the records with the precise value.. when i click A, only the records whose company is A shows up but not the ones with the values A / H.
I tried using the like operator, but it causes all my other combobox to stop working..

 

[Shadez]

hmm,

Do you need to do this in code or in a query?

Have you looked at the "Between" function.


ShadeZ

 

[keybearer]

I'm doing this in codes.. any ideas? i haven't tried using between.. isn't it used in sql only?

 

[dcx693]

The Like operator is probably the way to go, but what is your current code for findng matching records?

 

[Shadez]

You could try something along the lines of

dim chkChr as integer

chkChr = asc(ucase(left(YourTestField,1)))
if chkchr >=65 and chkchr <=72 then
       'first character is between A and H
end if

Hope this helps


ShadeZ

 

[keybearer]

This is my current code.. it uses the like operator but i can't get it working the way i want it..

strCoy = "Company Like " & Chr(34) & "*" & Forms![ReportPage]!CompanyCbx & " * " & Chr(34)

just for reference.. this is what the field looks like:

Company
A
A / H
WAT / A
DET / G
etc....

This is the row source of the combo box:

"";"A";"B";"C";"D";"E";"F";"G";"H";"BLN";"WAT";"DET";"INK"

 

[Shadez]

This is my current code.. it uses the like operator but i can't get it working the way i want it..

strCoy = "Company Like " & Chr(34) & "*" & Forms![ReportPage]!CompanyCbx & " * " & Chr(34)

Ok, i think i see where you are going wrong and its a simple mistake to make.

your line reads:-

Company like "*A * "

so your looking for something that has an "A" at the end of a word and the sentence must end in a space.

The code below( Note i removed the extra spaces around the second *.)

strCoy = "Company Like " & Chr(34) & "*" & Forms![ReportPage]!CompanyCbx & "*" & Chr(34)

will equate to :-

Company like "*A*"

So now you are searching for an "A" any where in your string.



Hope this helps


ShadeZ

 

[keybearer]

Hmm.. thanks for the solutions shadez.. but there's just one more problem.. When i click on A.. every word that contains the letter A will also appear.. for example the company value WAT is also displayed..
Anybody has a way to solve this?

 

[Shadez]

Im assuming you only want to look at the first few letters of the string.

so

strCoy = "Company Like " & Chr(34) &  Forms![ReportPage]!CompanyCbx & "*" & Chr(34)

will check for the string at the beginning only


ShadeZ

 

[keybearer]

not quite.. the data in the field may be jumbled up according to the company the person is in first.. but i guess the way my data is entered is quite screwed and maybe i need to rearrange it..
anyways.. thanks a bunch, shadez..

 

[keybearer]

hey shadez, if u'r still there, is it possible to use "OR" in the like expression to check the values? Lets say i want the expression to look for "A " or " A" and not "A"..

 

[Shadez]

Hmm Thats a little more tricky but possible.

strCoy = "(Company Like " & Chr(34) & " " & Forms![ReportPage]!CompanyCbx & "*" & Chr(34) & " or company Like " & chr(34) & Forms![ReportPage]!CompanyCbx & "* " & chr(34) & ")"

Or mayby this might be better

strCoy = "(Company Like " & Chr(34) & "?" & Forms![ReportPage]!CompanyCbx & "*" & Chr(34) & " or company Like " & chr(34) & Forms![ReportPage]!CompanyCbx & "*?" & chr(34) & ") and company not like " & chr(34) & " " & Forms![ReportPage]!CompanyCbx & " " & chr(34)

The first will retrun all reacords containing an "A" at the beginning or end of a word. Note yout word has to end in a space or begin in a space, I cant put (not " A ") in as this will make the statement return nothing. If this is a requirement then a different method needs to be used

The second will search for a "A" any where in teh string but ignore records containing " A "'s, again this is not a perfect situation.

Have a look in the help for "LIKE" there is a lot of info about it.

If neither of these options are acceptable, then its back to my first suggestion and using IF or IIF statments.



ShadeZ

 

[keybearer]

hmm.. thanks a lot shadez... i'll heed ur advice n do a bit of studying.. thanks again..