Query help

[Dr_Worm]

Hi,

I have a field in a table that has the following data;

Unit 2101 Type K7A Sch3 G9
Unit 230 Type K10B Sch10 G11
Etc.

There is always 6 “parts” to the string separated by a space.

I want to do a 3 queries, the first query to display;
K7A
K10B
Etc.

The second query to display;
Type K7A Sch3 G9
Type K10B Sch10 G11
Etc.

The third query;
2101
230
Etc.

If anyone can tell me how to do one of the queries and the syntax to do the rest so that I can learn how to pull the information out myself for other tables.

 

[Andrew Fergus]

Hi
It certainly helps that your data is in a consistent format. You can do these queries with variations on, and combinations of, the InStr, Right, Len and Mid functions.

For the first query try this as your field (I used the field name [text]) :

Mid([text], InStr( 1, [text], "Type") +5, InStr( InStr( 1, [text], "Type") +5, [text], " ") - (InStr( 1,[text],"Type") +5 ))

This is searching for the word "Type" in your field and working out how many characters to return in the Mid function based on the position of the word "Type" and the 2nd space after "Type".

The 2nd query is this :

Right([text], Len([text]) - InStr(1,[text], "Type") +1)

This looks for the wotd "Type" and returns all characters to the right of that.

and the 3rd is this :

Mid( [text], 6, InStr( 1, [text], "Type") -7)

This one starts at position 6 and returns the characters to the right of that up to the space before the word "Type".

Good luck with your other queries.

HTH, Andrew. )

 

[Dr_Worm]

Thankyou Andrew, they worked great.

I've also been able to create some other queries, but I'm need a little help with another one.

The data is in the form of number-number, eg.
1-1
1-23
15-2
23-101
124-3
I need to extract the number after the "-". The results would be;
1
23
2
101
3

 

[Andrew Fergus]

Hi
Try this : Right([text],Len([text])-InStr(1,[text],"-"))
I used the field [text] - remember to use your actual field name.
HTH, Andrew

 

[Dr_Worm]

Thanks Andrew,

That worked great! I didn't think I was going to need any more and I've tried to do create a query that extracts the numbers in front of the hyphen (-)

1-1
1-23
15-2
23-101
124-3

query displays
1
1
15
23
124

I really appreciate the help.

 

[Norie]

Try using something like Andrew posted but with the Left function.

Left([text],InStr([text],"-")-1)

 

[Dr_Worm]

I finished designing my database and it works great. One of my work mates is busy entering in all the data, now we find out the format of some of the fields have changed. What use to be;

Unit 2101 Type K7A Sch3 G9
Unit 230 Type K10B Sch10 G11
Unit T75 Type KL21 Sch2 G4
Etc.

is now;

Unit 2101 K7A Sch3 G9
Unit 230 K10B Sch10 G11
Unit T75 KL21 Sch2 G4
Etc.

With the word "Type" removed from the source data I need to change the three queries at the begining of this thread.

The new format is;

There is always 5 “parts” to the string separated by a space.

I want to do a 3 queries, the first query to display;
K7A
K10B
KL21
Etc.

The second query to display;
K7A Sch3 G9
K10B Sch10 G11
KL21 Sch2 G4
Etc.

The third query;
2101
230
T75
Etc.

Thank you

 

[Andrew Fergus]

Try these :

Mid([text],InStr(InStr([text]," ")+1,[text]," ")+1,InStr(InStr(InStr([text]," ")+1,[text]," ")+1,[text]," ")-InStr(InStr([text]," ")+1,[text]," ")-1)

(P.S. Edit due to formatting : there is a space bar character between the last quote on the first line and the first quote on the second line)

Right([text],Len([text])-InStr(InStr([text]," ")+1,[text]," "))

and

Mid([text],InStr([text]," ")+1,InStr(InStr([text]," ")+1,[text]," ")-InStr([text]," ")-1)

HTH, Andrew.