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

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:25
Joined
Aug 30, 2003
Messages
36,118
Thanks! I'm a legend...in my own mind. :p
 

irsmalik

Registered User.
Local time
Today, 17:25
Joined
Jan 28, 2015
Messages
88
Demo Database for Time Limit

Hello Friends
I want to include a time limit when a Demo Database run. Suppose I want my database run for 15 days and then it must Locked or Not Accept the User. Any guidance for this trick.

thanks
irsmalik
 

irsmalik

Registered User.
Local time
Today, 17:25
Joined
Jan 28, 2015
Messages
88
How to display Only One Record among many

Hello friends

I have a table having below fields.

DrawingId DrawingNumber Revision
111 555555 0
111 555555 1
111 555555 2
111 555555 3
111 555555 4

I want to display the Last Record through Query. Kindly guide me how to do it.
Thanks
irsmalik
 

Gasman

Enthusiastic Amateur
Local time
Today, 13:25
Joined
Sep 21, 2011
Messages
14,052
Please do not hijack other people's threads.:(

Please create a new thread with a relevant title.

For this question however, try sorting the query by Revision descending and select Top 1 for your required criteria.

HTH
 

irsmalik

Registered User.
Local time
Today, 17:25
Joined
Jan 28, 2015
Messages
88
Prints Months Name in Brackets with Comma

Hello Friends,
One more problem to solve and I need your help. I have a crosstab query is below that runs and give me my results.

TRANSFORM Sum(qBG.SumOfBG) AS SumOfSumOfBG
SELECT qBG.Company, qBG.Region, qBG.Territory, Sum(qBG.SumOfBG) AS [Total Of SumOfBG]
FROM qBG
GROUP BY qBG.Company, qBG.Region, qBG.Territory
PIVOT qBG.HeldOn In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

query runs and give me accurate results. But I do not want full months. I want only that months which contain data in brackets like (Jan,Feb,Mar) etc. Plz see picture attached.
 

Attachments

  • MyRequirement.jpg
    MyRequirement.jpg
    35.4 KB · Views: 85

irsmalik

Registered User.
Local time
Today, 17:25
Joined
Jan 28, 2015
Messages
88
Month & Date is not printing on Report

Hello Friends

I am selecting dates from a FORM to print my required data on report. My required data is displaying on Screen.. with the selected Dates... but when I print the report Report , it gives me error.... #Name?

This report has of 2 parts..... Part 1 for complete year data and Part 2 is for last 30 days data.... I select dates for last 30 days via a FORM..... & this dates are printed on Report... so far this is being displayed on Screen but not on Paper...


All the process is below:

Form Name: frmOne2
Report Name: SumryGLT4
Report Source: Query6, Query61a Query6b WithTerritoryGLT

Query6,
Expand|Select|Wrap|Line Numbers
SELECT DM.Class, DM.ClassName, DM.Size, DM.SizeName, Format([TDate],"yyyy") AS Period1, Sum(DM.FAS) AS SumOfFAS, Sum(DM.BG) AS SumOfBG, Sum(DM.Demo) AS SumOfDemo, Sum(DM.DTP) AS SumOfDTP, Sum(DM.FM) AS SumOfFM, Sum(DM.HPM) AS SumOfHPM, DM.Division
FROM DM
GROUP BY DM.Class, DM.ClassName, DM.Size, DM.SizeName, Format([TDate],"yyyy"), DM.Division, Format([TDate],"yyyy")
HAVING (((DM.Class)=2))
ORDER BY DM.SizeName;

Query61a,
Expand|Select|Wrap|Line Numbers
SELECT DM.Class, DM.ClassName, DM.Size, DM.SizeName, DM.TDate, Sum(DM.BG) AS SumOfBG, Sum(DM.Demo) AS SumOfDemo, Sum(DM.DTP) AS SumOfDTP, Sum(DM.FAS) AS SumOfFAS, Sum(DM.FM) AS SumOfFM, Sum(DM.HPM) AS SumOfHPM, DM.Division
FROM DM
GROUP BY DM.Class, DM.ClassName, DM.Size, DM.SizeName, DM.TDate, DM.Division
HAVING (((DM.Class)=2) And ((DM.TDate)>=forms!frmOne2!txtStartDate And (DM.TDate)<=forms!frmOne2!txtEndDate))
ORDER BY DM.ClassName, DM.TDate;

