Find a letter(s) in a field...

[stone28]

Hi All,

I am trying to create some autocomplete function (really simple one) but not sure how SQL should look for that. How can I ask DB if letters "xyz" (in that exact form) exists in the field. Or if word "hi exist in a field?

Thanks.

 

[Kryst51]

I used this:

Like (UCase([Customer Name?])+"*")

in the criteria in the QBE for one of my queries and it works great, I am sure you can add an asterisk (wild card) before the field too,

Here is the SQL for it:

WHERE (((UCase([cus_cus_long_nm])) Like (UCase([Customer Name?])+"*")));

 

[raskew]

Hi -

Lookup the InStr() function. Examples:

x = "abcefxyzn"
? instr(x, "xyz")
 6 

You can then use it in conjunction with the [B]Iif()[/B] function.
iif(instr(x, "xyz")>0, Action if true, Action if false

)

HTH - Bob

 

[spaddhu]

To stick with the use of Like clause, yes you can give the wild card * in front and/or back of the required field. However, I've known only using the & to concatenate strings as against the + that you have used.
This should work fine
WHERE (((UCase([cus_cus_long_nm])) Like ("*" & UCase([Customer Name?]) & "*")));

 

[ankit]

i have this number w14-1245-01, i want to do w14-1245-1 i can't use replace bcz i have like this also w14-0145-1 if i try to replace -01 to -1 the first number also replace please help.

 

[Atomic Shrimp]

i have this number w14-1245-01, i want to do w14-1245-1 i can't use replace bcz i have like this also w14-0145-1 if i try to replace -01 to -1 the first number also replace please help.

Does the last section (the bit from which you want to remove the zero) always consist of two digits - never more and never fewer?

 

[spaddhu]

Hi Ankit,

Pls post new requests as new threads, lest they go unseen and unanswered.
That being said, this is what you need to do..
Add an field expression : Left([Ref_Code],9) & CStr(Val(Right([Ref_Code],2)))
where Ref_Code is the field that you want to replace. The 9 indicates that only 9 chars are there before the last two digits; if this is not fixed, i.e. there may be longer strings, then you have to code 9 dynamically...

 

[Kryst51]

Does the last section (the bit from which you want to remove the zero) always consist of two digits - never more and never fewer?

Shouldn't this question be a new thread?

Edit: nevermind, I didn't read to the bottom, great answer spaddu.

 

[ankit]

i am sorry i am new in this forum i can't find new post before but now i know how to post. my apologies

 

[spaddhu]

It's OK Ankit, next time be sure to post a new thread for your queries.
Krist51 - Thanx.

 

[raskew]

Hi Ankit -

If the need for correction will always be in the last element of
a string, you can use the InStrRev() function to resolve it,
regardless of the length of the string.

Example from the debug/immediate window:

x = "w14-1245-01"
y = left(x, instrrev(x, "-")) & val(mid(x, instrrev(x, "-")+1))

? y
w14-1245-1

If, by chance, you're using A97 please post back and I'll post
an A97 version of the InStrRev() function.

HTH - Bob