Calender date to Month Code

[Guest]

I would like to set up a Table that will code a date to a sales Mo for me.
The issue is that we do not use a calendar date. I hope I can explain this.


Calendar Date Sales Mo
13 Sep 05 – 6 Oct 05 Oct 06
7 Oct 05 – 14 Nov 05 Nov 06
Etc.

I would like to set up a Calendar Date table that would have the following:
Start Date End Date Sales Mo
13 Sep 05 6 Oct 05 Oct 06

This table would be used to update my Sales Mo code so when the user adds a
new record and the Sales Calendar date for the sale is put into the Sales
date would somehow code that sales Mo.

I do not know what would be the bet way to do this.

Please help.

 

[Jeff Boyce]

I'd recommend against creating a table for this. Based on what you
described, would it be correct to say that the [SalesMonth] value you wish
to see is the same month as the month of the end-date, but one year later?

If you are saying you currently have a (?text) field that contains, for
example:
"13 Sep 05 - 6 Oct 05"
you can use a query to get the part before the dash ("-"), the part after
the dash, and convert these to actual dates.

Then you could use Month([EndDate]) and Year([EndDate]) + 1 to calculate
your [SalesMonth].

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

when the date gets to Jan it will be the same Year it is like a FY.

I'd recommend against creating a table for this. Based on what you
described, would it be correct to say that the [SalesMonth] value you wish
to see is the same month as the month of the end-date, but one year later?

If you are saying you currently have a (?text) field that contains, for
example:
"13 Sep 05 - 6 Oct 05"
you can use a query to get the part before the dash ("-"), the part after
the dash, and convert these to actual dates.

Then you could use Month([EndDate]) and Year([EndDate]) + 1 to calculate
your [SalesMonth].

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like to set up a Table that will code a date to a sales Mo for me.
The issue is that we do not use a calendar date. I hope I can explain
this.


Calendar Date Sales Mo
13 Sep 05 - 6 Oct 05 Oct 06
7 Oct 05 - 14 Nov 05 Nov 06
Etc.

I would like to set up a Calendar Date table that would have the
following:
Start Date End Date Sales Mo
13 Sep 05 6 Oct 05 Oct 06

This table would be used to update my Sales Mo code so when the user adds
a
new record and the Sales Calendar date for the sale is put into the Sales
date would somehow code that sales Mo.

I do not know what would be the bet way to do this.

Please help.

 

[e.mel]

FY?

Code like this should do the trick in seperating the dates out
but this may not be the best solution if the date ranges change from
year to year - you'd have to continually add cases


Select Case (Format(calDate, "dd mmm yy"))
Case CDate("13 Sep 05") To CDate("6 Oct 05")
salesMo = "Oct 06"
Case CDate("7 Oct 05") To CDate("14 Nov 05")
salesMo = "Nov 06"
'etc
End Select

For the table idea you could have a table with the begining date of
each period and the matching sales month code, then use something like:

salesMo = DMin("salesMonth", "table name", "salesMonth < #" & calDate &
"#")

(probably needs some tweaking)

when the date gets to Jan it will be the same Year it is like a FY.

I'd recommend against creating a table for this. Based on what you
described, would it be correct to say that the [SalesMonth] value you wish
to see is the same month as the month of the end-date, but one year later?

If you are saying you currently have a (?text) field that contains, for
example:
"13 Sep 05 - 6 Oct 05"
you can use a query to get the part before the dash ("-"), the part after
the dash, and convert these to actual dates.

Then you could use Month([EndDate]) and Year([EndDate]) + 1 to calculate
your [SalesMonth].

Regards

Jeff Boyce
Microsoft Office/Access MVP

I would like to set up a Table that will code a date to a sales Mo for me.
The issue is that we do not use a calendar date. I hope I can explain
this.


Calendar Date Sales Mo
13 Sep 05 - 6 Oct 05 Oct 06
7 Oct 05 - 14 Nov 05 Nov 06
Etc.

I would like to set up a Calendar Date table that would have the
following:
Start Date End Date Sales Mo
13 Sep 05 6 Oct 05 Oct 06

This table would be used to update my Sales Mo code so when the user adds
a
new record and the Sales Calendar date for the sale is put into the Sales
date would somehow code that sales Mo.

I do not know what would be the bet way to do this.

Please help.

 

[e.mel]

yup . . . definitely needs some tweaking

salesMo = DMax("salesMonth", "table name", "startDate <= #" & calDate &
"#")

this will return the salesMonth associated with the latest startDate
that occured on or before the calendar date. The #'s may or may not
be needed.
Or maybe it will return the last of the salesMonth (sorted
alphabetically) associated with any startDate that occured on or before
the calendar date.

.. . . needs testing

FY?

Code like this should do the trick in seperating the dates out
but this may not be the best solution if the date ranges change from
year to year - you'd have to continually add cases


Select Case (Format(calDate, "dd mmm yy"))
Case CDate("13 Sep 05") To CDate("6 Oct 05")
salesMo = "Oct 06"
Case CDate("7 Oct 05") To CDate("14 Nov 05")
salesMo = "Nov 06"
'etc
End Select

For the table idea you could have a table with the begining date of
each period and the matching sales month code, then use something like:

salesMo = DMin("salesMonth", "table name", "salesMonth < #" & calDate &
"#")

(probably needs some tweaking)

when the date gets to Jan it will be the same Year it is like a FY.

I'd recommend against creating a table for this. Based on what you
described, would it be correct to say that the [SalesMonth] value you wish
to see is the same month as the month of the end-date, but one year later?

If you are saying you currently have a (?text) field that contains, for
example:
"13 Sep 05 - 6 Oct 05"
you can use a query to get the part before the dash ("-"), the part after
the dash, and convert these to actual dates.

Then you could use Month([EndDate]) and Year([EndDate]) + 1 to calculate
your [SalesMonth].

Regards

Jeff Boyce
Microsoft Office/Access MVP


I would like to set up a Table that will code a date to a sales Mo for me.
The issue is that we do not use a calendar date. I hope I can explain
this.


Calendar Date Sales Mo
13 Sep 05 - 6 Oct 05 Oct 06
7 Oct 05 - 14 Nov 05 Nov 06
Etc.

I would like to set up a Calendar Date table that would have the
following:
Start Date End Date Sales Mo
13 Sep 05 6 Oct 05 Oct 06

This table would be used to update my Sales Mo code so when the user adds
a
new record and the Sales Calendar date for the sale is put into the Sales
date would somehow code that sales Mo.

I do not know what would be the bet way to do this.

Please help.

 

[Guest]

I would suggest you change the format of your calendar table so that you have
just the closing date of the period and it should be the primary key. I
would also suggest you not put the month and day in one field (Sales Mo). I
would suggest they be separate fields (without spaces in the names, because
that is bad naming).
SalesYr SalesMo
To find what period you are in, you can use a routine like this.

Set rst = CurrentDb.OpenRecordset("tblCalendar", dbOpenDynaset)
rst.FindFirst "[PeriodEndDate] > #" & Me.txtSomeDate & "#"
If rst.NoMatch Then
MsgBox "Error Find Date"
Else
Me.txtSalesMo = rst![SalesMo]
Me.txtSalesYr = rst![SalesYr]
End If
Set rst = Nothing

What this does is find the first date in the table that is greater than the
date you want to find the period for.