Standard way to deal with non standard date formats?

[Sharky II]

Hi guys

I've created a simple finance/transaction database. It has a 'Transaction' and a 'TransactionItem'. Once all the TransactionItem OracleItemClearDate fields are filled in, the Transaction OracleDateClear field is filled in automatically (But this can be manually edited later).

I have now been told that this field needs to just be month/year (mm/yy). I understand that i can simply set the format to mm/yy (or mmmm/yyyy), however this seems to simply be a 'mask' for the real, specific date value. The date picker still shows a specific calendar date. How can i make this really a month/year? I also am not sure how this may 'break' my date search code, in the dynamic query.

There is also a 'FinanceReportDate' on the main 'Transaction' table. I've now been told that this needs to be 'by the quarter', so 'Q1/2011', etc. This again, needs to be searchable. If simply make it a string, then my search mechanism which searches between two date ranges will not work (i am using a dynamic query search). I have attached a sample of what my date search code looks like, for standard dates, below.

Does anyone have any advice on how to integrate these kinds of date fields - i am sure they must have come up before in the past. It would be great if access simply 'understood' them!

Many thanks!

Eddie

'*************************
'SrchEntryDate Start/End *
'*************************

' Note the number signs (#) surrounding the date field [Order Date].
If Not IsNull(Me![SrchEntryDateEnd]) Then
    If Not IsNull(Me![SrchEntryDateStart]) Then
        where = where & " AND [EntryDate] between #" + _
        Format(Me![SrchEntryDateStart], "mm/dd/yyyy") + "# AND #" & Format(Me![SrchEntryDateEnd], "mm/dd/yyyy") _
        & "#"
    Else
        where = where
    End If
End If

 

[Sharky II]

Hi guys, anyone able to help?

If this is a common problem that everyone's tired of dealing with - please just point me in the right direction!

Thanks again

Eddie

 

[Galaxiom]

Best to keep the data in the table as dates. This is the most efficient by far. MM/DD and Q/YYYY are not very nice formats to index and search.

Use an unbound textbox for entry and display and VBA to convert.
Use the OnCurrent Event to load the value to the unbound control.
Use the AfterUpdate Event of the unbound control to convert the changed value.

Use the NewRecord property to test for data being entred and the Form_BeforeUpdate procedure to convert and load to the table.

Name the unbound controls and field in the bound form's recordsource differently and you can refer to either of them exactly as you would to a control.

 

[Sharky II]

Hi there!

Thank you for your post. My only problem is turning your directions into reality - i'm not quite sharp enough on my VBA to write this from scratch. Could anyone lend a hand on how to get me started? I'd appreciate any help at all!

At the moment i've converted it to mm/yy (for example) in terms of how it's displayed in the forms, but this seems to just be a 'mask' for a real date value.

I don't really mind using a real date, as long as that's transparent to the user.

Any advice would be great!

Thank you

 

[Galaxiom]

Here is a "rough as guts" sample database showing the basic technique of translating values between an unbound control and a field in a bound recordsource.

Just browse through the records with the record navigation at the bottom of the form.

It can manage view and edit without the problem encountered using the Format property of a bound control where the value reverts to the native datatype when the control has focus as you noted earlier.

However this technique only works on Single Form mode because the control is not bound. All records will show the current value for the unbound control in Continuous Forms or Datasheet mode.

It accepts the month as an integer so the leading zeros are optional though it will always display them after input.

The year uses the system setting for determining the century so can be entered as YY or YYYY but will always display YYYY.

You will need to do some further entry validation since invalid months will be accepted. The user can enter 13/11 and Access will convert this to 01/2012.

You could call this a feature . Otherwise it would be relatively easy to add to the required code in the AfterUpdate Procedure.

 

[Sharky II]

Thanks so much for this! At a glance, it seems to work OK for continuous forms? That's how i'm going to be using it, inside a continuous (sub) form displaying a list of items that pertain to the main record.

I'm going to have a play now!

 

[Galaxiom]

Definitely won't work properly for display with Continuous Forms. Technically it will work in that it saves the correct values and the current record will be displayed correctly. But if you look closely you will see all the values are the same as the current record in that control.

With your subform in Continuous Forms mode just use a bound control and the Format property as you did before. Disable edits on the subformcontrol so it is only used for display. Lock and disable the date control on the form so the user will not be able to make it turn into a full date.

Then use the DoubleClick event in the controls on the subform itself to open a Popup Single Form that has this translation facility. This will allow you to edit the control value. You can then continue through the rest of the records in that form (or even a New Record) if you want but it definitely needs to be a Single Form.

Include a button for new record on the main form which also opens a translating popup form in Single Form mode at the New Record.

 

[Galaxiom]

For the sake of those who come across this thread in the future and wonder about why one might go to this trouble when the MM/YYYY string could be stored instead. Obviously this is would be much simpler for the display and entry.

However it does not work so well with querying. You can't simply use Between 01/2009 And 03/2009 for example because the string is ordered alphanumerically.

If the string was stored as yyyy/mm then it would be possible but mm/yyyy is a real problem.

One might consider this workaround if the number of records was relatively small:

Right([fieldname],4) & / & Left([fieldname,2] BETWEEN Format(somedate,"yyyy\/mm" AND Format(anotherdate,"yyyy\/mm")

However this solution relies on every record in the table being processed to determine if it is in the range. The index on the mm/yyyy field cannot be used.

Consequently it is very much slower than simply using a Between two dates which can efficiently determine the matching records directly from the field's index without processing the values in the record. On a large table this can be a hundred times faster than the previous alternative.

I would expect even compared to the yyyy/mm (which can use an index), the date query is going to be faster because it is simply comparing Double datatype numbers. However I have not tested this assumption.

 

[Sharky II]

Thanks so much!

I'm going to have a 'meeting' with the folk who will be using it and show them how it is currently (with the dates as real/full dates in the tables, and the mm/yy or qq/yy for the format on the form fields). Most of the time they will be simply using the date picker and selecting 'today', other than when entering old records to get this new database up to speed.

Perhaps they would be comfortable selecting the first of the month of each quarter with the date picker... it's not exactly super slick, though. I guess they will want to be able to enter '08/2011' or '08/11' for this month, but we'll see. If they are not OK with how it functions, i will have to go back to the drawing board and implement the suggestions you've put here.

They will not have more than a few thousand records (probably under 10k, and because it's a new DB, it will certainly take some time to get up to that number) which will be stored on a networked drive.

Thanks again, i'll post again soon

Eddie

 

[Galaxiom]

You could make your own month/quarter picker using a small popup form. That would be slick. But data entry operators often want a keyboard solution so they don't have to move their hand to the mouse.

A simple alternative would be to use a combobox to choose. Or two combos, one for month and one for year. With the right defaults they would probably rarely have to choose a year selection anyway.

Combos with a well ordered rowsource and autocomplete are fast because they could type a single character and get the reset of the entry they wanted at the top of the list modt of the time.

Another alternative is to store year and month separately. The query can work with Between on two fields. Note however you should not store quarter as this can be derived from the date or year and month so storing would breach normalization.

With only 10K records the query workaround with string storage should be fine.