Find Related Period for Entered Date Function

  • Thread starter Thread starter Luke
  • Start date Start date
L

Luke

I need to find a period based on submitted date. Here is
an example

Period Table
Period Start_Date End_Date
2 1.15.2004 1.31.2004
3 2.1.2004 2.14.2004
4 2.15.2004 2.29.2004

If the date is 1.22.2004, Period 2 should be selected, if
the date is 2.28.2004, period 4 should be selected, and
so on.

I would like to create a function that uses the date and
kicks out the period, something simple like DateToPeriod
(<EnterDate>)

Any help or resource to check is greatly appreciated.

Thank you.

Luke
 
Luke,
"Why does US have to use non international format for dates and non metric
units??"

The period Start date and end date should be in separate fields so that the
data is atomic and held as the correct data type ie Date.

Table = tblPeriodTable
Fields :
Period type=Integer
DatePeriodStart=TimeDate
DatePeriodEnd=TimeDate

Then you can do a comparison

Dim db as DAO.Database, rs As DAO.Recordset
Dim intPeriod as Integer, stSql as string

stSql= "Select Period From tblPeriodTable " & _
"Where (#" & datSelectDate & "# >= DatePeriodStart " & _
"And #" & datSelectDate & "# <= DatePeriodEnd);"
Set db=CurrentDB()
Set rs=db.Openrecordset(stSql, dbopensnapshot)
With rs
if .EOF then
intPeriod=0
else
..Movefirst
intPeriod=!Period
end if
End with
Set rs=nothing
Set db=nothing

You can build a function to pull the data to peices in the format that you
have now but it would be quite a bit more complex.
 
"Why does US have to use non international format for dates and non
metric units??"
....

stSql= "Select Period From tblPeriodTable " & _
"Where (#" & datSelectDate & "# >= DatePeriodStart " & _
"And #" & datSelectDate & "# <= DatePeriodEnd);"
Click to expand...

If you are going to complain about the difficulties of managing date-to-
text conversions in international settings, then you should at least defend
against it in your code, because the above will only work successfully
where USian dates are used.

stSQL = "SELECT Period FROM tblPeriodTable " & _
"WHERE DatePeriodStart <= " & _
Format$(datSelectDate,"\#yyyy\-mm\-dd\#") & _
" AND " & Format$(datSelectDate,"\#yyyy\-mm\-dd\#") & _
" <= DatePeriodEnd"




Hope that helps

Tim F
 
Thank you for your reply.

The dates are in separate fields (I thought that was
clear from the example) and as such I have no idea what
you talking about when you write: "use non international
format for dates and non metric units?".

Plus this needs to be a function so users can plug in the
date.

Thanks again.

Luke
 
Back
Top