Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Table Development question

Posted on 2014-09-03
Medium Priority
174 Views
Experts, I am developing a table but need a little help.

I have :
ID
[Description]
[Type]
[WithinDays]
[Type]

I have many reporting requirements and they are either Quarterly, Semiannually, Annually and within a certain number of days past those points.  For example, an annual report would be within 120 days after years end and a quarterly is within 60 days past the quarter.

My question is, do I hard code [withindays] (ie 120, 60) as a field in a table?  Or is it better some other way (maybe within a query)

thank you
0
Question by:pdvsa
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 2

Accepted Solution

Priya Sudharsan earned 1000 total points
ID: 40302667
Do not hard code it. Have the data alone in the table and build queries as per the reporting criteria for each reports.
0

LVL 51

Assisted Solution

Gustav Brock earned 1000 total points
ID: 40302929
That would be an Integer. Then you can use DateAdd and DateDiff to find dates or calculate day passed relative to, say, quarter end or quarter start.

To identify various parts of a year, this function can be helpful:
``````Public Function DatePartYear( _
ByVal strInterval As String, _
ByVal datDate As Date) _
As Integer

' Returns the part of the year of datDate according to strInterval.
'
' 2008-02-07. Cactus Data ApS, CPH.

Const cMonthsInYear As Integer = 12

Dim intYearPart     As Integer
Dim intYearParts    As Integer

Select Case strInterval
Case "y", "m", "q"
' Day, Month, or Quarter of a year.
intYearPart = DatePart(strInterval, datDate)
Case "i"
' Dimidiae. Half part of a year.
intYearParts = 2
Case "t"
' Tertia. Third part of a year.
intYearParts = 3
Case "x"
' Sexta. Sixth part of a year.
intYearParts = 6
End Select

If intYearParts > 0 Then
intYearPart = -Int(-Month(datDate) / (cMonthsInYear / intYearParts))
End If

DatePartYear = intYearPart

End Function
``````
And to find, say, first or last date of a quarter, these can be used:
``````Public Function DateThisQuarterFirst( _
Optional ByVal datDateThisQuarter As Date) As Date

Const cintQuarterMonthCount   As Integer = 3

Dim intThisMonth              As Integer

If datDateThisQuarter = 0 Then
datDateThisQuarter = Date
End If
intThisMonth = (DatePart("q", datDateThisQuarter) - 1) * cintQuarterMonthCount

DateThisQuarterFirst = DateSerial(Year(datDateThisQuarter), intThisMonth + 1, 1)

End Function

Public Function DateThisQuarterLast( _
Optional ByVal datDateThisQuarter As Date) As Date

Const cintQuarterMonthCount   As Integer = 3

Dim intThisMonth              As Integer

If datDateThisQuarter = 0 Then
datDateThisQuarter = Date
End If
intThisMonth = DatePart("q", datDateThisQuarter) * cintQuarterMonthCount

DateThisQuarterLast = DateSerial(Year(datDateThisQuarter), intThisMonth + 1, 0)

End Function
``````
Just examples.

/gustav
0

Author Closing Comment

ID: 40303216
thank you.  I plan to use those functions so I think a split is acceptable. Let me know if there is an objection.

thanks again.
0

Author Comment

ID: 40303471
Gustav:  could you give me a quick example of how I would use those functions?  Meaning I would first identify the end of quarter with the first function and then use DateADD to add X days?  I guess I would call the function in the query design window.  thank you for your help.  I am glad you responded.  I remember you are excellent with dates and technical questions.  I am a little rusty as have not worked in Access in some time.
0

LVL 51

Expert Comment

ID: 40303573

"When is the next reports ultimo this quarter?"

datNextThisQuarter = DateThisQuarterLast(Date)

"What is the deadline of this? If within 30 days:

intMaxDelay = 30

But there are, of course, many variations over this.

/gustav
0

Author Comment

ID: 40303938
very nice.  thank you.  I will be playing around with this.  thanks again for the codes.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
###### Suggested Courses
Course of the Month9 days, 7 hours left to enroll