New column or alter table?

[lution]

I've got a database that's been in use for 10 years and there's ~ 40 copies of it out there now. I have dtmInitialAppearanceDate in the database already and have it stored as short date in the column definition. Some newer customers are asking that I now also store the initial appearance time. As I see it, I have two choices:

1. Use VBA to issue an alter table from the front end to the back end to change the format for dtmInitialAppearanceDate from short date to general date and include the date and time in one column.

2. Use VBA to add a new column dtmInitialAppearanceTime and set its format to only include the time portion.

Any modifications to the back end from the front end is going to need to be done via VBA and there is a non-unique index on the field already which may make doing an alter table harder?

Any recommendations? Storage space isn't an issue yet. Largest customer database is only up to 76MB. If I add a new column, existing queries can be left alone and I can just modify the ones that need to account for the time. What other things should I consider when weighing the decision? If the decision is to go with #1, then I'll need help with the best way to alter the table definition.

Thanks
Lution

 

[spikepl]

Errr ... why are any changes in the table necessary at all? Do ppl view tables directly? Formatting does not affect the contents, so you can store the time element in the same column as the date. All you need is to change the formats in the controls of the user interface.

 

[Pat Hartman]

If your back end is Jet/ACE then the Date datatype allows for the entry of time also. So, as Spikepl mentioned - no table change is required. To store time as well as date, you'd need to change any code or query where you use Date() to Now(). You could write a VBA procedure to open all forms and reports and look for textboxes with a date format and change the format to include time. The big problem will be adjusting th size of the field since it will require more than twice its original width to show time as well as date.

 

[gemma-the-husky]

however bear in mind that if a date includes a time element then a comparison such as

<= somedate, will fail, if somedate has a time element

ie

<=somedate checks for 12.00 midnight (start of the day) - and a time will mean the date is greater than the reference time

this will also affect things like

between somedate and someotherdate

 

[lution]

Reason I thought I needed to change was because the following query on the table only displays the date portion:

SELECT DISTINCT tblCases.dtmCaseFirstAppearanceDate
FROM tblCases
ORDER BY tblCases.dtmCaseFirstAppearanceDate DESC;

I use this query to get a distinct list of first appearance dates and now want it to include the time portion. Tried adding format(tblcases.dtmCaseFirstAppearanceDate, "General Date") to the query but it still just shows the date and no time. If I change it to:


SELECT DISTINCT Format([dtmCaseFirstAppearanceDate],"mm/dd/yyyy hh:nn am/pm") AS DocketDate
FROM tblCases
ORDER BY Format([dtmCaseFirstAppearanceDate],"mm/dd/yyyy hh:nn am/pm") DESC;

It treats the results like a string and the sort desc doesn't sort in decending date format but by character so 12/31/2009 comes before 05/06/2012.

 

[spikepl]

Format (set for some field) is not an inherent property of a value. Values do not have properties - fields do. Format is just the recipe for how to display the value held in a field in a human-readable format.

The function FORMAT does not do anything to a value. It produces a string out of the given value according to whatever formatting parameters were specified, and leaves the value unaffected. So your last query doesn't "treat the results like string" but simply shows exactly what you asked of it: strings!

What your first query shows is immaterial - you should set formatting in the controls of the user interface. Your query just inherits the format setting from the table. To convince yourself , just make some query and a form based on it, and set the formatting to general date in the controls there. All that providing there indeed is a time part stored in the date field. Or, in the query designer, in the property box of that field, set the format of that field to your liking.

 

[lution]

I setup a sample database with a single table (Table1). In the table there is an ID column and then two date columns which are both typed:

dtmMyShortDate - datetime, format "Short Date"
dtmMyGeneralDate - datetime, format "General Date"

I created a datasheet form to be able to view the data in the table with the data displayed in General Date format for both columns to confirm that time is stored in both columns.

I also created a second form called Form1that has a text box Date and control called Add that can be used to add new rows to the table. Then there are two combo boxes: Short dates, General dates. Both combo boxes are set to General Date format. One queries the dtmMyShortDate column for values, the other dtmMyGeneralDate.

If you select the Short Dates combo, you'll see that it isn't displaying the time portion even though the field is set to General date. This is similar to how my existing database is setup. Need to find a way so that I see the time in a combo even though the column is defined as Short Date.

Thanks

 

[spikepl]

You have fallen into the previously mentioned trap of inherited format. When RowSource is stuffed with data, the data is converted to strings, so you lost the time element, because the query based on the SQL-statement uses the format property of the table to do the conversion.

I have made a new SAVED query, changed the format property of the column to GeneralDate and based the short-date combo on that query. It displays also the time element of your short-date formatted table field just fine.

Pay attention to which format property affects what. The format property of the combo does indeed affect how data is displayed as control value (but not in the RowSource - i.e. the drop down list of STRINGS), provided that the data is there in the first place. The time element was ripped of. The format of the query affects how the query's data are converted into the strings stuffed into the row source of the combo.

 

[spikepl]

If your problems are related to combos then you could use parts of the query you showed in #5

SELECT DISTINCT Format([dtmCaseFirstAppearanceDate],"mm/dd/yyyy hh:nn am/pm") AS DocketDate
FROM tblCases
ORDER BY dtmCaseFirstAppearanceDate DESC;

This would work just as well. The sort would then be not on a string but the actual date value (with or without the time element - this is unaffected by the format of the column in the table).

 

[lution]

Thanks Spikepl. That's the connection I was looking for. In the attached database, changed the data source for the Short date combo to:

SELECT DISTINCT Format(Table1.dtmMyShortDate,"General Date") AS DocketDate, Table1.dtmMyShortDate
FROM Table1
ORDER BY Table1.dtmMyShortDate DESC;

and got what I was looking for.

-Lution