Returning part of a string

[dr4ke]

Can someone tell if it's possible to return a Text string between two points? To elaborate, the column I want to apply this to has a different number of characters that I want to return so it isn't as simple as a MID(Text, StartPosition,NumberOfCharacters) job.



Some examples of what it will apply to are below, the common factor in them all is that they all start with a '.', have the number, a space and then 'GB'. What I would like to return is just the number.

.1.098 GB (1,178,599,424 bytes)
.2 GB (2,147,483,648 bytes)
.1.488 GB (1,598,029,824 bytes)
.1.952 GB (2,096,103,424 bytes)
.6 GB (6,442,450,944 bytes)

So it would look like...

1.098
2
1.488
1.952
6

Is it possible to return text from two text conditions? I.e. '.' & 'GB' or is there another way to do this?

[JoeM]

How about this:
MyValue: Mid([MyField],2,InStrRev([MyField],"G")-3)
where MyField is the name of your field.

[dr4ke]

Perfect, thank you :-)

Can you explain it please? I know what Mid means...

What is InstrRev?

[JoeM]

Actually, I meant to use INSTR, not INSTRREV, though both will work in the examples you provided. INSTR is probably the better one to use in this instance. It is similar to the Excel FIND function.

Here are some links for you:
http://www.techonthenet.com/access/f...ring/instr.php
http://www.techonthenet.com/access/f...g/instrrev.php

[dr4ke]

Thank You :-) really appreciate it!

[dr4ke]

Thank you for the links; i've done the reading/writing and this is the summary I've come up with. Could you possible tell me if this is correct?

 MID([myfield],2,

Simply this is telling the Query to look into the relevant column ('myfield' is this instance) and start at position the 'cutting' of the field as position 2...

 Instr([MyField],"G")

This is the 'Number of Characters' part of the MID function, but instead of entering a number value it's advising it to search for the letter "G" as the end of the 'cut' string.

 -3)

This then cuts off the last three characters in the returned string (' GB').

[JoeM]

This is the 'Number of Characters' part of the MID function, but instead of entering a number value it's advising it to search for the letter "G" as the end of the 'cut' string.

Not quite. Instr([MyField],"G") does return a numeric value. All it says is find the numeric position where the first "G" resides in the string returned by the field "My Field".

Since we are trying to determine the length of the value we want to return, we need to find the position of "G". However, since we are not starting at the very beginning (but instead starting at position 2), we need to trim the length down a little, which is why we subtract 3.

To see exactly how it all works, it is sometimes helpful to break the function up into smaller parts to see what each part does. It is also good to look at the arguments of each function (MID and INSTR) to see what each one represents.

Hope that helps.

[dr4ke]

Thank you for that, I understand now. Because the syntax of MID is

 Mid ( text, start_position, number_of_characters ) 

the INSTR function is pointing the 'number_Of_Characters' part of this at whatever position (as a numeric value) that the first "G" is located.