Date() as year only (1 Viewer)

statsman

Active member
Local time
Today, 08:24
Joined
Aug 22, 2004
Messages
2,088
I am creating a football (soccer) stats keeper for a pal. I have been away from Access for awhile, but it's coming back slowly.
The stats part has worked out OK, but the problems lie in in exporting the data from the stats keeper database to the archive database at the end of a season.
Access 2007.
1. Both the team and player stats are exported but there is no reference to the season. In Canada football is a spring to autumn sport so only four numbers representing the year are needed. Is there a method of breaking the Date() apart so only the year is recorded? Set the default value of a field as the year.
2. I have all the steps required for the transfer in a macro, but the SetWarning function doesn't seem to be there. When I run the macro there are multiple screen prompts requiring YES to be clicked to proceed. What happened to SetWarning? I do hope something has replaced it.
 

June7

AWF VIP
Local time
Today, 04:24
Joined
Mar 9, 2014
Messages
5,425
Year(Date())

I don't use macros, only VBA. However, SetWarnings is available in macro. Click "Show All Actions" on the ribbon.
 

Micron

AWF VIP
Local time
Today, 08:24
Joined
Oct 20, 2018
Messages
3,476
Where/how would you make us of just the year value? If in a form or report, the control can show just the year, or month, or day using the entire date in table field. If you need to see the year value because you're working directly with table data, then that's not so good.


I don't use macros but can't imagine set warnings was removed, or that you would need to select to show all actions (or whatever the option says) but maybe you do. Check that.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:24
Joined
Jan 20, 2009
Messages
12,849
The stats part has worked out OK, but the problems lie in in exporting the data from the stats keeper database to the archive database at the end of a season.

Are you sure you need to archive? I assume you are not working with millions of records.

It is very easy to keep all the data in place and adjust queries to only work with a specified year.
 

statsman

Active member
Local time
Today, 08:24
Joined
Aug 22, 2004
Messages
2,088
As I stated in the original post, the year is to keep track of the year the matches were played.
I plan to offer team and player stats by the year in the archive so this is just an archive matter.
I will be using a make table query in the main database to transfer the data to the archive. It is possible to add the year at that time with a prompt, but it's another potential mistake by the user I would like to avoid if possible.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:24
Joined
Jan 20, 2009
Messages
12,849
As I stated in the original post, the year is to keep track of the year the matches were played.

Presumably the matches also have a PlayedDate field. This is sufficient to know which year they were played without adding another field.

I plan to offer team and player stats by the year in the archive so this is just an archive matter.

This data could all be in the same table in the same database without the complexity of archiving to another database. It would also allow comparisons between the current and previous years.

You are making unnecessary complexity.
 

statsman

Active member
Local time
Today, 08:24
Joined
Aug 22, 2004
Messages
2,088
Are you sure you need to archive? I assume you are not working with millions of records.

It is very easy to keep all the data in place and adjust queries to only work with a specified year.

The league has been going for some time and will probably continue for some so an archive seems the best solution. There are several thousand records to be archived already.
However I will consider your suggestion.
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:24
Joined
Jan 20, 2009
Messages
12,849
The league has been going for some time and will probably continue for some so an archive seems the best solution. There are several thousand records to be archived already.
However I will consider your suggestion.

Access can easily hold hundreds of thousands of records. I have had Access databases with several million records and still performing well.

Databases are designed to be able to do this very efficiently. It is just a matter of appropriate indexing.
 

plog

Banishment Pending
Local time
Today, 07:24
Joined
May 11, 2011
Messages
11,613
The term solution implies a problem or an issue. What's the issue or problem you are trying to solve with an archive table?

I think the term "method" is probably more apt. And galaxiom's method is the correct way to store this data.
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:24
Joined
Sep 21, 2011
Messages
14,051
statsman;1627421 2. I have all the steps required for the transfer in a macro said:
If you click Show All Actions button you then see it.?
 

statsman

Active member
Local time
Today, 08:24
Joined
Aug 22, 2004
Messages
2,088
Thanks to everyone for your help and advice.
I have decided to follow Galaxiom's advice and just go with one database.
I feel kind of silly about the Set Warning bit. Chalk it up to my Access rust.
 

Users who are viewing this thread

Top Bottom