Quarter End date: Enter as Short Text or Date/Time? And how to Validate?

[dswift]

I would like to enter a column in a table where the data should look like a year and a quarter end. For example, I would like to see 2018 Q1 or 2018 Q2...etc. Or I am okay with using actual months but then it must only be quarter end dates (3, 6, 9, 12).

1) Is it best to use a date/time or short text data type?
2) what is the best way to restrict entry so that any (current or future) year can be entered, but restrict the dates to either quarter end (so, 3/31, 6/30, 9/30 or 12/31) or Q1, Q2, Q3 Q4?

Right now I am using short text with an input mask = "201"0" Q"0. However this would allow the input of any number after Q, and it should really only allow 1, 2 3 or 4. When I try to input a validation rule here I have tried #### Q1 or #### Q2 or #### Q3 or #### Q4. I get an error message "The expression you entered has an invalid date value" even though I am using short text and not the date/time data type.

Thank you!

 

[Fazza]

hello

How about if there is another worksheet with a dedicated table in successive cells: 2018 Q1, 2018 Q2, 2018 Q3, 2018 Q4, 2019 Q1, etc

Give it a defined name, like 'tblQuarterEnding', and use that as data validation on the cells?

regards

 

[dswift]

Hmm, that could work too. It would probably work better than what I have now. The only downside being that I would have to continuously update it each quarter to add new dates

 

[xenou]

In general I would find it highly preferable to use real dates. If Q1 ends on 4/30 then use 4/30/yyyy. This allows you to do true date comparisons on date values throughout your database.

You could however still store quarter and year but in that case I'd want to have them as integers and stored in two data point - fiscal year, fiscal quarter. Now you can do yearly reporting, year over year comparisons, quarter over quarter comparisons, easily create true date values from the year and quarter, and probably more good things as well. This is what I've seen in the two enterprise financial databases that I've used in work environments (they stored financial transactions with a year, quarter, and transaction date (at least - sometimes other dates (such as invoice dates or received dates). That way there was always a single "true value" about what quarter a transaction is reported in, regardless of other dates involved. Often there would also be a monthly close routine that switches the system to the new quarter.

As far as having to continuously update the quarters, it isn't uncommon in financial databases to have yearly "maintenance" required for fiscal years. This is mainly because companies have different fiscal calendars and so no financial system can assume that Q1 is Jan-Mar, Q2 is Apr-Jun, etc.

That said, you should be easily able to create such data quite a few years ahead in advance. I like to use Excel to do so when i need to generate lots of data points, since a few formulas do the trick in seconds. Then import to Access.

 

[dswift]

So, are you suggesting a separate table with two columns: Year (YYYY) and Quarter (Q1, Q2, Q3, Q4). Then pulling in both of those columns into the original table that I was trying to make?

 

[xenou]

That would be my preference, but not the only way to go.
If you did store string data, it will also work if the data is stored consistent:
2014-Q1
2015-Q2
2018-Q1
etc.

Or
2014-01
2015-02
2018-01

Or something to that effect (which is also pretty easy to parse out as a true date if necessary).

This is sortable and comparable in the expected order (year, then quarter).
In that case the only problem was the proper validation you were trying to find earlier.

As used to be said, there are many roads to Rome.

 

[xenou]

Note that here would be the original validation rule you were looking for, I believe (4 numbers, a space, a "Q" and finally a number from 1-4.

Like "[0-9][0-9][0-9][0-9] Q[1-4]"