Part appending a field (Access 2000)

  • Thread starter Thread starter BigAl
  • Start date Start date
B

BigAl

I feel that this question must have been asked before but a google search
hasn't found anything. Apologies if it's old hat.

I'm correcting a database (hah! 1 table, 41 fields) and I've run into a bit
of a problem. One field is year commenced with just a 4 digit field. Another
field is date subs due which is a proper date field of dd/mm/yyyy.
What I need to do is copy over the dd/mm/ from the subs due field to the
start of the year commenced field so that they match - apart from years of
course. I could do it the long winded copy and paste but as it's 2,000
records I'd like to do it somewhat quicker and without the tedious boredom
that copy and paste entails. Any ideas please?

TIA

BigAl
 
BigAl said:
I'm correcting a database (hah! 1 table, 41 fields) and I've run into a bit
of a problem. One field is year commenced with just a 4 digit field. Another
field is date subs due which is a proper date field of dd/mm/yyyy.
What I need to do is copy over the dd/mm/ from the subs due field to the
start of the year commenced field so that they match - apart from years of
course. I could do it the long winded copy and paste but as it's 2,000
records I'd like to do it somewhat quicker and without the tedious boredom
that copy and paste entails. Any ideas please?
Click to expand...


Run an Update query:

UPDATE table
SET onefield = DateSerial(onefield,
Month(anotherfield),
Day(anotherfield))
WHERE onefield Is Not Null
AND anotherfield Is Not Null
AND Len(onefield) < 5
 
The solution depends on the data type of the [Year] Field.

If it is Integer or Long (likely), you are likely to need a new DateTime
Field in your Table. Once the Table is modified, you can use an Update
Query to create the values for this Field with an SQL String like:

UPDATE [YourTable]
SET [NewDateField] =
DateSerial([YearField], Month([Date Subs Due]), Day([Date Subs Due]))

I think if you have "29 February" with the non-leap year, Access will change
it to 01/Mar/non-leap year (Is "non-leap year" the right term?)
 
Van T. Dinh said:
The solution depends on the data type of the [Year] Field.

If it is Integer or Long (likely), you are likely to need a new DateTime
Field in your Table. Once the Table is modified, you can use an Update
Query to create the values for this Field with an SQL String like:

UPDATE [YourTable]
SET [NewDateField] =
DateSerial([YearField], Month([Date Subs Due]), Day([Date Subs Due]))

I think if you have "29 February" with the non-leap year, Access will
change it to 01/Mar/non-leap year (Is "non-leap year" the right term?)
Click to expand...

Cheers Van.
The year commenced foeld had been set to a 4 digit txt field (?!) but I
just changed that over and it worked a treat.

As a side point I don't know if you recall this but you helped me agreat
deal about 4 years ago creating an accounts/invoicing database which was
excellent once it was finished. Had it checked by a chartered accountant who
passed it A1 and was amazed when he saw it could even do age analysis if
required. Sad thing though is that the friend I created it for is such a
technophobe that he hunted out a second hand typewriter to do his invoices
with! The paper, enevelopes, software and the new company logo I designed
still reside at the back of my computer room gathering dust. :--( Still, I
learned a lot in the making of it.

Hope you and yours are well.

BigAl
 
KARL DEWEY said:
If I understand you - you want to take --
[year commenced] [date subs due]
2004 02/01/2004
2005 05/06/2005
and make it ---
[year commenced] [date subs due]
2/01/2004 02/01/2004
05/06/2005 05/06/2005

Is [year commenced] a text or number field?
Click to expand...

Not quite Karl

[Year Commenced] [Date Subs Due]

2001 02/01/2006
1995 26/05/2003


and variants thereof. What I wanted was to add the day and month to the Year
Commenced field thus allowing a proper table to be created for subscribtions
thus creating a subscription history - though true records would only begin
this year. As you'll see from my reply to Van his way worked just the way I
wanted (hadn't thought to create a new field).

Cheers

BigAl
 
Marshall Barton said:
Run an Update query:

UPDATE table
SET onefield = DateSerial(onefield,
Month(anotherfield),
Day(anotherfield))
WHERE onefield Is Not Null
AND anotherfield Is Not Null
AND Len(onefield) < 5
Click to expand...

Thanks Marsh and I'm sure that would have worked just as well as Van's did.
Appreciate the time taken and advice given.

BigAl
 
How are you, Al?

Yes, BigAl. I still remember you as I still feel guilty wasting perfectly
good software packages you sent me: they are still in shrink-wrapped boxes.
My health has not been good so besides necessary testing for my work, I
don't have a lot of opportunity to do extra learing / testing.
 
Van T. Dinh said:
How are you, Al?

Yes, BigAl. I still remember you as I still feel guilty wasting perfectly
good software packages you sent me: they are still in shrink-wrapped
boxes. My health has not been good so besides necessary testing for my
work, I don't have a lot of opportunity to do extra learing / testing.
Click to expand...

No need to feel guilty Van - they'd only be gathering dust on my shelves and
they're there if/when you have the time and or inclination.
Hope your health improves over the coming year.

Regards

BigAl
 
Back
Top