Newbie Probably Trying To Run Before She Can Walk

[Martina]

OK

As I said I'm probably trying to run before I can walk but even if somebody
could just tell me "it can't be done" then at least I can stop wasting my time.
Of course anything that can help me solve the problem (Not macros haven't got
into them yet) would be muuch appreciated.

OK here is the problem I'm trying to resolve; I have a field in a table whose
contence is the the form

xxx-nnnnnn-cccc

where:
x = character (a-z)
n = number 0-9
c = character either a-z or 0-9 or a combination of both
- = hyphen

Unfortunately the number of characters in each of the 3 groups varies

first group between 2 & 5 characters
second group between 5 and 8 characters
third group between 3 and 4 characters


What I'm trying to do is to run a simple quesry that will generate a table
comprising of 3 fields, one for each of the 3 groups i.e. split the single field
into 3 using the "-" as a delimeter.

Any hep much appreciated.


Thanks

Martina

 

[Guest]

Looks like a job for the Left, Mid, Right, and InStr functions. Try something
like this.

SELECT tblParseSSAN.Dashes,
Left([Dashes],InStr(1,[Dashes],"-")-1) AS FirstPart,
Mid([Dashes],InStr(1,[Dashes],"-")+1,InStr(1,Mid([Dashes],InStr(1,[Dashes],"-")+1),"-")-1) AS MiddlePart,
Right([Dashes],5-InStr(1,Right([Dashes],5),"-")) AS LastPart
FROM tblParseSSAN;

Change all the "tblParseSSAN" above to your table name. Change "Dashes"
above to your field name. Test and test again before using.

I was tempted to use the Val function for the middle part; however, any
non-numerical characters would cause a problem.

Also there must be data just as you described or there will be errors. For
example just "ABC-78777" will throw an error.

 

[Martina]

Sorry about this I'm not joking with the term "Newbie"

I'm trying to work this out but failing at the first step. Is this a Macro or
should I be pasting it into the "Zoom Window" for the Critera

Tried both but not getting anywhere with either.

One other thing, when I cut and past I have a blank space at the end of each
line, should I delete these when I pased it in ?

Thanks for your help and VERY prompt reply

Martina

Looks like a job for the Left, Mid, Right, and InStr functions. Try something
like this.

SELECT tblParseSSAN.Dashes,
Left([Dashes],InStr(1,[Dashes],"-")-1) AS FirstPart,
Mid([Dashes],InStr(1,[Dashes],"-")+1,InStr(1,Mid([Dashes],InStr(1,[Dashes],"-")+1),"-")-1)
AS MiddlePart,
Right([Dashes],5-InStr(1,Right([Dashes],5),"-")) AS LastPart
FROM tblParseSSAN;

Change all the "tblParseSSAN" above to your table name. Change "Dashes"
above to your field name. Test and test again before using.

I was tempted to use the Val function for the middle part; however, any
non-numerical characters would cause a problem.

Also there must be data just as you described or there will be errors. For
example just "ABC-78777" will throw an error.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

OK

As I said I'm probably trying to run before I can walk but even if somebody
could just tell me "it can't be done" then at least I can stop wasting my time.
Of course anything that can help me solve the problem (Not macros haven't got
into them yet) would be muuch appreciated.

OK here is the problem I'm trying to resolve; I have a field in a table whose
contence is the the form

xxx-nnnnnn-cccc

where:
x = character (a-z)
n = number 0-9
c = character either a-z or 0-9 or a combination of both
- = hyphen

Unfortunately the number of characters in each of the 3 groups varies

first group between 2 & 5 characters
second group between 5 and 8 characters
third group between 3 and 4 characters


What I'm trying to do is to run a simple quesry that will generate a table
comprising of 3 fields, one for each of the 3 groups i.e. split the single field
into 3 using the "-" as a delimeter.

Any hep much appreciated.


Thanks

Martina

 

[Guest]

First using Word paste the stuff that I sent to you. Change things to match
your table and field names. The result is called SQL for Structured Query
Language and a standard way to talk to many different databases.

Open a new query in design view and close it without adding any tables. Next
go to View, SQL View and paste in your modified SQL. Try to run it. There
will probably be an error as even a simple typo can mess things up.
Word-wrapping usually isn't a problem, but could be.

See if you can get it to work. When done, save the query. You can then open
it in Design View and see how the QBE grid interpets the SQL. You can also go
the opposite way and see how Access writes SQL from the QBE grig. It's a good
learning tool.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Sorry about this I'm not joking with the term "Newbie"

I'm trying to work this out but failing at the first step. Is this a Macro or
should I be pasting it into the "Zoom Window" for the Critera

Tried both but not getting anywhere with either.

One other thing, when I cut and past I have a blank space at the end of each
line, should I delete these when I pased it in ?

Thanks for your help and VERY prompt reply

Martina

Looks like a job for the Left, Mid, Right, and InStr functions. Try something
like this.

SELECT tblParseSSAN.Dashes,
Left([Dashes],InStr(1,[Dashes],"-")-1) AS FirstPart,
Mid([Dashes],InStr(1,[Dashes],"-")+1,InStr(1,Mid([Dashes],InStr(1,[Dashes],"-")+1),"-")-1)
AS MiddlePart,
Right([Dashes],5-InStr(1,Right([Dashes],5),"-")) AS LastPart
FROM tblParseSSAN;

