one form with 3 options

[Compufreak]

Hi



I posted this one another section within this forum. All i would like to know is if it possible to create a form with a start date and end date and a combobox. If the user enters a start and end date then it must pull data for that date range only. if the user selects just the combobox only it must pull data for that selection only. if the user selects the start date and end date and the combobox it must pull data for that selection.

All this information is going to be run on one report only. its really tricky and well i really need help now. I am still new to access so please assist me.


thanks again

[amrut]

What data does the combo box has ? Can you post your database ?

[Compufreak]

hi

my combobox is just pulling simple data such as names. all i want this form to do is just bring the data in one report.

here is my code :
Private Sub Command0_Click()
If ([StartDate]) And ([EndDate]) Then' User selects start date and end date only
Me.RecordSource = "qrySearhByDate"
DoCmd.OpenReport "test", acViewReport
Else

If ([Combo5]) Then ' user selects combobox only
Me.RecordSource = "searchby cluster"
DoCmd.OpenReport "test", acViewReport
Else
If ([StartDate]) And ([EndDate]) And ([Combo5]) Then 'user selects both start date and end date and the combobox
Me.RecordSource = "Search_by_Clusters and date"
DoCmd.OpenReport "test", acViewPreview
Else
Me.Visible = False
End If
End If
End If
End Sub


I know something is wrong as it is retrieving all the information. please help, this thing is driving me mad.

[amrut]

I am unable to understand why you are changing the record source of the form after each If ..... then (Me.Record source="XXXXXXXXXXXX"). If you are trying to set the report's record source, the above code will not work. Your code is changing the form's recordsource.

There are many ways to set "criteria" for the report filter.
One example is create a string filter in your code like strFilter="[Your Date field] Between #" & Me.StartDate & "# AND #" & Me.EndDate & "#"
and use DoCmd.OpenReport "test", acViewPreview, , strfilter to open the report.
This will show only results between selected dates

[Compufreak]

where do i reference strfilter? do i code it in the form or the report. I understand what you are trying to do here, but could you clarify it just alittle more.

Thanks again

[amrut]

You need a code similar to below
Private Sub Command0_Click()
Dim strFilter as String
If ([StartDate]) And ([EndDate]) Then ' User selects start date and end date only
strFilter="[Your Date field] Between #" & Me.StartDate & "# AND #" & Me.EndDate & "#"
Endif

If ([Combo5]) Then ' user selects combobox only
strFilter= "[Yourfield]= '" & Me.Combo5 & "'" ' similar to above use single quotes around text field
Endif

If ([StartDate]) And ([EndDate]) And ([Combo5]) Then 'user selects both start date and end date and the combobox
strFilter="[Your Date field] Between #" & Me.StartDate & "# AND #" & Me.EndDate & "# AND [Yourfield]= '" & Me.Combo5 & "'"
End If
DoCmd.OpenReport "test", acViewPreview, , strfilter
End Sub

Hope this solves your purpose

[Compufreak]

it works for the date part but not for the combobox and the combobox with the date selection

[amrut]

The combo box bound column is text or number ? Post your code.

[Compufreak]

Private Sub Command0_Click()
Dim strSQL As String

strSQL = "SELECT Cluster_Dept.ID, Cluster_Dept.Cluster_ID, Cluster_Dept.Dept_ID, Clusters.Cluster_Desc, Department.Dept_Desc, " & _
"Source.Day_Month_Year, Source.Original_Source, Source.Headline, Source.Issue, Source.Analysis, Source.Action, Source.Flag " & _
"FROM Source INNER JOIN (Department INNER JOIN (Clusters INNER JOIN Cluster_Dept ON Clusters.Cluster_ID " & _
"= Cluster_Dept.Cluster_ID) ON Department.Dept_ID = Cluster_Dept.Dept_ID) ON Source.ID = Cluster_Dept.ID"
'Code:
'MsgBox strSQL


If IsNull(Combo1.Value) And IsNull([StartDate]) And IsNull([EndDate]) Then
'run report without any condition
strSQL = strSQL & ";"

Else
If ((Combo1.Value <> " ") And IsNull([StartDate]) And IsNull([EndDate])) Then
'run report by category
strSQL = strSQL & " " & "WHERE [Clusters].Cluster_Desc =" & Chr(34) & [Combo1] & Chr(34) & ";"
Else
If IsNull(Combo1.Value) And (([StartDate] <> " ") Or ([EndDate] <> " ")) Then
'run report by date recieved"
strSQL = strSQL & " " & "WHERE [Day_Month_Year] Between #" & [StartDate] & "# AND #" & [EndDate] & "#"
Else
'If IsNull([Combo1] <> " ") And (([StartDate] <> " ") Or ([EndDate] <> " ")) Then
'strSQL = strSQL & " " & "WHERE [Day_Month_Year] Between #" & [StartDate] & "# AND #" & [EndDate] & "#" AND [Clusters].Cluster_Desc =" & Chr(34) & [Combo1] & Chr(34) & ";"
'MsgBox "'run report by date recived and service number"
'End If
End If
End If
End If

' Assign SQL code to query that the entry form uses
CurrentDb.QueryDefs("Search_by_Clusters and date").SQL = strSQL
'Open the entry Form
DoCmd.OpenReport "Search_by_Clusters and date", acViewReport
End Sub


the part where i have underlined and put it in bold , thats where i am getting an error , not sure if my code there is correct

N.B I quoted that specific code as i was testing the code , and when i unquoted it , it was throwing out an error

please help

[Compufreak]

let me know if you could solve this, i know its something small , just my brain doesnt seem to wanna work now lol

[amrut]

If you leave the erroneous part, does following the part produces the report ?
'run report by category
strSQL = strSQL & " " & "WHERE [Clusters].Cluster_Desc =" & Chr(34) & [Combo1] & Chr(34) & ";"

[Compufreak]

yes it works , so i aint sure whats the problem

[amrut]

Is the " (double quote) shown in red is the culprit ? Carefully examine, constant values should be within quotes and dynamic between & ( ampersand).
strSQL = strSQL & " " & "WHERE [Day_Month_Year] Between #" & [StartDate] & "# AND #" & [EndDate] & "#" AND [Clusters].Cluster_Desc =" & Chr(34) & [Combo1] & Chr(34) & ";"
does this works ?
strSQL = strSQL & " WHERE [Day_Month_Year] Between #" & [StartDate] & "# AND #" & [EndDate] & "# AND [Clusters].Cluster_Desc =" & Chr(34) & [Combo1] & Chr(34) & ";"