Split dates into seasons? 1

[lind77]

Hi

My problem is as follows: I need to assign seasons to dates. To do this I have 2 tables: one with a list of all dates from 1/103 to 31/12/10 (not done by hand!)and one in which the beginning and end date of the season (summer/winter) are listed. I need to combine these two somehow so that all dates also state their seasons, ans are also automatically updated if the beginning/end dates arew altered. I need to do this for scheduling purposes. I have been fiddling with queries and a bit of VBA code but can't seem to achieve this. Any ideas?

Thanks

Mark

 

[jimb0ne]

lind77,
You could just use VBA and check the date that you need the season for against the start/end dates of the seasons. The easiest way to do this would be to use the Month() and Day() functions on the dates. These will return only the number of the month and the number of the day for the dates you pass them.

If I have the date 08/25/1999 and the start of winter is set for 09/15 then its easy enough to say:

If Month(CurrentDate) < month(WinterStartDate) then
month is summer
elseif month(currentdate) > month(winterstartdate) then
month is winter
else
if day(currentdate) >= day(winterstartdate)
month is winter
else
month is summer
end if

hope this helps.

 

[hneal98]

Can you give an example of what you have in each table?

 

[hneal98]

If you already have the info you say in tables, you should be able to do this without VBA.

 

[SgtJarrow]

I went the same as jimb0ne...

Assumptions:
tblSeasons
AutoId Autonumber (pk)
Season Text
StartDate Date
EndDate Date

Make sure vba reference to Microsoft DAO 3.x Objects is checked

Paste the following into a new module:

' *********************

Public Function DetermineSeason(dteDate As Date) As String

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset(&quot;tblSeasons&quot;, dbOpenSnapshot)

With rs
.MoveFirst
Do Until .EOF
If dteDate >= .Fields(&quot;StartDate&quot And dteDate <= .Fields(&quot;EndDate&quot Then
DetermineSeason = .Fields(&quot;Season&quot
Exit Do
End If
.MoveNext
Loop
End With

End Function
' *******************

And then anywhere you want to determine the season, just use:

TheSeason = DetermineSeason(place date here)

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[MichaelRed]

perhaps I'm just stubborn, but why not use the db engine in it's intended role?

MySeasons Table:
Season dtSt dtEnd
Winter 12/22/2000 12/31/2001
Spring 3/22/2001 6/21/2001
Summer 6/22/2001 9/21/2001
Fall 9/22/2001 12/21/2001
Winter 1/1/2001 3/21/2001

The query SQL:

UPDATE tblDtSeason, tblSeasons SET tblDtSeason.MySeason = [tblSeasons].[Season]
WHERE ((Format([tblDtSeason].[MyDt],"mm/dd") Between Format([tblSeasons].[dtSt],"mm/dd") And Format([tblSeasons].[dtEnd],"mm/dd")))
WITH OWNERACCESS OPTION;

All table and field names are included in the query. Alter to suit.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[SgtJarrow]

Michael,

I gathered from the initial post that this needed to be fairly dynamic...as stated it needs to change if the start and end dates change. I am not sure on yours, but my solution will allow for immediate visible change if the start and end dates are changed in the seasons table. And it can be used anywhere and does not require any new fields in the tables expects the ones already described in the initial post.

But as the original poster has not responded to any of our sugestions at this time, we have to wait and see if anything we have suggested has been any help.

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[MichaelRed]

Robert,

I haven't really checked exhaustively, but did generate a table with two years worth of dates and the query does fill in all of the 'seasons', and looks (at a glance) to be correct. Since it is a query aginst two tables, w/o intervening steps or operations, I would expect no other isues. Of course it will, by the very nature of the query action update all 'seasons' in the target table when ever it is run. Obviously, one needs to be aware of the need for an execution, wheather dates are added to the target table or alterations made to the seasons table.

You soloution (and code in general) is not my preference for updating numerous entries in a recordset. I have never been able to generate a code soloution to multiple updates which is faster than an SQL soloution, and have even attemppted to do so under 'test; conditions. Even using code, I would think it would be faster (and conceptually easier) to do a lok up (find or seek) than the loop to obtain the season for a single date.

e.g.

Public Function basGetSeas(dteDate As Date) As String

    Dim rs As DAO.Recordset
    Dim strCrit As String
    Dim Quo As String * 1
    Dim Pnd As String * 1

    Quo = Chr(34)
    Pnd = Chr(35)

    Set rs = CurrentDb.OpenRecordset("tblSeasons", dbOpenSnapshot)

    strCrit = "Format(" & Pnd & dteDate & Pnd & ", " & Quo & "mm/dd" & Quo & ") " & _
              "Between " & _
              "Format([dtSt], " & Quo & "mm/dd" & Quo & ") " & _
              "AND " & _
              "Format([dtEnd], " & Quo & "mm/dd" & Quo & ")"

    rs.FindFirst strCrit
    If (Not rs.NoMatch) Then
        basGetSeas = rs!Season
    End If

End Function

Of course, this leaves out the nicity of error detection and a few bells an whistles (and optional parameter for the dteDate?).

Briefgly reviewing the original post, I do notice that two TABLES are mentioned, so to use either of the 'code' soloutions would imply calling the procedure for each record. For trivial recordsets, the difference is probably un-important. For 'industrial' sized ones it would more likely be noticable.

As you note, the inquiry has not, as of this point, be sufficiently clear to know the final 'best answer', and it is quite possible that both approaches could be useful. Another basic tennant in db land is to NOT store redundant information, so since 'season' may be calculated from date and the seasons, then as long as it is not a high usage element it may in fact be better to use the date input approach.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[SgtJarrow]

Michael,

As we both sit and hold our breath on this one, I do agree with you in the aspect of using Access' built in updating function whenever possible. But my solution was not meant to do updates with...it merely got the corrct season for one particulr day whenever you needed it...

I am not arguing with you on this, as I have already expressed my gratitude and admiration of you and your solutions. As stated by both of us, until some feedback is given by the poster, we are simply talking....but maybe someone else will find this post useful....that's why we are here anyway, right? And this clearly demonstrates the mutiple methods to skin cats theory.

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[lind77]

Hi guys,

Thanks for your tips. I am now trying to do this the following way: I have two tables:
1. One containing two fields: all dates from 1/1/03 to 31/12/10 and the season(to be filled in)
2. A table containing the season name, beginning date and end date, as these can vary. this table has an associated form. On this form there is a button that will automatically assign all dates within these limits to the season.

my Code sofar is as follows:

Private Sub UpdateSeason_Click()
Dim rs As Recordset
Dim Datum As Date
Dim dbs As Database
Dim strSeek
Dim Counter As Long
Dim BeginDatum As Long
Dim EindDatum As Long

Set dbs = CurrentDb

Set rs = dbs.OpenRecordset(&quot;Tbl_Datum&quot;, dbOpenDynaset)

Do Until Datum = DatumEinde Or Datum = DatumAanvang + 365

strSeek = DLookup(&quot;[Nr]&quot;, &quot;Tbl_Datum&quot;, &quot;[Datum]= #&quot; & Datum & &quot;#&quot


With rs
.FindFirst &quot;[Nr]=&quot; & strSeek
.Edit
!Periode = Periode
.Update
End With

Datum = Datum + 1
Loop

rs.Close

End Sub
rs.Close

End Sub

Unfortunately I still have to change the date formats, for even though in both tables they are short date, my computer has european settings and this causes problems.

Oh yes, and I am in Euope so forgive me if my replies are a bit slow but one needs to sleep as well!

Cheers,

Mark