Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem with dates

Status
Not open for further replies.

TSSTechie

Technical User
May 21, 2003
353
GB
Howdy,

Not sure if this should go in this forum or the reports one but here goes.

I am designing a training records database and one of the things that needs to be recorded is the date a course was completed. These should then be displayed in date order on a report. The problem i've got is that the dates I have been given from the current paper based record system are in 3 different formats. They are either 'short date', 'medium date' or just the year. Is it possible to store these values as one of the 3 formats in one field ??
At the moment i've got another field as autonumber. The records are sorted on this field but this means that the training records must be entered into the database in date order starting with the earliest so they appear in the correct order on the report. As i'm sure you can imaging, this is far from ideal.

Hope someone can offer some assistance with this matter. I'm really looking forward to getting this database completed and I think this is going to be my last big hurdle.

Thanks in advance

TSSTechie

[lightsaber] May The Force Be With You [trooper] [yoda]
 
Make the field with just the year a valid date. You could convert year to "01/01/" + year before loading the records.
 
Hi

In the table define the TrainingDate as a date/time type

The format is the visual representation of the date, but what is actually stored is always the same (actually a count of the number of days since some date in the past).

You can show the date in reports and forms as any format you wish by using the .format proeprty of the relevant control

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Howdy,

Firstly I made a slight error in my original post. I incorrectly said "medium date". What I meant was the format "mmm yyyy" (i.e. no day). Sorry for the confusion

I can't set the report to one format. The report generated could be required to use any or all of the 3 formats for the date field. I.e. if the full date is known, the format "dd/mm/yy" shall be used, if only the montha nd year are known then the format "mmm yyyy" shall be used otherwise, if only the year is know then the format "yyyy" shall be used. As I say, these could all be needed on one report.

Also, where the day and/or month is available, these will need to be considered when sorting so storing only the year as a valid date is not really an option.

Thanks for the advise so far guys. Any other suggestions would be greatly appreciated.

TSSTechie

[lightsaber] May The Force Be With You [trooper] [yoda]
 
Then I would have CompletedDay, CompletedMonth and CompletedYear fields instead of a single date field. You could then concantenate the information together to get exactly what you need.

leslie
 
Hi

While I would agree with lepaul if the lack of completeness of the date was to be an ongoing problem, my understanding was that this was a problem only in the 'legacy' data, and that all 'new' data entered via your system would be complete. On that basis I can see little justification for continuing a bad practice into the new system. As a good friend of mine always says "it is never easier to do it wrong".

If that is so, I still stick with my original point that you should store the date in the table as a date/time type. I would extend the table definition to include two yes/no columns blnDayUnknown, and blnMonthUnknown, both default value false. For 'legacy' data where (say) the day is not known, I would force in (say) 15, but set the column blnDayUnknown to true. Then in the report (or query on which it is based) you can present the date value as dd/mmm/yyyy or mmmm/yyyy or yyyy depending on the settings of blnDAyUnknown and blnMonthUnknown, thus covering all bases, but you still have the advantage of a true date field for any future sorting etc.

You say the existing system is 'paper based' does this mean that a (manual) data entry exercise must be mounted to get the 'legacy' data into the system?




Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Howdy,

Unfortunately this is not just a problem with legacy data. When a new member of staff is employed they may well bring with them qualifications that will need to be entered into the database. This may have the same problem eg. just a month and year is known as opposed to the full date.

The storing of the information in seperate field then concatenating the data sounds good. Wonder if you might be able to provide a little further help on this. How is it possible to enter just the day or just the month or just the year into a field? It always seems to require a full date to be entered [sad]. Also, how can I concatenate the information so it looks like a proper date? I think I should be able to do the sorting if I use the 3 seperate fields (hidden) and sort on each of these in turn. Does this sound like it would work or is there a better way?

Thanks again for all help given

TSSTechie

[lightsaber] May The Force Be With You [trooper] [yoda]
 
Also, I will be entering all of the legacy information into the database before handing it over to 'Go Live' as it were.

[lightsaber] May The Force Be With You [trooper] [yoda]
 
I still stick with my original point that you should store the date in the table as a date/time type. I would extend the table definition to include two yes/no columns blnDayUnknown, and blnMonthUnknown, both default value false. For 'legacy' data where (say) the day is not known, I would force in (say) 15, but set the column blnDayUnknown to true. Then in the report (or query on which it is based) you can present the date value as dd/mmm/yyyy or mmmm/yyyy or yyyy depending on the settings of blnDAyUnknown and blnMonthUnknown, thus covering all bases, but you still have the advantage of a true date field for any future sorting etc.



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Howdy,

Ok then, if I do it that way. I would like the user to be able to simply input what they do know of the date. I would then like the database to fill in the missing bits with 15 or whatever. Also I would like the database to fill in those 2 extra fields itself so, if it needs only to force in a day then the first box becomes true and if it needs to force in both a day and a month then both boxes become true.

Is this possible ??

Thanks again

TSSTechie

[lightsaber] May The Force Be With You [trooper] [yoda]
 
Howdy

Well to be honest, I haven't got the foggiest clue where to even start with this. I've got very little knowledge of VBA (which I assume will be required here) so as much detail as possible would be greatly appreciated.

Thanks very much

TSSTechie

[lightsaber] May The Force Be With You [trooper] [yoda]
 
Hi

OK, if you can wait a few hours I will be back to you with some code etc. It might be helpful if you EMailed me at kenneth dor reay at talk21 dot com, and I will also send what I post here directly to you, with sample

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Howdy

Thanks a lot Ken, e-mail sent as requested.

TSSTechie

[lightsaber] May The Force Be With You [trooper] [yoda]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top