String Search and Manipulation

[mous]

I have a string field i.e. YYYNNNNNNNYYNNNYYNN that will always start at one and always finish on 53.

I'm using InStr(1,[Weeks],"Y") to find the first Y in the string, but how do I find the last Y in the string?

If the first Character in the string is 28/07/03 (every Monday) - can I tell the dates of all other characters)?

Can I count how many "Y"'s there are in the string?

Can I also determine the actual week day from the above dates, I also have the day number in the table.

I know there's a lot here but I'm stuck and need this information fast.

Thanks for any help.

 

[dcx693]

If you're using Access 2000+, you can use the InstrRev() function. Otherwise, you'll need to call Instr() repeatedly, until it returns 0.

 

[mous]

Not in query, you can't.

 

[dcx693]

Not in query, you can't.

I was answering your first question. Somehow, I don't recall there being that many questions in there when I posted my response...but never mind.

If the first Character in the string is 28/07/03 (every Monday) - can I tell the dates of all other characters)?

Say what? A character is a single character. Not 8 digits like you posted. What do you mean?

While we're at it:

I have a string field i.e. YYYNNNNNNNYYNNNYYNN that will always start at one and always finish on 53.

What does that even mean?

Can I count how many "Y"'s there are in the string?

I don't know of a clever way to do this. I think you'll have to write a custom function.

 

[Kodo]

acutally, there is a really clever way to do this without looping through with instr

split it. Before you say it's not going to work, think about it. It's one string with no spaces or delimeters.

so in essence: YNNNNNNN would be listed as one element of the array if you're checking for Y and thus it would be counted as 1

so if you do something like this

mystring="YYYNNNNNNNYYNNNYYNN"

mystring2=split(mystring,"Y")

you can then just grab the ubound of mystring2 and there you have your answer.

 

[dcx693]

Actually, I remembered that Access 2000+ has the Replace function. Use it like this to count the number of Y's in a string: Len(Replace("YYYNNNNNNNYYNNNYYNN","N",""))