Multi Select List Box to filter Report 1

[ssecca26]

i spend whole week to solve this problem but until now i cant solve it, can someone help me please.

i have this in a form

lstArea ---- Area 1-4
lstCP - CP01 - 10
Name - 4 Names
cmbfrom - date from
cmbto - date to
sections - section in my table - fieldnames(Section 1- 5)
cmdpreview - preview a report


i want a report to filter my choices in the above criterias, if the box is empty means all


please help me, i really need to get this code.


Thanks!

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ssecca26]

i got this one in one site i just edit it... i really dont know how to start this.

Private Sub cmdpreview_Click()
On Error GoTo Err_cmdpreview_Click

Dim stDocName As String

'query code
Dim stWhere As String
Dim stDates As String
Dim blnTrim As Boolean

If Not IsNull(Me.LstArea) Then
stWhere = "[Area]=" & Me.LstArea & " And "
blnTrim = True
End If

If Not IsNull(Me.LstCP) Then
stWhere = stWhere & "[CP_number]=" & Me.LstCP & " And "
blnTrim = True
End If

If Not IsNull(Me.lstname) Then
stWhere = stWhere & "[name]=" & Me.lstename & " And "
blnTrim = True
End If

If IsNull(Me.cmbWeekFrom) And Me.cmbWeekFrom = "" Then
If Not IsNull(Me.cmbWeekTo) And Me.cmbWeekTo <> "" Then
stWhere = stWhere & "[Week] <=" & Me.cmbWeekTo & "#"
blnTrim = False
End If
Else
If IsNull(Me.cmbWeekTo) And Me.cmbWkeeTo = "" Then
If Not IsNull(Me.cmbWeekFrom) And Me.cmbWeekFrom <> "" Then
stWhere = stWhere & "[Week]>=" & Me.cmbWeekFrom
blnTrim = False
End If
Else
If (Not IsNull(Me.cmbWeekFrom) And Me.cmbWeekFrom <> "") And (Not IsNull(Me.cmbWeekTo) Or Me.cmbWeekTo <> "") Then
stWhere = stWhere & "[Week] Between #" & Me.cmbWeekFrom & "# And #" & Me.cmbWeekTo & "#"
blnTrim = False
End If
End If
End If

If Not IsNull(Me.lstsections) Then
stWhere = stWhere & Me.lstsections & " And "
blnTrim = True
End If

If blnTrim Then
stWhere = Left(stWhere, Len(stWhere) - 5)
End If

stDocName = "Qry Weekly Assessment"
DoCmd.OpenReport stDocName, acPreview

Exit_cmdpreview_Click:
Exit Sub

Err_cmdpreview_Click:
MsgBox Err.Description
Resume Exit_cmdpreview_Click

End Sub

 

[FancyPrairie]

Check out this FAQ faq181-5497. It will solve your problem for you. All you have to do is to copy the code from the FAQ and paste it into a new module. Then set the tag property of the list box as specified in the FAQ and open the report like this:
Docmd.OpenReport stDocName, acPreview,,BuildWhere(Me)

 

[ssecca26]

Fancy i did the instruction, copy and paste the code, set the tag property of all list box and preview the report like Docmd.OpenReport stDocName, acPreview,,BuildWhere(Me)

i also change the AND to OR but this problem pops up

Synstax error (missing operator) in query expression '((Weekly Report.ID in (Area)) OR (Weekly Report.ID in (Name)) OR (Weekly Report. ID in (Section2)))'

hope you can help me please...


Thanks!

 

[FancyPrairie]

I believe the problem is that you have spaces incorporated in your field names. If you are going to use spaces, then you should surround your field names with brackets. (My code should accomodate for that, but it doesn't.) Something like this:
Where=[Weekly Report].ID...

 

[ssecca26]

same problem even though i already put [] on tag

 

[FancyPrairie]

I'm assuming that you have a table called [Weekly Report] that contains a field called ID. I'm also assuming that ID's data type is Text. Therefore, I believe your tag property has been set incorrectly. Try this:

Where=[Weekly Report].ID,string

 

[ssecca26]

my ID is a autonumber, i change it to string, data type mismatch in criteria expression.

i really dont know what is the problem with this one.

 

[FancyPrairie]

What data type is "Area" or "Name" or "Section2"?

Create a query that works with a Where clause that includes Area, Name, and Section2. That should tell you how the tag property should look.

 

[ssecca26]

i already put for area (string); cp (string); name (string)

but for section i dont know because it is a value list that comes from different field in my table.

i dont know how to explain hope you get what i mean.


Thanks

 

[FancyPrairie]

ID is an autonumber and the others are strings. You're not going to get a match on those. Again, create a query using the query designer. When you get the query working, show me the query.

 

[ssecca26]

i already created a query before and it is working all the values and fields shows their data.

 

[FancyPrairie]

Paste the query here so I can see it.

 

[ssecca26]

i cannot attach the snap shot any way here it is


ID Project_Week Area CP Name Section 1 Section 2 Section 3 Section 4 Section 5 Section 6
1 262 Area 3 18 Malu 10 Days NA NA 20 Days List of Tech NA

 

[FancyPrairie]

No, I don't want the results of the query, I want to see the query itself (i.e. Select...)

 

[ssecca26]

how can i copy it here? the query it self?

 

[ssecca26]

or the code which i copy from the faq? sorry im really dont know how to make this database right/work

 

[FancyPrairie]

I'm assuming your report is based on a query. Simply go into the Query designer, switch to SQL view (rather than design view) and copy the Select statement and paste it here.

 

[ssecca26]

SELECT [Weekly Report].ID, [Weekly Report].Project_Week, [Weekly Report].Area, [Weekly Report].CP, [Weekly Report].Name, [Weekly Report].Section 1, [Weekly Report].Section 1_Details, [Weekly Report].Section 2, [Weekly Report].Section 2_Details, [Weekly Report].Section 3, [Weekly Report].Section 3_Details, [Weekly Report].Section 4, [Weekly Report].Section 4_Details
FROM [Weekly Report];