Filter reports 2

[dvannoy]

Does anyone know how to create a Form that filters out what you would like to see on reports.. I been trying to work with an example i got from micrsoft but could not make it work all the way...




DVannoy
A+,Network+,CNA
dvannoy@onyxes.com

 

[JoeMiller]

I know how, but what are you trying to create? What have you got so far? What method were you trying to use? Field/table/query/report/form names are ALL helpful!

Joe Miller
joe.miller@flotech.net

 

[jebry]

Hi!

Usually I use a form with combo boxes or list boxes to allow the user to select their criteria and then build a filter in VBA based on their entries. But I am not familiar with the example from microsoft. If you could supply a few of the specifics, I would be glad to provide more help.

Jeff Bridgham

 

[dvannoy]

im pretty much trying to do what jebry is currently doing....ihave a query that has 5 fileds..

sampleno
sample_type
logindate
generator
wip

i want drop down boxes for the sample_type, generator and logindate. then after the user chooses there criteria it can either go into a preview mode on a report..or in a form...I havent decided what would be the best way for the user to view it.

Thanks

DVannoy
A+,Network+,CNA
dvannoy@onyxes.com

 

[dvannoy]

im pretty much trying to do what jebry is currently doing....ihave a query that has 5 fileds..

query name = search for samples

sampleno
sample_type
logindate
generator
wip

i want drop down boxes for the sample_type, generator and logindate. then after the user chooses there criteria it can either go into a preview mode on a report..or in a form...I havent decided what would be the best way for the user to view it.

Thanks

DVannoy
A+,Network+,CNA
dvannoy@onyxes.com

 

[JoeMiller]

Use the Like operator in your query to get the appropriate records:

Like "*" & [Forms]![MyFormName]![MyControl] & "*"

Then when you run it it will look at the form for the criteria, if no criteria is entered, you'll get Like * which is ALL records!

Joe Miller
joe.miller@flotech.net

 

[jebry]

Hi!

Assuming your form will have three combo boxes and a command button you can use the following:

Private Sub CommandButton_Click()

Dim Criteria As String
Dim CriteriaCount As Integer

CriteriaCount = 0

If Nz(Len(cbosample_type),0) <> 0 Then
Criteria = &quot;sample_type = '&quot; & cbosample_type & &quot;'&quot;
CriteriaCount = 1
End If

If Nz(Len(cbogenerator),0) <> 0 Then
If CriteriaCount = 0 Then
Criteria = &quot;generator = '&quot; & cbogenerator & &quot;'&quot;
CriteriaCount = 1
Else
Criteria = Criteria & &quot; And generator = '&quot; & cbogenerator & &quot;'&quot;
End If
End If

If Nz(Len(cbologindate),0) <> 0 Then
If CriteriaCount = 0 Then
Criteria = &quot;logindate = #&quot; & cbologindate & &quot;#&quot;
CriteriaCount = 1
Else
Criteria = Criteria & &quot; And logindate= #&quot; & cbologindate & &quot;#&quot;
End If
End If

DoCmd.OpenReport &quot;ReportName&quot;, acViewPreview, , Criteria

End Sub

There are other ways to go about it so if you need more information let me know.

hth
Jeff Bridgham

 

[dvannoy]

im getting an syntax error with the #...the other thing is , do you have your combo boxes as a value list or what?

If i link my combo boxes to the table or query i see multi entrys....whats the best way to do this..

Thanks DVannoy
A+,Network+,CNA
dvannoy@onyxes.com

 

[jebry]

Hi!

For you rowsource in the combo boxes use:

Select Distinct sample_type From YourTableName

This will allow only one entry for each sample type in the table. I'm not sure about the other problem. I did make the assumption that logindate was a date/time data type. If it isn't then you should substitute single quotes for the pound signs. If it is a date/time type then check your quotes to make sure they balance and be sure that you have spaces on both sides of the ampersands.

Let me know if this works out.
Jeff Bridgham

 

[dvannoy]

Thanks Man I got it to work...

DVannoy
A+,Network+,CNA
dvannoy@onyxes.com