Shorten a Text Field via Query Expresion ?

[AlienX69]

I have a text field that have a few records with a consistent string at the end, i.e. "....... Disk 1 of 4" or "...... Disk 1 of 3"

Characters before the "Disk 1 of #" varies so the Left function will not work

Thought of using "Mid", but don't think that will work either ?

I would like to remove the " of 3" from each text string so that the end result only has "....... Disk 1"

Thanks in advance for your assistance !!

 

[missinglinq]

Is the final digit, say the

4 in Disk 1 of 4

always going to be a single digit, i.e. 9 or less? If so,

LEFT(YourString,Len(YourString)-5)

will do the job. If it can be more than 9 you'll have to test the string to see what the numeric value at the end is, using something like

Val(Right(YourString,Len(YourString)-Instrrev(YourString," ")))

and adjust the 5 in the original code accordingly.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[AlienX69]

Thanks for the assistance Missingling

Yes, currently I do have two (2) records that do go to value above 9, i.e. 10 and 12 (see below)

I forgot about something else too, I have the following at the end of a few records as well ".....(1A)"

Here are some records/examples of the field [MovieTitle]:

2001: A Space Odyssey
24: Season 1 - Disk 1 of 6
Deadwood [HBO]: Season 1 - Disk 1 of 6
Las Vegas: Season 1 [Uncut & Uncensored] - Disk 1 of 6 (1A)
Lost: Season 1 - Disk 1 of 8
NYPD Blue: Season 3 - Disk 1 of 8 (1A)
Rescue Me: Season 1 - Disk 1 of 3
Shawshank Redemption, The [10th Anniversary Edition]
Young Indiana Jones: Volume 1 - Disk 1 of 12
Young Indiana Jones: Volume 2 - Disk 1 of 10

And as previously mentioned I would like to strip out the " of #", as shown in the above examples so the results would be


2001: A Space Odyssey
24: Season 1 - Disk 1
Deadwood [HBO]: Season 1 - Disk 1
Las Vegas: Season 1 [Uncut & Uncensored] - Disk 1 (1A)
Lost: Season 1 - Disk 1
NYPD Blue: Season 3 - Disk 1 (1A)
Rescue Me: Season 1 - Disk 1
Shawshank Redemption, The [10th Anniversary Edition]
Young Indiana Jones: Volume 1 - Disk 1
Young Indiana Jones: Volume 2 - Disk 1

FYI:
The field name is [MovieTitle] and I'm looking to use a Query Expression, i.e. DiskInfoTitle: Val(Right(IIf([MovieTitle],Len...........

...... or another possibility ......

I do store how many disks there are in another FIELD called [Disks]. Which I could be inserted at the end where a record is returned in a IIF ? But how would I address the ones with the (1A) at the end, if the ACTUAL records were as shown in the RESULTS above.

You see my problem is on my reports I want the "...... of 8", but on a particular Form I'm using, I would like NOT to see the " of #"

Hope that was a clearer than Mud ? ... ;-)

Again thanks for all the help, I'm still a junior at Access

AX

 

[PHV]

Have a look here:

http://msdn2.microsoft.com/en-us/library/6wzad2b2.aspx

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[HarleyQuinn]

One of my favourite links PHV

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com