Query6b,
Expand|Select|Wrap|Line Numbers
SELECT Query6a1.Class, Query6a1.ClassName, Query6a1.Size, Query6a1.SizeName, Sum(Query6a1.SumOfFAS) AS SumOfSumOfFAS, Sum(Query6a1.SumOfFM) AS SumOfSumOfFM, Sum(Query6a1.SumOfDemo) AS SumOfSumOfDemo, Sum(Query6a1.SumOfHPM) AS SumOfSumOfHPM, Sum(Query6a1.SumOfBG) AS SumOfSumOfBG, Sum(Query6a1.SumOfDTP) AS SumOfSumOfDTP, Query6a1.Division
FROM Query6a1
GROUP BY Query6a1.Class, Query6a1.ClassName, Query6a1.Size, Query6a1.SizeName, Query6a1.Division;

WithTerritoryGLT
Expand|Select|Wrap|Line Numbers
SELECT Query6.ClassName AS CompanyName, Query6.Division, Query6.SizeName AS RegionName, Region.Territories, [Territories]*932 AS FASTarget, Query6.SumOfFAS AS A1, Round([A1]/[FASTarget]*100,2) AS [FAS%], Round([FASTarget]/12,2) AS FASMonth, Query6b.SumOfSumOfFAS AS A2, Round([A2]/[FASMonth]*100,2) AS [FASM%], [Territories]*104 AS FMTarget, Query6.SumOfFM AS B1, Round([B1]/[FMTarget]*100,2) AS [FM%], Round([FMTarget]/12,2) AS FMMonth, Query6b.SumOfSumOfFM AS B2, Round([B2]/[FMMonth]*100,2) AS [FMM%], [Territories]*75 AS DemoTarget, Query6.SumOfDemo AS C1, Round([C1]/[DEMOTarget]*100,2) AS [DEMO%], Round([DemoTarget]/12,2) AS DemoMonth, Query6b.SumOfSumOfDemo AS C2, Round([C2]/[DemoMonth]*100,2) AS [DemoM%], [Territories]*3 AS HPMTarget, Query6.SumOfHPM AS D1, Round([D1]/[HPMTarget]*100,2) AS [HMP%], Round([HPMTarget]/12,2) AS HPMMonth, Query6b.SumOfSumOfHPM AS D2, Round([D2]/[HPMMonth]*100,2) AS [HPM%], [Territories]*3 AS BGTarget, Query6.SumOfBG AS E1, Round([E1]/[BGTarget]*100,2) AS [BG%], Round([BGTarget]/12,2) AS BGMonth, Query6b.SumOfSumOfBG AS E2, Round([E2]/[BGMonth]*100,2) AS [BGM%], [Territories]*3 AS DTPTarget, Query6.SumOfDTP AS F1, Round([F1]/[DTPTarget]*100,2) AS [DTP%], Round([DTPTarget]/12,2) AS DTPMonth, Query6b.SumOfSumOfDTP AS F2, Round([F2]/[DTPMonth]*100,2) AS [DTPM%]
FROM (Query6 INNER JOIN Query6b ON (Query6.Class = Query6b.Class) AND (Query6.ClassName = Query6b.ClassName) AND (Query6.Size = Query6b.Size) AND (Query6.SizeName = Query6b.SizeName)) INNER JOIN Region ON (Query6b.SizeName = Region.RegionName) AND (Query6b.Size = Region.RegionID)
ORDER BY Query6.Division, Query6.SizeName;

Request for all friends for help.
Thanks
irsmalik
 

Attachments

  • report 1.jpg
    report 1.jpg
    103.3 KB · Views: 75
  • Report 2.jpg
    Report 2.jpg
    98.5 KB · Views: 76

irsmalik

Registered User.
Local time
Today, 17:25
Joined
Jan 28, 2015
Messages
88
May be I could not describe my problem.... kindly see the attached database and please run the form and select dates which will be displayed on Screen but will not print on paper and gives error.... #Names.

thanks
irsmalik
 

Attachments

  • MyDatabase.zip
    170.6 KB · Views: 94

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:25
Joined
Aug 30, 2003
Messages
36,118
Can't look at the sample right now, but it sounds like you're closing the form after opening the report. It has to be open when the report prints, though it can be hidden.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:25
Joined
Aug 30, 2003
Messages
36,118
Have looked at the sample and confirmed. You open the report and then close the form. The report will look for the criteria again when you print, so the form has to remain open. You can hide rather than close the form in the button code, and then close it in the report's close event.
 

irsmalik

Registered User.
Local time
Today, 17:25
Joined
Jan 28, 2015
Messages
88
Sir Pbaldy......... Thanks a Lot.

You Are Great........ You Are Really Great.... Superman....

You wouldn't believe how much tried I have made to remove this error... But always failed.....
Sir once again I am thankful to you and hoping for guidance in future.
irsmalik
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:25
Joined
Aug 30, 2003
Messages
36,118
Happy to help!
 

Users who are viewing this thread

Top Bottom