Filter By Multiple Comboboxes on a form

[Tubbzuk]

Hi,

I have written a small databse to handle samples.
I want to be able to report on the data in there by several different criteria.
I would like a date range taken from 2 textboxes on a form and several values picked from comboboxes.
If a combobox is left blank then all values from that are to be used.

I have tried 2 methods.
The first being in VB which comes fromthe microsoft pages where it has a set filter button and the code builds a string by naming each 1 Filter1 filter2 and so on and the next loop takes the value of each one and builds the SQL string. When ever i use this method i get a parameter box appear and then a blank report regardless of what i enter.

The second method is to tie the control source of the report in the query builder to the control. THis works for single entries. I have added like... & "*" to that statement to capture when the box is left blank and this too works fine. When there is a second option selected it returns nothing.

I need to have a total of around 9 different options to select and a date range and am now at the point where i may start to tear my hair out.

Can anyone help please?

Regards,

Alan

 

[Isskint]

The answer is method 1. The parameter box suggests an error in constructing your SQL. Can you post the VBA you are using to create the SQL string?

 

[Tubbzuk]

Thanks for replying.
I got the code from a microsoft site that explains the whole process. I also downloaded a sample database in which someone had used the same code.
Theirs works but when i use it in my own it has this box.

the set filter subroutine that builds the SQL string is

Private Sub btnSetFilter_Click()
Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 10
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next
If strSQL <> "" Then
'Strip Last " And "
strSQL = Left(strSQL, (Len(strSQL) - 5))
'Set the Filter property
Reports![rptMain].Filter = strSQL
Reports![rptMain].FilterOn = True
End If

End Sub

you probably recognise the code,

regards,

Alan

 

[Isskint]

The bit that is probably causing the parameter box to pop up is the square brackets. Also I have never seen ME used in this way (not saying it can not). In fact i am not sure what this code will achieve. The use of the Tag property is just puzzling the hell out of me also. Essentially what the SQL reads is;
[Filter1.Tag] = "Filter1".

If you are trying to set a filter you need to include a field/control name to filter on and what its parameter is. So;
strSQL = strSQL & "(Field1 ='" & Combo1 & "')". This would filter the data on Field1 where Field1 = the value of Combo1. The use of controls with the same name + a number makes the process much easier but only if the Field/Control names and Parameter controls are are in this format.
In addition you mentioned about dates and there is no inclusion of dates in your code. Dates need to be bracketed by octothorpes # in SQL.

Sorry if this does not help, but more info will be needed to help resolve your query. Namely;

• Field Names on the Form that need to be filtered on
  • Their data types
• Control Names that the parameters are selected from or entered in

 

[Tubbzuk]

would it help if i attach my database?

 

[Tubbzuk]

the date thing i was going to handle with a between parameter on the report.

so there are 2 textboxes which are the date controls one being datefrom the other being dateto.
The parameter would be between Forms!frmFilter.datefrom AND Forms!frmFilter.dateto

i thought this should do what i want it to without the need for adding vb coding.
the code i got for the other controls that are using the .tag thing can be found at this site.
but i have not posted enough so i cannot post the link. it can be found at support . microsoft . com / kb / 208529

thanks for your help, it is very much appreciated

 

[Isskint]

Thanks for the link, the use of Tag still fails me, hopefully someone else can answer that.

To your specific issue then. The method you are following can be acheived in different ways. The most often way is to have the report run off a query and then EITHER

1. Change the queries SQL (in much a similar way as you are now)
   1. An example of this can be found in the attached and a good guide here http://fontstuff.com/access/acctut17.htm
      
2. Have the query get the parameters from your pop up box.
   1. In the criteria section for each field you want to filter, include a reference to the relevant control on your pop up form, so like;
      1. =Forms!PopUp!Control1
      2. The only problem with this method is you can not allow for Null criteria

 

[Tubbzuk]

Hi,

Thanks so much for that. I think i understand how you have gone about building the string with the if statements and such and makes a bit more sense than the other one i was trying to do. I never thought of adding a query to it either.
I will be trying the same thing for my database today once i have finished work and let you know how i get on.
Thanks so much for your help ;-)

 

[Tubbzuk]

Hi,

I have the reporting side of things working with null values and returning the report for 10 criteria. i just need to add a date range.

I have 2 textboxes, with date selectors. one is datefrom and the other is dateto.
THe default value for dateto is date() to give todays date.

I thought it would be somethign like
if me.datefrom <>"" then strwhere = strwhere & "(BETWEEN '" & Datefrom.value & "' AND '" & Dateto.value & "' AND "

This gives me and error 3075 syntax error.

is it becasue i should do a seperate line for each date entry? i am unsure how to get this to read back the figures inbetween these dates if a iuse 2 seperate lines.

regards,

Alan

 

[Isskint]

No, just as you need to enclose strings in quotes, with dates you need to enclose the date in octothorpes #. There are a couple of other syntax issues with thecode also.

If Me.datefrom & "" <> "" Then
strWhere = strWhere & "(BETWEEN #" & Datefrom & "# AND #" & Dateto & "#) AND "
Else
strWhere = strWhere & "(< #" & Dateto & "#) AND "
End If

 

[Tubbzuk]

thats a great help thank you.
I have got the following string now from the following code

however it returns results that do not match

If Me.dateFrom <> "" Then strwhere = strwhere & "([date] BETWEEN #" & Me.dateFrom.Value & "# AND #" & Me.dateTo.Value & "#) AND " is the code

