How do I increase data in column by 5

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

KIMA06 - 21 Nov 2007 14:26 GMT

I am updating a large country time zone file. I want to increase the TimeZone
column by 5. For example. Afghanistan's time zone is +0430. I want to add 5
to all records so in this case the time zone will change from +0430 to +0930.
Which means that Afganistan is 9 hours and 30 minutes ahead of EST.

Reply to this Message

Rick Brandt - 21 Nov 2007 14:37 GMT

> I am updating a large country time zone file. I want to increase the
> TimeZone column by 5. For example. Afghanistan's time zone is +0430.
> I want to add 5 to all records so in this case the time zone will
> change from +0430 to +0930. Which means that Afganistan is 9 hours
> and 30 minutes ahead of EST.

Is "+0430" a character field? If so...

UPDATE TableName
SET TimeZone = "+0930"
WHERE TimeZone = "+0430"

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

KIMA06 - 21 Nov 2007 14:56 GMT

Rick..Thanks for your response. There are 108K records in the file and all of
the time zones are different. The time zone is in GMT, but I need to add 5 to
each record in the timezone column, so that it converts from GMT to EST.

> > I am updating a large country time zone file. I want to increase the
> > TimeZone column by 5. For example. Afghanistan's time zone is +0430.
[quoted text clipped - 7 lines]
> SET TimeZone = "+0930"
> WHERE TimeZone = "+0430"

Reply to this Message

Rick Brandt - 21 Nov 2007 15:22 GMT

> Rick..Thanks for your response. There are 108K records in the file
> and all of the time zones are different. The time zone is in GMT, but
> I need to add 5 to each record in the timezone column, so that it
> converts from GMT to EST.

"Adding 5" cannot be resolved accurately until you answer the question in my
previous post.

What kind of data type is this field?

Also, in looking at both your posts it is not clear to me whether you want to
modify ALL rows or only those in a particular zone. If the latter how can you
identify those? By the value they currently contain or do you have other fields
that need to be used to filter on?

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Jeff Boyce - 21 Nov 2007 15:03 GMT

This may be unnecessarily picayune, but "5" + "0430" is "0435". It appears
you want to add 5 HOURS to the time represented by "0430". Given the
military-style time, you'd need to add 500.

Or, if the underlying data is stored as a date/time value (and simply
displayed as "0430"), you could use the DateAdd() function (see Access HELP
for syntax).

Signature

Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

> I am updating a large country time zone file. I want to increase the TimeZone
> column by 5. For example. Afghanistan's time zone is +0430. I want to add 5
> to all records so in this case the time zone will change from +0430 to +0930.
> Which means that Afganistan is 9 hours and 30 minutes ahead of EST.

Reply to this Message

KIMA06 - 21 Nov 2007 15:25 GMT

You are correct. I need to add 500 to all timezone fields. However in MS
Acces help a day"d", month"m" or hour"h", must be specfied. Since this is
military time there is no day, month or hour.

> This may be unnecessarily picayune, but "5" + "0430" is "0435". It appears
> you want to add 5 HOURS to the time represented by "0430". Given the
[quoted text clipped - 11 lines]
> +0930.
> > Which means that Afganistan is 9 hours and 30 minutes ahead of EST.

Reply to this Message

Rick Brandt - 21 Nov 2007 15:30 GMT

> You are correct. I need to add 500 to all timezone fields. However in
> MS Acces help a day"d", month"m" or hour"h", must be specfied. Since
> this is military time there is no day, month or hour.

That would be true if you were using the DateAdd() function and that would only
be appropriate if your field were a DateTime data type. Is it?

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message