Multi-Select Listbox as filter for Query field (1 Viewer)

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:07
Joined
Aug 30, 2003
Messages
36,118
After I take the territory criteria out of the query, the code works fine for me.
 

irsmalik

Registered User.
Local time
Today, 06:07
Joined
Jan 28, 2015
Messages
88
Thank you very much sir pbaldy.... it is really working...
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:07
Joined
Aug 30, 2003
Messages
36,118
No problem, glad it worked for you.
 

irsmalik

Registered User.
Local time
Today, 06:07
Joined
Jan 28, 2015
Messages
88
Sir pbaldy
Instead of Selecting Territories one by one, can I put * to select All.
How can I do it.
irsmalik
 

moke123

AWF VIP
Local time
Yesterday, 22:07
Joined
Jan 11, 2013
Messages
3,852
Put a "Select All" button next to the list box and run this code from it.
This code goes in a standard module.

Code:
Public Function SelectAll(lst As ListBox) As Boolean

    On Error GoTo Err_Handler

    'Purpose:   Select all items in the multi-select list box.
    'Return:    True if successful
    'Author:    Allen Browne. http://allenbrowne.com  June, 2006.
    Dim lngRow As Long

    If lst.MultiSelect Then
        For lngRow = 0 To lst.ListCount - 1
            lst.Selected(lngRow) = True
        Next
        SelectAll = True
    End If

Exit_Handler:
    Exit Function

Err_Handler:

    Resume Exit_Handler

End Function

you would then call it with something like

Code:
call SelectAll(me.YourListboxName)
be sure to substitute the name of your listbox
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:07
Joined
Aug 30, 2003
Messages
36,118
Or simply open the report with no wherecondition if a checkbox for "All" is checked, or a listbox value is added that denotes all.
 

moke123

AWF VIP
Local time
Yesterday, 22:07
Joined
Jan 11, 2013
Messages
3,852
Here's a quick example of building a where clause with a multi-select listbox.
If nothing is selected it returns all records.
 

Attachments

  • where.accdb
    484 KB · Views: 107

irsmalik

Registered User.
Local time
Today, 06:07
Joined
Jan 28, 2015
Messages
88
Monthly Report based on Crosstab Query

I want to make a Monthly Report from my Table. I know this will be done by Crosstab Query. I have tried to make it but could not. My table fields are given below. What I want to make, is attached as a picture.

CompanyName text
RegionName text
TerritoryName text
Activitydate date
FAS number
FM number
Demo number
HPM number
BG number
DTP number
Any one could help me to create crosstab query for my Monthly Report.

Thanks
irsmalik
 

Attachments

  • MonthlyReport.jpg
    MonthlyReport.jpg
    95.6 KB · Views: 94

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:07
Joined
Aug 30, 2003
Messages
36,118
If I had to guess, that will probably be done with subreports for each month, not a crosstab.
 

irsmalik

Registered User.
Local time
Today, 06:07
Joined
Jan 28, 2015
Messages
88
Monthly Report based on Crosstab Query

Dear Sir Pbaldy

I want to make one Report. Subreport is not a solution. Please look the attachment and advice.

thanks
irsmalik
 

Frothingslosh

Premier Pale Stale Ale
Local time
Yesterday, 22:07
Joined
Oct 17, 2012
Messages
3,276
Re: Monthly Report based on Crosstab Query

Subreport is not a solution.

Actually, yes it is.

It's also the only solution that's likely to work, as you can't do what you're asking for in that screenshot with a single crosstab query. Not in Access, anyway.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:07
Joined
Aug 30, 2003
Messages
36,118
Re: Monthly Report based on Crosstab Query

I want to make one Report. Subreport is not a solution. Please look the attachment and advice.

I did look at the attachment and gave you my opinion. I'll step out of the way so somebody smarter than I can help you do it with a single report.
 

irsmalik

Registered User.
Local time
Today, 06:07
Joined
Jan 28, 2015
Messages
88
Hello friends
At last I made my Region Wise Monthly Report.
Some one advise me to combine all my activities in one column and then make Cross tab query. That really worked and my report is now final. Plz see the attached pic.

One more help plz. I can't make below event in Order / Sort. Plz guide me how to do it.
*******************
Private Sub cboCompany_AfterUpdate()
If Not IsNull(Me![cboCompany]) Then
Me![cboRegion].RowSource = "SELECT DISTINCT [RegionID], [RegionName] FROM tblCommodity WHERE [Company] = " & Me![cboCompany]

End If
End Sub
*********************
thanks
irsmalik
 

Attachments

  • Month Wise Report.jpg
    Month Wise Report.jpg
    100.4 KB · Views: 105

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:07
Joined
Aug 30, 2003
Messages
36,118
Try

Me![cboRegion].RowSource = "SELECT DISTINCT [RegionID], [RegionName] FROM tblCommodity WHERE [Company] = " & Me![cboCompany] & " ORDER BY DesiredFieldName"
 

irsmalik

Registered User.
Local time
Today, 06:07
Joined
Jan 28, 2015
Messages
88
Thanks sir PBaldy............ its working....
 

irsmalik

Registered User.
Local time
Today, 06:07
Joined
Jan 28, 2015
Messages
88
Hello Friends
I am making a query to retrieve Data 30 days before from Todate. I have already done this kind of work but I don't know why it is not working. here is the query

SELECT DM.ClassName, DM.SizeName, DM.TDate, DM.FAS, DM.FM
FROM DM
WHERE (((DM.TDate)=Date()-30))
ORDER BY DM.ClassName, DM.TDate;

can any one help me plz.
thanks
irsmalik
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:07
Joined
Aug 30, 2003
Messages
36,118
If your date field includes a time that will only match records at midnight. Try this as a test:

Between Date()-30 And Date()-29
 

irsmalik

Registered User.
Local time
Today, 06:07
Joined
Jan 28, 2015
Messages
88
No Sir.... my TDate field is Medium Date....
I want to fix it in my program. Every time when query run... it must display data 30 days before Current Date.
irsmalik
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 19:07
Joined
Aug 30, 2003
Messages
36,118
That's a format, and doesn't mean the value doesn't include a time, you just wouldn't see it. Did you try what I suggested?
 

Users who are viewing this thread

Top Bottom