Truncating a Field

[Coral G via AccessMonster.com]

Using Access 2000 I need to truncate the last few characters of a string
based on the presence of a dash (-). This dash is not always present and can
be followed by a variable number of numbers and/or letters. Ideally I'd like
to do this with an Update Query to create a new field with the truncated
information but I'm open to other options as well.

Example: Field contains 123456-789 I would like a new field that only
returns 123456

Thanks for the help!

[Admiral Access via AccessMonster.com]

iif(Instr(1,[Field],"-")=0,[Field],Left([Field],Instr(1,[Field],"-")-1))

Coral G wrote:

Using Access 2000 I need to truncate the last few characters of a string
based on the presence of a dash (-). This dash is not always present and can
be followed by a variable number of numbers and/or letters. Ideally I'd like
to do this with an Update Query to create a new field with the truncated
information but I'm open to other options as well.

Example: Field contains 123456-789 I would like a new field that only
returns 123456

Thanks for the help!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200510/1

[Mark]

It doesn't seem like a good idea to create another field in your table which
holds information that already exists in your table. You can select the
information upto the "-" using the InStr function. With table data =

123456-78910
12345678

Query =
SELECT
IIf(InStr([your_field],"-")-1<0,[your_field],Mid([your_field],1,InStr([your_field],"-")-1))
AS Expr1
FROM your_table;

results =
123456
12345678

If you did want yo add the data to another field, the following will work:
UPDATE your_table SET your_table.truncated_field =
Mid([your_Field],1,InStr([your_field],"-")-1)
WHERE (((your_table.your_field) Like "*-*"));

Regards,

Mark

"Coral G via AccessMonster.com" <u14591@uwe> wrote in message
news:5563642b9dd72@uwe...

Using Access 2000 I need to truncate the last few characters of a string
based on the presence of a dash (-). This dash is not always present and
can
be followed by a variable number of numbers and/or letters. Ideally I'd
like
to do this with an Update Query to create a new field with the truncated
information but I'm open to other options as well.

Example: Field contains 123456-789 I would like a new field that only
returns 123456

Thanks for the help!

[Coral G via AccessMonster.com]

I know it doesn't seem like a good idea, but that's what my boss asked for :)

I think she'll be ok with splitting it into two fields, so I tried the second
option you listed but it's giving me a compile error. It's saying the
problem is in the Mid Expression, not sure if I did something wrong. Any
advice? I'm a bit new to this stuff, so it's probably something simple I
imagine. This is what it looks like right now:

UPDATE Customers SET Customers.ID2 =
Mid([ID],1,InStr([ID],"-")-1)
WHERE (((Customers.ID) Like "*-*"));

Customers is the name of the Table, ID is the name of the field I'm working
with.

Mark wrote:

It doesn't seem like a good idea to create another field in your table which
holds information that already exists in your table. You can select the
information upto the "-" using the InStr function. With table data =

123456-78910
12345678

Query =
SELECT
IIf(InStr([your_field],"-")-1<0,[your_field],Mid([your_field],1,InStr([your_field],"-")-1))
AS Expr1
FROM your_table;

results =
123456
12345678

If you did want yo add the data to another field, the following will work:

UPDATE your_table SET your_table.truncated_field =
Mid([your_Field],1,InStr([your_field],"-")-1)
WHERE (((your_table.your_field) Like "*-*"));

Regards,

Mark

Using Access 2000 I need to truncate the last few characters of a string
based on the presence of a dash (-). This dash is not always present and

[quoted text clipped - 8 lines]

Thanks for the help!

[Mark]

I have changed my table and field names and this is working fine. Just out
of curiosity, are both ID and ID2 set to Text datatypes?

UPDATE Customers SET Customers.ID2 = Mid([id],1,InStr([id],"-")-1)
WHERE (((Customers.ID) Like "*-*"));

Mark

"Coral G via AccessMonster.com" <u14591@uwe> wrote in message
news:55644f607c4c6@uwe...

I know it doesn't seem like a good idea, but that's what my boss asked for
:)

I think she'll be ok with splitting it into two fields, so I tried the
second
option you listed but it's giving me a compile error. It's saying the
problem is in the Mid Expression, not sure if I did something wrong. Any
advice? I'm a bit new to this stuff, so it's probably something simple I
imagine. This is what it looks like right now:

UPDATE Customers SET Customers.ID2 =
Mid([ID],1,InStr([ID],"-")-1)
WHERE (((Customers.ID) Like "*-*"));

Customers is the name of the Table, ID is the name of the field I'm
working
with.

Mark wrote:

It doesn't seem like a good idea to create another field in your table
which
holds information that already exists in your table. You can select the
information upto the "-" using the InStr function. With table data =

123456-78910
12345678

Query =
SELECT
IIf(InStr([your_field],"-")-1<0,[your_field],Mid([your_field],1,InStr([your_field],"-")-1))
AS Expr1
FROM your_table;

results =
123456
12345678

If you did want yo add the data to another field, the following will work:

UPDATE your_table SET your_table.truncated_field =
Mid([your_Field],1,InStr([your_field],"-")-1)
WHERE (((your_table.your_field) Like "*-*"));

Regards,

Mark

Using Access 2000 I need to truncate the last few characters of a string
based on the presence of a dash (-). This dash is not always present
and

[quoted text clipped - 8 lines]

Thanks for the help!

[Coral G via AccessMonster.com]

Yay, now it works, I have no idea why though. Thanks Mark!

Mark wrote:

I have changed my table and field names and this is working fine. Just out
of curiosity, are both ID and ID2 set to Text datatypes?

UPDATE Customers SET Customers.ID2 = Mid([id],1,InStr([id],"-")-1)
WHERE (((Customers.ID) Like "*-*"));

Mark

I know it doesn't seem like a good idea, but that's what my boss asked for
:)

[quoted text clipped - 48 lines]

Thanks for the help!

--
Message posted via http://www.accessmonster.com

[Coral G via AccessMonster.com]

I have a follow up question to this. Basically what this does is dump the
truncated data into another field, but right now I have to create that field
before I run the query. This is fine for me, but other people are going to
use the query and I want to know if it's possible to have the new field
automatically inserted into the table to minimize the work they have to do.

Thanks for the input!

Coral G wrote:

Yay, now it works, I have no idea why though. Thanks Mark!

I have changed my table and field names and this is working fine. Just out
of curiosity, are both ID and ID2 set to Text datatypes?

[quoted text clipped - 9 lines]

>
> Thanks for the help!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200510/1

[Mark]

I'm no expert with table definition SQL's but as far as I know, you would
need to first run the Definition SQL and then run the UPDATE SQL.
Something like : alter table [customers] add column [ID3] string (50);

This will add another field to the table called ID3 which would be of a text
datatype and a field length of 50.

Then you could run your UPDATE query.

Regards,

Mark

"Coral G via AccessMonster.com" <u14591@uwe> wrote in message
news:55a38bdab6e23@uwe...

I have a follow up question to this. Basically what this does is dump the
truncated data into another field, but right now I have to create that
field
before I run the query. This is fine for me, but other people are going
to
use the query and I want to know if it's possible to have the new field
automatically inserted into the table to minimize the work they have to
do.

Thanks for the input!

Coral G wrote:

Yay, now it works, I have no idea why though. Thanks Mark!

I have changed my table and field names and this is working fine. Just
out
of curiosity, are both ID and ID2 set to Text datatypes?

[quoted text clipped - 9 lines]

>>
>> Thanks for the help!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200510/1