Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-14-2018, 05:25 AM   #31
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,218
Thanks: 83
Thanked 458 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Monthly Report based on Crosstab Query

Quote:
Originally Posted by irsmalik View Post
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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 12-14-2018, 08:57 AM   #32
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,399
Thanks: 13
Thanked 4,125 Times in 4,057 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Monthly Report based on Crosstab Query

Quote:
Originally Posted by irsmalik View Post
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.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 12-19-2018, 02:11 AM   #33
irsmalik
Newly Registered User
 
Join Date: Jan 2015
Location: Multan - Pakistan
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
irsmalik is on a distinguished road
Send a message via Skype™ to irsmalik
Re: Multi-Select Listbox as filter for Query field

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
Attached Images
File Type: jpg Month Wise Report.jpg (100.4 KB, 36 views)

irsmalik is offline   Reply With Quote
Old 12-19-2018, 08:07 AM   #34
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,399
Thanks: 13
Thanked 4,125 Times in 4,057 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Multi-Select Listbox as filter for Query field

Try

Me![cboRegion].RowSource = "SELECT DISTINCT [RegionID], [RegionName] FROM tblCommodity WHERE [Company] = " & Me![cboCompany] & " ORDER BY DesiredFieldName"
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 12-21-2018, 03:40 AM   #35
irsmalik
Newly Registered User
 
Join Date: Jan 2015
Location: Multan - Pakistan
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
irsmalik is on a distinguished road
Send a message via Skype™ to irsmalik
Re: Multi-Select Listbox as filter for Query field

Thanks sir PBaldy............ its working....
irsmalik is offline   Reply With Quote
Old 12-21-2018, 08:42 AM   #36
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,399
Thanks: 13
Thanked 4,125 Times in 4,057 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Multi-Select Listbox as filter for Query field

No problem.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 12-28-2018, 01:47 AM   #37
irsmalik
Newly Registered User
 
Join Date: Jan 2015
Location: Multan - Pakistan
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
irsmalik is on a distinguished road
Send a message via Skype™ to irsmalik
Re: Multi-Select Listbox as filter for Query field

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

irsmalik is offline   Reply With Quote
Old 12-28-2018, 08:46 AM   #38
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,399
Thanks: 13
Thanked 4,125 Times in 4,057 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Multi-Select Listbox as filter for Query field

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
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 12-30-2018, 09:06 PM   #39
irsmalik
Newly Registered User
 
Join Date: Jan 2015
Location: Multan - Pakistan
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
irsmalik is on a distinguished road
Send a message via Skype™ to irsmalik
Re: Multi-Select Listbox as filter for Query field

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
irsmalik is offline   Reply With Quote
Old 12-30-2018, 09:42 PM   #40
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,399
Thanks: 13
Thanked 4,125 Times in 4,057 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Multi-Select Listbox as filter for Query field

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?
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 01-05-2019, 04:05 AM   #41
irsmalik
Newly Registered User
 
Join Date: Jan 2015
Location: Multan - Pakistan
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
irsmalik is on a distinguished road
Send a message via Skype™ to irsmalik
Re: Multi-Select Listbox as filter for Query field

Hello Dear friends

I want give Numbers for my field staff on their performance.
FAS is an activity.... If some one gain 500 out of 500, he will be given 10 Numbers.
if less than 500, then how to show its performance in Query. I made below query.

Expr1: IIf([FAS2]>=10,"10") it works fine

But if FAS2 is <10, how to write it in Query...

Please help.
Thanks

irsmalik
irsmalik is offline   Reply With Quote
Old 01-05-2019, 09:38 AM   #42
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,399
Thanks: 13
Thanked 4,125 Times in 4,057 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Multi-Select Listbox as filter for Query field

Try

Expr1: IIf([FAS2]>=10,"10", "Whatever")
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 01-06-2019, 08:46 PM   #43
irsmalik
Newly Registered User
 
Join Date: Jan 2015
Location: Multan - Pakistan
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
irsmalik is on a distinguished road
Send a message via Skype™ to irsmalik
Re: Multi-Select Listbox as filter for Query field

No Sir pbaldy...
it is giving me word "whatever"

I want to display the value of FAS, if it is Less then 10 Number like 9, 8 or so on.
in query it must display....
Expr1: IIf([FAS2]>=10,"10")
if less than 10
Expr1: IIf([FAS2]>=10,"10", iif [FAS]<10, [FAS])
any one plz correct my query.
thanks
irsmalik
irsmalik is offline   Reply With Quote
Old 01-07-2019, 09:47 AM   #44
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,399
Thanks: 13
Thanked 4,125 Times in 4,057 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Multi-Select Listbox as filter for Query field

Yes, "whatever" meant you should put the desired result there. It's hard to correct when we don't know what the exact goal is. You've referred to 2 different fields there; is that correct? If not:

Expr1: IIf([FAS2]>=10,"10", [FAS2])

You're also mixing text and numbers there, which can give you odd results.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 02-12-2019, 12:11 AM   #45
irsmalik
Newly Registered User
 
Join Date: Jan 2015
Location: Multan - Pakistan
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
irsmalik is on a distinguished road
Send a message via Skype™ to irsmalik
Re: Multi-Select Listbox as filter for Query field

Hello Friends.........
In the Data Entry Form..... I am using following fields by using Combo Box
Company Name.......... Combo Box .. and get Company ID by using DLookup
Region Name ............ Combo Box .. and get RegionID by using Dlookup
Area Name................ Combo Box ... and get AreaID by using Dlookup...
This is working OK....

I want to Save the values I got from Dlookup........ plz some one guide me how to do it.....

Thanks
Irshad Ahmed

irsmalik is offline   Reply With Quote
Reply

Tags
listbox , multi-select , query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Use Records in Multi Select Listbox in query TimTDP Modules & VBA 2 09-19-2012 08:33 AM
Filter Report based on Multi-Select Listbox bstboy Forms 3 10-24-2011 05:42 AM
Filter query by multi select listbox AccessDev Forms 3 09-02-2006 11:51 AM
Multi Select ListBox criteria in query oihjk Forms 1 04-01-2003 03:58 PM
[SOLVED] Using a Multi-select listbox to populate a DB field mackdaddy Forms 1 07-10-2000 02:48 PM




All times are GMT -8. The time now is 12:06 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World