Adding Rows

[chuck via AccessMonster.com]

I have a unique situation that I'm not sure how to solve:

I have many fields but I only want to change one field. For example.

Field 11
972.00^05001200^

This contains a dollar amount as well as time in military time. Where I have
times that are between 05001200 I want to split the data out so that it
reflects two times. 05000559 and 06001200. I want to split it out and
create a new row with all the same information in the fields but instead of
having one row that is 05001200 it would have two such as 05000559 and
06001200.

I also want to change the second dollar amount from 972.00 to 0.00 as the
second time won't have a dollar amount associated with it. I know this is
sort of confusing but any help will be much appreciated.

Thanks,

Chuck

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

[Br]

chuck via AccessMonster.com wrote:

I have a unique situation that I'm not sure how to solve:

I have many fields but I only want to change one field. For example.

Field 11
972.00^05001200^

This contains a dollar amount as well as time in military time.
Where I have times that are between 05001200 I want to split the data
out so that it reflects two times. 05000559 and 06001200. I want to
split it out and create a new row with all the same information in
the fields but instead of having one row that is 05001200 it would
have two such as 05000559 and 06001200.

I also want to change the second dollar amount from 972.00 to 0.00 as
the second time won't have a dollar amount associated with it. I
know this is sort of confusing but any help will be much appreciated.

Thanks,

Chuck

I think you'd need to do two passes, one for each new record:

(I don't know how the time works so I've made some assumptions)

INSERT INTO tblTest ( Field11 )
SELECT "000.00^" & Mid([Field11],8,4) & "0559^"
FROM tblTest
WHERE (((Right([Field11],5))="1200^"));

INSERT INTO tblTest ( Field11 )
SELECT "000.00^" & Format(CLng(Mid([Field11],8,2))+1,"00") & "001200^" AS
Expr1
FROM tblTest
WHERE (((Right([Field11],5))="1200^"));

--
regards,

Br@dley