Date help!

[esu4edp]

I have a database that is basically a log book for recording flight hours. We enter the date like 01-jul-05 for example in the tables. I have a form that asks the user to enter the year to print out an annual report of flight times for that whole year, but I only want the user to enter for example "2005" and then have it print out a report of the annual hours broken down by months, quarters and so on for all the records that have a date of 2005 even though the data is formatted as a medium date in the table. Is this possible. I know this could be done alot easier with date ranges but I just want the user to enter 4 numbers for the year and be done with it.

 

[MajP]

Something like

Private Sub Combo27_AfterUpdate()
  Dim intYear As Integer
  Dim strWhere As String
  intYear = Me.Combo27.Value
  strWhere = "Year([dtmDates]) = " & intYear
  DoCmd.OpenForm "frmFindDates", , , strWhere
End Sub

Ensure the user chooses only valid dates, use a combo. Sql for the combo

SELECT DISTINCT Year([dtmDates]) AS Year FROM tblDates ORDER BY Year([dtmDates])

 

[esu4edp]

Ok, just so I understand where to put this.

This statement would go on a form by itself, the user enters the 4 digit date and then it would take that date and open another form "frmFindDates" and display it:

Private Sub Combo27_AfterUpdate()
Dim intYear As Integer
Dim strWhere As String
intYear = Me.Combo27.Value
strWhere = "Year([dtmDates]) = " & intYear
DoCmd.OpenForm "frmFindDates", , , strWhere
End Sub

and then provide the sql statement in a query?

SELECT DISTINCT Year([dtmDates]) AS Year FROM tblDates ORDER BY Year([dtmDates])

 

[MajP]

It would not have to be its own form, other things could be on the form. In my example I have a table with data and dates in it. I find all the possible years to display in my combobox, by looking in this table and pulling out the unique years from the dates. For you it is probably something more like

SELECT DISTINCT Year([FlightDate]) AS Year FROM tblFlightLog ORDER BY Year([FlightDate])

where your table is "tblFlightLog" and your field is "FlightDate"

Combo27 gets replaced with your combo's name

This opens a form name "frmFindDates"
DoCmd.OpenForm "frmFindDates", , , strWhere
You can open a report
docmd.openreport ...

 

[esu4edp]

Hey this worked out perfectly. Can I change the order by in the statement to order it by month for that particular year.

So basically the user enters the year and it gives all those records for that year, then can I order by month for that year?

 

[MajP]

Private Sub Combo27_AfterUpdate()
Dim intYear As Integer
Dim strWhere As String
intYear = Me.Combo27.Value
strWhere = "Year([dtmDates]) = " & intYear
DoCmd.OpenForm "frmFlightLog", , , strWhere
with forms(frmFlightLog")
.orderBy = "dtmDates" 'Name of field to sort
.orderByOn = true 'Turn it on in the form
end with
End Sub