SELECT [QC Tasting].* FROM [QC Tasting]WHERE (([date] BETWEEN #01/10/2012# AND #01/01/2013#)); is the string once i have put 2 values in the relevant text boxes

The query holds this string
SELECT [QC Tasting].*
FROM [QC Tasting]
WHERE (([date] BETWEEN #01/10/2012# AND #01/01/2013#));

and the results in the report start with date 31/08/2012

Any ideas where i am going wrong?
as far as i can tell the sql string is correct as when i put it into another query it selects the correct data straight away.

Thanks for your help, it is much appreciated.

Regards,

Alan

 

[Isskint]

It is most likely an issue with default date format and/or your local settings. Access will read 01/10/2012 as 10 January 2012 by default (American date format mm dd yy). You could try DateValue() to coerce the correct date.

Another possible minefield is the use of [date] as a field name. Date() is a function and statement used in Access eg SomeVariable = Date(). You would be better off including a meaning to your field name, TastingDate for example?

 

[Tubbzuk]

i have changed the date field to SampleDate and made all the adjustments across all forms and code as necessary.
I still get the same problem.
The date format of the laptop is correct as i have checked date format in the bottom corner ( work laptop so dont have admin rights to even adjust my dtae, how grim!)

I have looked at the sql of the query that is called though and it seems to be droppign any zeros that are in there.
i ran a search between 01/07/2012 and 07/07/2012 and the results are mostly good bar around 3 erroneous results.
Then i close the report and changed the dates to 01/08/2012 and 30/08/2012 and the query sql says it is looking for
SELECT [QC Tasting].*
FROM [QC Tasting]
WHERE ((([QC Tasting].[SampleDate]) Between #1/8/2012# And #8/30/2012#) AND (([QC Tasting].[Sample Nature?])='Packed'));

that suggests to me that the format of the date is wrong somewhere.
is that in the property of the text box on the from?

Regards,

Alan

 

[Isskint]

I would say this is a date format issue. Have you noticed you asked for 30 August and it returned August 30? Your database is handling dates in the American format. You could try adding a format to the textboxes. Just see what it display when you auto input (CTRL + ; ) todays date. 01/07/13 or 07/01/13?
You could also try entering months as names.

I seem to remember reading on this forum that SQL will always see dates as mm/dd/yy no matter what you do. I have resorted to using calendar pop ups to record dates and use formats to 'show' it the way the user wants. Also in reporting scenarios i tend to use combo boxes returning a DISTINCT list of dates so again there is no 'interpretation' issues.

 

[Tubbzuk]

in using distinct list of dates would you then revert to formating the data type as text and use the input data as a string as opposed to the octothorpe date method?

Regards,

Alan

 

[Isskint]

No you would still need to use # Combobox #. The # tells SQL that the value is a date.

 

[Tubbzuk]

starting to tear my hair out now. I have set the comboboxes to choose the distinct values of the date.
when converting it to SQL it swaps the date and month around. If i manually enter the date in the format mm/dd/yyyy it runs everything fine. I just cant get it to run correctly in the format dd/mm/yyyy.
I have tried searching on the net for this problem and am not having much luck.
I have been reading soemthing on converting which i think i need to do first. If i can convert the value of the textbox into a string and rearrange it then pass that value into the SQL statement it might work but my vb is limited as this is the most i have done in years!
Do you think this could work and have you ever done anythign like this before?

Regards,

Alan

 

[evanark]

I got this code I found and tweeked for my purpose but you can create a form and have drop down boxes and link them to values in you tables. You can pull distinct values for you drop down if you want or hard code them. This code helped me. You create the form add the controls and add the following code to you button to open the report. You have to design the report for you purpose but the code takes the values you select from the drop downs and adds them to a string in the filter for the form. I hope this helps.

Private Sub cmdGenerate_Click()
Const conJetDate = "\#mm\/dd\/yyyy\#"
Dim strReport As String
Dim strWhere As String
Dim lngView As Long
Dim lngLen As Long

If IsNull(Me.cboReport) Then
MsgBox "Please select a report", vbOKOnly
Else
strReport = (Me.cboReport)
End If

If (Me.cboOutput) = "Print" Then
lngView = acViewNormal
Else
lngView = acViewPreview
End If

If Not IsNull(Me.cboProduct) Then
strWhere = strWhere & "([Product] = """ & Me.cboProduct & """) AND "
End If
If Not IsNull(Me.cboIssue) Then
strWhere = strWhere & "([Issue] = """ & Me.cboIssue & """) AND "
End If
If IsNull(Me.txtStartDate) Then
MsgBox "Please enter a start date", vbOKOnly
Else
strWhere = strWhere & "([Open Date] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
End If
If IsNull(Me.txtEndDate) Then
MsgBox "Please enter a end date", vbOKOnly
Else
strWhere = strWhere & "([Open Date] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If
If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.Close acReport, strReport
End If
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
DoCmd.OpenReport strReport, lngView, , strWhere
End If
End Sub

 

[Tubbzuk]

thats cool. thank you for replying to me.
I have the code that sorts all the relevant data i need. the only issue i have now is the dates.
the date format in the data base is dd/mm/yyyy. i have got code that will sort the dates fine. but when its read by the query it reads it as mm/dd/yyyy.

is the Const conJetDate = "\#mm\/dd\/yyyy\#" just a constant variable?

I need some code that when i read the datefrom variable for instance 31/01/2012 it places it in the sql string as 01/31/2012

Thanks for your help

 

[Isskint]

The Const conJetDate may resolve your problem, try it. It revolves around this issue where SQL 'likes' dates in one format (further checking shows that to be yyyy/mm/dd !). Obviously you do not need all the rest of the code just the Const declaration Const conJetDate = "\#mm\/dd\/yyyy\#" and the format line Format(Me.txtStartDate, conJetDate) any time you are refering to a date from your comboboxes.

If this still is not working then please attach the DB so i can look at the data insitu.