Query to use on Pivot Chart / Report

[Grafixx01]

Ok, so this has been boggling my mind for about a month now. I'm having difficulties trying to figure out how to write this query, as I figure it'll have to be done in SQL versus GUI.

Requirements:

1. User selects "Country" from a combobox on a form.
2. User can select a pre-determined number of days to search from a combo box (30, 60, 90, 120, 180) OR
3. User can select a specific date range using two unbound text boxes on the form. OR
4. If user DOES NOT select a pre-determined or date range, it AUTOMATICALLY defaults to 365 days PRIOR to current date.

This would then show on the bottom of the form a report where the user can view the graph and then have the option to print it to the default local printer.

Any ideas on what and how I can do this?

 

[DCrake]

Just noticed you had not got a reply as yet so here goes.

Have a button on your form that will act as a criteria builder that checks for any records found based on the user input.

CmdTestResults

Next declare 2 date variables and 1 string variable and 1 Integer variable

Dim DateLower As Date
Dim DateUpper As Date
Dim StrCountry As String
Dim IntInterval As Integer

On the OnClick Event of the CmdTestResults button try the following

StrCountry = Me.ComboCountry

Next check to see if the user has entered in the date range manually.

If Me.TxtDateLower <> "" and Me.TxtDateUpper <> "" Then

   DateLower = CDate(Me.TxtDateLower)
   DateUpper = CDate(Me.TxtDateUpper)

Else



If Me.ComboDays <> ""  Then
   IntInterval = Val(Me.ComboDays) * -1
   DateLower = DateAdd("d",IntInterval,Date)
   DateUpper = Date
Else
   DateLower = DateAdd("d",-365,Date)
   DateUpper = Date

End if

So now we have all the components we can build the actual SQL statement

Sql = "SELECT * From TblTableName WHERE fldCountry ='" & StrCountry & "' And fldDate Between #" & DateLower & "# And #" & DateUpper & "#;"

You can then use the underlying query for your source data.

Remember the table and field names used in this example are for brevity only. You will need to substitute your actual table and field names.

Also this has been written in aircode, and as such has not been validated.

What you could do here is to count the number of records found and if there are none you can inform the user of no matching records, thus preventing you trying to build a report / graph with no source data.

If TestResults(sql) = 0 Then
    MsgBox "There were no matching records found based on the criteria selected. Please revise and retry.", vbExclamation+vbOkOnly,"No Data"
End If

Public Function TestResults(SqlStr As String) As Boolean

Dim Rs As DAO.Recordset

Set Rs = CurrentDb.OpenRecordset(SqlStr)

If Rs.EOF Then
   TestResults = 0   
Else
   TestResults = True
   Rs.Close
Endif

Set Rs = Nothing

End Function

CodeMaster: