UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Parsin Query Parametar Into Report, Access 2010    
 
   
dzoker
post Jan 18 2020, 02:09 PM
Post#1



Posts: 457
Joined: 12-February 14



Hi All,

I have a query qryReportYear
QUOTE
SELECT [Work Hours Extended].[Employee Name], [Work Hours Extended].[Date Worked], [Work Hours Extended].Shield, DateDiff("n",[Date worked]+[HoursFrom],[Date Worked]+IIf([HoursTo]=0,1,0)+[HoursTo]) AS Diff, Format(Int(([Diff])/60),"00") & ":" & Format(([Diff] Mod 60),"00") AS TotalHours, [Work Hours Extended].DateSeparated, Format([Date Worked],"yyyy") AS [Year], [Work Hours Extended].YouthSquad
FROM [Work Hours Extended]
WHERE ((([Work Hours Extended].DateSeparated) Is Null) AND (([Work Hours Extended].YouthSquad) Like "*" & [Youth Squad? Enter -1 for YES, 0 for NO or leave blank for all] & "*" Or ([Work Hours Extended].YouthSquad) Is Null) AND ((Year([Date Worked]))=[Enter Year]));


That feeds a crosstab query qryReport_Crosstab

QUOTE
TRANSFORM Sum(qryReportYear.Diff) AS TotalHours
SELECT qryReportYear.[Employee Name], qryReportYear.Shield, Sum(qryReportYear.Diff) AS Total
FROM qryReportYear
GROUP BY qryReportYear.[Employee Name], qryReportYear.Shield
PIVOT Format([Date Worked],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


That feeds a report.

In the qryReportYear I have a criteria parameter [Enter Year]. Is there a way to pass that value that the user enters to a report textbox?

ty

Go to the top of the page
 
June7
post Jan 18 2020, 02:31 PM
Post#2



Posts: 1,241
Joined: 25-January 16
From: The Great Land


I never use input popup parameters - reference a control on form. Then report can reference form control.

Otherwise, calculate a field with the same parameter and bind textbox to that constructed field. Example:

SELECT *, [Enter Year] AS Yr FROM table WHERE Year([DateWorked]) = [Enter Year];

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
dzoker
post Jan 18 2020, 02:43 PM
Post#3



Posts: 457
Joined: 12-February 14



June7,

Thank you for your reply!

QUOTE
I never use input popup parameters - reference a control on form. Then report can reference form control.
- In this case I could not do that due to customer request

QUOTE
Otherwise, calculate a field with the same parameter and bind textbox to that constructed field. Example:

SELECT *, [Enter Year] AS Yr FROM table WHERE Year([DateWorked]) = [Enter Year];
- Worked perfectly

Thank you
Go to the top of the page
 
June7
post Jan 18 2020, 03:00 PM
Post#4



Posts: 1,241
Joined: 25-January 16
From: The Great Land


Perhaps client would be more accepting of form input if they understood the perils of popup inputs. Can't validate user input - query will accept any input and if not valid, still runs but errors, resulting in user frustration and lost productivity.

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
dzoker
post Jan 18 2020, 03:22 PM
Post#5



Posts: 457
Joined: 12-February 14



June7,

QUOTE
Perhaps client would be more accepting of form input if they understood the perils of popup inputs. Can't validate user input - query will accept any input and if not valid, still runs but errors, resulting in user frustration and lost productivity.

Hmm...good thinking..I'll pass that info.

Thank you again!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th February 2020 - 07:12 PM