 | Global Replace date based on date |  |
 Posted: Fri Oct 19, 2007 12:28 pm |
Points: 0  |
|
| Deborah |
| Obsessive GoldMiner |
|
 |
| Joined: 05 Apr 2007 |
| Posts: 128 |
| Location: Seattle, Washington |
|
|
|
|
|
 |
|
Hi Bob,
I am trying to do a global replace of a field in the contsupp table. I am using Details Plus and the 2 fields I am working with are "date" fields in DP but still really a Character field in Contsupp, if that makes sense.
So I have a date in contsupp.address1. I want to replace contsupp.address2 with a date that is the 1st of the month, one year from address1. So if address1 is 6/5/06 (or 20060605) I want the address2 to be 6/1/07 (or 20070601). I have tried various options using the AddYears(1,field) and trying to convert CtoD and back to DtoC with no luck. I frankly don't have time to fuss with this anymore. Can you help with the formula? |
|
_________________
--Deborah Atkinson
Solutus
206-937-0451 |
|
|
|
| | |
| Posted: Fri Oct 19, 2007 12:54 pm |
Points: 0 |
|
| BobTaylor |
| GoldBox Guru |
|
|
| Joined: 07 Sep 2007 |
| Posts: 1216 |
| Location: Jacksonville, FL |
|
|
|
|
|
|
|
It would be easy, IF I knew exactly what is in Address1; that is, the format. If it's like 20060605, that's fine. Then the expression for Address2 would be
| Code: |
| LTrim(Str(Val(Left(Contsupp->Address1, 4))+1)+Substr(Contsupp->Address1, 5, 2)+"01") |
Of course, if it's some other format, it will be some other expression. Because I'm not familiar with what Details Plus does, you'll have to test that for yourself. |
|
_________________
Bob Taylor
Bob Taylor Consulting
(904)646-9861
www.goldboxbob.com
Consulting with GoldBox and GoldMine since 1997. |
|
|
|
| | |
| Posted: Fri Oct 19, 2007 1:04 pm |
Points: 0 |
|
| Deborah |
| Obsessive GoldMiner |
|
|
| Joined: 05 Apr 2007 |
| Posts: 128 |
| Location: Seattle, Washington |
|
|
|
|
|
|
|
| It worked beautifully!! I had to change contsupp to tgtsup but other than that it is exactly what I needed. I think I was getting caught up in the complexity of changing data type. It looks like DP doesn't change the underlying data type but only the data entry and display formatting. This was exactly what I needed. Thanks. |
|
_________________
--Deborah Atkinson
Solutus
206-937-0451 |
|
|
|
| | |
| Posted: Fri Oct 19, 2007 1:30 pm |
Points: 0 |
|
| BobTaylor |
| GoldBox Guru |
|
|
| Joined: 07 Sep 2007 |
| Posts: 1216 |
| Location: Jacksonville, FL |
|
|
|
|
|
|
|
I know there are some presentations that could make you think that Details Plus is actually changing the data type, but that's impossible. GoldBox Views also store dates as strings, and give you calendars to select the dates. It's just a kind of trick, really.
If you try to run a SQL query against the Contsupp table, you'll quickly find that they are still character fields. However, using string dates does make it possible to compare one date to another, to find which is larger. But actually subtracting one string date from another...now that's a bit of a chore. |
|
_________________
Bob Taylor
Bob Taylor Consulting
(904)646-9861
www.goldboxbob.com
Consulting with GoldBox and GoldMine since 1997. |
|
|
|
ContactReview Forum Index » GoldBox
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
All times are GMT - 7 Hours
Page 1 of 1
|
|
|
|
Brought to you by Castell Computers, Doug Castell, Admin
Powered by phpBB © 2001-2004 phpBB Group
Theme created by Vjacheslav Trushkin
|
