Extracting specific positions for a field within a query

[JRG0620]

I have a 6 character field. For some reason the person who created this field decided to combine 3 unique fields into one field. I now need to extract the middle field so that it can be joined to another table. Here is the senerio:

The Field is Project and it contains 6 characters - 123456.
1 represents the type of project
23 represents the location of the project
456 represents the actual project

I need to create a query to extract 23. I can easily do this within VB by using a combination of Left and Right but I'm unsure how to do this within the QBE panel.

Any help would be appreciated.
Thanks

 

[dkinley]

You can use many of same functions in the QBE as you have with VB. In the QBE, go to a blank column and declare a variable by using some keystrokes and a colon (remember not to use reserved words). Then use your VB left/rights with the right field name from the table, [FieldName].

For example ..

MyNewData: Left([FieldName],3)

MyNewData is a variable and the definition of it is Left(..).

Hope that helps,
-dK

 

[Alansidman]

Here are two tutorials that should help you to accomplish what you want. It is similar to using the vba.

http://www.datapigtechnologies.com/flashfiles/leftright.html

http://www.datapigtechnologies.com/flashfiles/datamanipulate.html

Alan

 

[JRG0620]

Thanks for the quick reponse and information. I used the suggestions and the tutorials and figured out I could use the mid command: ProjCampus: ((Mid([Proj],2,2)))

This got me what I needed. Thanks again.