Change all the "tblParseSSAN" above to your table name. Change "Dashes"
above to your field name. Test and test again before using.

I was tempted to use the Val function for the middle part; however, any
non-numerical characters would cause a problem.

Also there must be data just as you described or there will be errors. For
example just "ABC-78777" will throw an error.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

OK

As I said I'm probably trying to run before I can walk but even if somebody
could just tell me "it can't be done" then at least I can stop wasting my time.
Of course anything that can help me solve the problem (Not macros haven't got
into them yet) would be muuch appreciated.

OK here is the problem I'm trying to resolve; I have a field in a table whose
contence is the the form

xxx-nnnnnn-cccc

where:
x = character (a-z)
n = number 0-9
c = character either a-z or 0-9 or a combination of both
- = hyphen

Unfortunately the number of characters in each of the 3 groups varies

first group between 2 & 5 characters
second group between 5 and 8 characters
third group between 3 and 4 characters


What I'm trying to do is to run a simple quesry that will generate a table
comprising of 3 fields, one for each of the 3 groups i.e. split the single field
into 3 using the "-" as a delimeter.

Any hep much appreciated.


Thanks

Martina

 

[John Vinson]

OK

As I said I'm probably trying to run before I can walk but even if somebody
could just tell me "it can't be done" then at least I can stop wasting my time.
Of course anything that can help me solve the problem (Not macros haven't got
into them yet) would be muuch appreciated.

OK here is the problem I'm trying to resolve; I have a field in a table whose
contence is the the form

xxx-nnnnnn-cccc

where:
x = character (a-z)
n = number 0-9
c = character either a-z or 0-9 or a combination of both
- = hyphen

Unfortunately the number of characters in each of the 3 groups varies

first group between 2 & 5 characters
second group between 5 and 8 characters
third group between 3 and 4 characters


What I'm trying to do is to run a simple quesry that will generate a table
comprising of 3 fields, one for each of the 3 groups i.e. split the single field
into 3 using the "-" as a delimeter.

Create a new Query. You don't say what the name of this field is so
I'll just call it MyField.

In the Query grid, type

FirstPart: Left([MyField], InStr([MyField], "-") - 1)

in the first vacant Field cell (the top row of the first blank
column).

In the next column's Field cell, type

SecondPart: Mid([MyField], InStr([MyField], "-") + 1,
InStr(InStr([MyField], "-") - 1,[MyField],"-")-1)

and in the third column's Field cell, type:

ThirdPart: Mid([MyField], InStr(InStr([MyField], "-") +
1,[MyField],"-")+1)

You have now learned why it's considered a Bad Idea to pack three
different kinds of data into one field! It's much easier to splice
them together than to pull them apart.

John W. Vinson[MVP]


and finally in a third

 

[Martina]

To John and Jerry

Thanks both for your help, I'm getting somewhere and what's more I understand
what I'm doing (just). Tried both techniques and they works fine though only
with some simple test data not with the actual data I need to manipulate but
thats probably my fault I'll run through it tomorrow to see what I've messed up

Anyway a BIG thankyou to you both

Martina

Sorry about this I'm not joking with the term "Newbie"

I'm trying to work this out but failing at the first step. Is this a Macro or
should I be pasting it into the "Zoom Window" for the Critera

Tried both but not getting anywhere with either.

One other thing, when I cut and past I have a blank space at the end of each
line, should I delete these when I pased it in ?

Thanks for your help and VERY prompt reply

Martina

Looks like a job for the Left, Mid, Right, and InStr functions. Try something
like this.

SELECT tblParseSSAN.Dashes,
Left([Dashes],InStr(1,[Dashes],"-")-1) AS FirstPart,
Mid([Dashes],InStr(1,[Dashes],"-")+1,InStr(1,Mid([Dashes],InStr(1,[Dashes],"-")+1),"-")-1)
AS MiddlePart,
Right([Dashes],5-InStr(1,Right([Dashes],5),"-")) AS LastPart
FROM tblParseSSAN;

Change all the "tblParseSSAN" above to your table name. Change "Dashes"
above to your field name. Test and test again before using.

I was tempted to use the Val function for the middle part; however, any
non-numerical characters would cause a problem.

Also there must be data just as you described or there will be errors. For
example just "ABC-78777" will throw an error.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

OK

As I said I'm probably trying to run before I can walk but even if somebody
could just tell me "it can't be done" then at least I can stop wasting my time.
Of course anything that can help me solve the problem (Not macros haven't got
into them yet) would be muuch appreciated.

OK here is the problem I'm trying to resolve; I have a field in a table whose
contence is the the form

xxx-nnnnnn-cccc

where:
x = character (a-z)
n = number 0-9
c = character either a-z or 0-9 or a combination of both
- = hyphen

Unfortunately the number of characters in each of the 3 groups varies

first group between 2 & 5 characters
second group between 5 and 8 characters
third group between 3 and 4 characters


What I'm trying to do is to run a simple quesry that will generate a table
comprising of 3 fields, one for each of the 3 groups i.e. split the single field
into 3 using the "-" as a delimeter.

Any hep much appreciated.


Thanks

Martina