Query Help

[ptw78]

I have a field in a query that returns a value that has 10 digits(spaces). An example would be xxxxxyyyyy, or it would be xx yyyyyy. So it can be any combination of X, Y, or a space. Is it possible to split up each one of the digits/spaces into a separate field for each? I guess it would be like a text to columns in excel. I saw a previous thread about it but not sure if it would apply in my example.

 

[genomon]

SELECT
Mid(fieldname, 1, 1) AS Field1,
Mid(fieldname, 2, 1) AS Field2,
Mid(fieldname, 3, 1) AS Field3,
etc....
FROM yourtable


Beir bua agus beannacht!

 

[Andrzejek]

I know some Access wizard will give you the Access answer, but in Oracle I would do something like:
[tt]
SELECT FIELDX,
SUBSTR(FIELDX, 1, 1) AS a,
SUBSTR(FIELDX, 2, 1) AS b,
SUBSTR(FIELDX, 3, 1) AS c,
SUBSTR(FIELDX, 4, 1) AS d
FROM MyTable[/tt]

which would give me something like this:

[tt]
FIELDX A B C D
09C009030 0 9 C 0
65C065047 6 5 C 0
531366700 5 3 1 3
430374701 4 3 0 3[/tt]

You may just find what Access uses for SUBSTR function

Have fun.

---- Andy

 

[ptw78]

I wasn't sure how to do Genomon's way, I'm assuming through SQL in Access, but w/other code in it, I'm not sure where to put it. So I tried w/the mid function. And it doesn't appear the mid function reads a space in Acces. Any way around that?

 

[Golom]

Can you post the SQL and/or code that you used? Mid (as shown by genomon) should have no problem returning a space character.

 

[ptw78]

Nevermind, I got it all to work I think. Doing some double checking. Thanks though. Your SQL pointed me in the right direction.

 

[SkipVought]

Something like this

ExprX: mid([Field],1,instr([Field],right([Field],1))-1)
ExprY: mid([Field],instr([Field],right([Field],1)))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[genomon]

ptw78 - Paste this code into the SQL view pane in a new query design session - change the table name and field name to suit your database. I set up a test table and it works fine:

SELECT
Mid(fieldone, 1, 1) AS Field1,
Mid(fieldone, 2, 1) AS Field2,
Mid(fieldone, 3, 1) AS Field3,
Mid(fieldone, 4, 1) AS Field4,
Mid(fieldone, 5, 1) AS Field5,
Mid(fieldone, 6, 1) AS Field6,
Mid(fieldone, 7, 1) AS Field7,
Mid(fieldone, 8, 1) AS Field8,
Mid(fieldone, 9, 1) AS Field9,
Mid(fieldone, 10, 1) AS Field10
FROM table1

If you don't like names like "Field1", then change it to something pretty like "Ethel" or "Myrna". The SQL engine doesn't care...


Beir bua agus beannacht!