Convert time stamp Now() to Fiscal Year

[ajcke]

By googling this I realize this has been answered, but I'm very new to Access and do not quite understand the syntax of any of the solutions I've found.

Our fiscal year is from the first day of July to the last day of June. I have a table called StudentScores and I need to be able to sort student progress by school year. My end goal is for the results to be displayed as 2010-2011 School Year, 2011-2012 School Year, etc... I will also need to allow users to select the year they want from a combo box and run a report based on that fiscal school year selection.

The StudentScores table has a field called RecordCreationDate. Type: date/time, Default Value: =Now(), Format: General Date. The end result displays like this: 5/8/2012.

I could create a query per year, and I do that now by using the following as the criteria: >=#7/1/2011# And <#6/30/2012#, but that really limits what I'm doing and isn't getting me the results I need.

[June7]

How will users select fiscal year? E.g., for 2010-2011 FY will they just select or type 2011 or will they select from a list of the hyphenated year pairs? Say they just type/select 2011, then something like:

DoCmd.OpenReport "report name", , , "RecordCreationDate Between #7/1/" & Me.yeartextbox - 1 & "# AND #6/30/" & Me.yeartextbox & "#"

[JeroenMioch]

I dont fully understand what the problem is.
I suppose you have a combobox and filter the school results after selecting the (school) year they wanna see.
So i'd place an afterupdate event on that combobox.

When the user selects 2011-2012 it queries records that have the users student number between the dates you mention.
To get these filtered records on the report you can use this code :

Dim stDocName As String
stDocName = "YourReportName"
DoCmd.OpenReport stDocName, acPreview, , Me.Filter

As your new to Access im guessing you dont know how to filter records programmaticly, But before i go into that i wanna be sure if i understand your problem correct.

[ajcke]

There are two ways I'd like to display data based on the fiscal year. The first would be to allow the user to select the year from a list box to view a report that only displays data within that fiscal year. The second - allow the user to run a report that displays a student's assessment and scores sorted by school year. All years would be displayed.

I guess another solution would be to allow the user to enter a select from date into a text box and a to date into a text box and select go to run the reports. This would work for both options above.

[ajcke]

Yep that's exactly what I need to know how to do. Could you provide some steps?

[JeroenMioch]

Well first of all you need to know the technique to filter records. After you master that then you can filter any data you want to get. Whether it is data based on names, part of names, dates greater then, less then or between dates. Or a combination of them all..

But let us focus on the dates first.

Make two unbound textboxes and a button beside it.

The names of the textboxes will have to correspond with mine (fldDatumZoeken and fldDatumZoeken2)
You can rename them offcourse, but then you have to rename the names in the code

Under the button you set an event procedure to the <on click> and paste this code :

Private Sub KnpFilteren_Click()
Dim strFilter As String, strWhere As String
Dim blnFilter As Boolean
Dim strDateField As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
Dim tmp
blnFilter = False
strFilter = ""
strDateField = "[DatumOrder]"
'Replace the name between brackets to the name of your date field in your table

If IsDate(Me.fldDatumZoeken.Value) Then
If strFilter <> vbNullString Then strFilter = strFilter & " AND "
strFilter = strFilter & "(" & strDateField & " >= " & Format(Me.fldDatumZoeken.Value, strcJetDate) & ")"
blnFilter = True
End If
If IsDate(Me.fldDatumZoeken2.Value) Then
If strFilter <> vbNullString Then strFilter = strFilter & " AND "
strFilter = strFilter & "(" & strDateField & " <= " & Format(Me.fldDatumZoeken2.Value + 1, strcJetDate) & ")"
blnFilter = True
End If

If blnFilter Then
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
If Me.Filter = "" Then
MsgBox "No records found"
Exit Sub
End If

Make sure you have the following code in the form_open event :

Private Sub Form_open(Cancel As Integer)
Me.Filter = "1=2"
Me.FilterOn = True
end sub

When you enter a date in the first unbound textbox (named fldDatumZoeken) and press the buttom you shoud find all records matching records with a date equal to or greater then the date you entered.
When you add a date in the second textbox then you filter records between textbox 1 and 2
When you only fill textbox 2 with a date you find records earlier then the date you entered.

Let me know how this works out so far

[ajcke]

Do I need to name the button KnpFilteren to get this to work? Also when I put the button on the form do I need to go through the button wizard and select Report Operations > Open Report?

[ajcke]

This is great! I setup a continuous form and the code above is working just as described. The form is only displaying dates that I plugin to each field. I had to remove the +1 in the following line to get it to work.
strFilter = strFilter & "(" & strDateField & " <= " & Format(Me.fldDatumZoeken2.Value + 1, strcJetDate) & ")"
Otherwise I would get this error if I typed something into this field: Run-time error '13': Type mismatch

How would I get the button to open a report that I have created using the same query that I used for the form?

Also could you explain the code or point me to some good resources on how to better understand filter records? I think this will really help me in the future.

[June7]

If the query is a saved Access object, form and report can use it for RecordSource, otherwise, build the SQL statement in the RecordSource.

[ajcke]

The query is saved as an access object, but the filter was created with vb code. The form is filtering by date because of the two textboxes and button, but I'm not sure how to pass the filtered criteria from the form to a report. The report displays the query data but does not further filter based on the form code from JeroenMioc above.

[June7]

Can use the same filter string variable in the WHERE argument of DoCmd.OpenReport action.

[ajcke]

Thanks June7, but you're gonna have to explain how to do that.

[June7]

We showed code in earlier posts, such as #2.

[ajcke]

Oops I missed that. DoCmd.OpenReport "report name", , , "RecordCreationDate Between #7/1/" & Me.yeartextbox - 1 & "# AND #6/30/" & Me.yeartextbox & "#"

Works great! I just started with Access Friday and found this forum a couple of days ago. It's awesome!

Thanks for all your help!

[ajcke]

I used the following code to get the report. I showed the user who will be using the database. They would like me to add a dropdown list for assessment types on the form where the user selects the date. This is because they only want to see scores for one assessment at a time. Right now the report shows all assessments. Using the code below, how would I add this filter functionality? They would also like the option to select all assessments. I have a table called Student_Scores with a field called Assessment Name which is a Combo Box Value List.

Private Sub cmdPreview_Click()
'On Error GoTo Err_Handler 'Remove the single quote from start of this line once you have it working.
'Purpose: Filter a report to a date range.
'Documentation: http://allenbrowne.com/casu-08.html
'Note: Filter uses "less than the next day" in case the field has a time component.
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#" 'Do NOT change it to match your local settings.

'DO set the values in the next 3 lines.
strReport = "StudentR" 'Put your report name in these quotes.
strDateField = "[Record_Creation]" 'Put your field name in the square brackets in these quotes.
lngView = acViewPreview 'Use acViewNormal to print instead of preview.

'Build the filter string.
If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If

'Close the report if already open: otherwise it won't filter properly.
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If

'Open the report.
'Debug.Print strWhere 'Remove the single quote from the start of this line for debugging purposes.
DoCmd.OpenReport strReport, lngView, , strWhere

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume Exit_Handler
End Sub