Solved Parameters from Unbound Fields Not Appearing in Query Results

[AngelSpeaks]

Hi Everyone,

I have a database that has a Navigation form. One of the forms has date fields for StartDate and EndDate for date selection.

My query is using these dates for selection criteria with no problem. My problem is I want the StartDate and EndDate to display in the query results.

The criteria is set up as:

Between [Forms]![frmNavigation]![NavigationSubform].[Form]![StartDate] And [Forms]![frmNavigation]![NavigationSubform].[Form]![EndDate]
The SQL for this is: HAVING (((tblPWBenefits.Date) Between [Forms]![frmNavigation]![NavigationSubform].[Form]![StartDate] And [Forms]![frmNavigation]![NavigationSubform].[Form]![EndDate]))

The column to display is set up as:

PayrollStart: [Forms]![frmNavigation]![NavigationSubform].[Form]![StartDate]
The SQL version is: SELECT [Forms]![frmNavigation]![NavigationSubform].[Form]![StartDate] AS PayrollStart, [Forms]![frmNavigation]![NavigationSubform].[Form]![EndDate] AS PayrollEnd

The only time I get something to display in PayrollStart is when I execute the query outside of the Criteria form and I get prompted to enter the dates.

Thanks

Cathy

 

[theDBguy]

Hi. That looks fine from here. Can you please post the complete SQL for the query? Thanks!

 

[arnelgp]

which form are you executing the query? another tab of navigation form?
if so it's a no, no. only one subform can appear in the navigationsubform at
any given time.
better assign to a Tempvars collection those start/end date on the current
event of your first subform. then use the Tempvars as Criteria of your query.

 

[AngelSpeaks]

Hi. That looks fine from here. Can you please post the complete SQL for the query? Thanks!

Thanks for responding. Here goes, its quite large:

SELECT [Forms]![frmNavigation]![NavigationSubform].[Form]![StartDate] AS PayrollStart, [Forms]![frmNavigation]![NavigationSubform].[Form]![EndDate] AS PayrollEnd, tblEmployers.ContractorNo AS ContractNumber, tblPWBenefits.Job AS ProjectNumber, qryJobsPublic.Address AS ProjectAddress, qryJobsPublic.County AS ProjectCounty, qryJobsPublic.CountyFIPS AS ProjectCountyFIPSCode, qryJobsPublic.City AS ProjectCity, qryJobsPublic.State AS ProjectState, qryJobsPublic.ZipCode AS ProjectZip5, qryJobsPublic.Zip4 AS [Zip-4], IIf([qryJobsPublic].[CapitalFund],"1","0") AS StateCapitalFunds, tblEmployers.Employer_Name AS CompanyName, tblEmployers.Contact_First AS ContactFirstName, tblEmployers.Contact_Middle AS ContactMiddleName, tblEmployers.Contact_Person AS ContactLastName, tblEmployers.Address_Line1 AS PostalAddress, tblEmployers.CountyFIPSCode, tblEmployers.City, tblEmployers.State, tblEmployers.ZipCode AS Zip5, tblEmployers.Zip4 AS [Zip 4], tblEmployers.Telephone AS PrimaryPhone, tblEmployers.FAX AS SecondaryPhone, tblEmployers.Email AS PrimaryEmail, tblEmployers.Secondary_Email AS SecondaryEmail, qryJobsPublic.PublicBodyName, qryJobsPublic.Contact_Person AS PublicBodyContactFirstName, qryJobsPublic.Contact_Middle AS PublicBodyContactMiddleName, qryJobsPublic.Contact_Person AS PublicBodyContactLastName, qryJobsPublic.PublicBodyAddress, qryJobsPublic.PublicBodyCounty, qryJobsPublic.PBCountyFIPS AS PublicBodyCountyFIPS, qryJobsPublic.PublicBodyCity, qryJobsPublic.PublicBodyState, qryJobsPublic.PublicBodyZip, qryJobsPublic.PublicBodyZip4, qryJobsPublic.PublicBodyTelephone, qryJobsPublic.PublicBodySecondaryTelephone, tblEmployees.First_Name AS FirstName, tblEmployees.Middle_Name AS MiddleName, tblEmployees.Last_Name AS LastName, tblEmployees.SSN4 AS Last4SSN, tblEmployees.Telephone, tblEmployees.SecondaryTelephone, tblEmployees.TradeTitle AS WorkClassification, tblEmployees.Address, tblEmployees.County, " " AS EmployeeCountyFIPS, tblEmployees.City AS [Employee City], tblEmployees.State AS [Employee State], tblEmployees.ZipCode, tblEmployees.Zip4, tblEmployees.Race, IIf([tblEmployees]![Ethnicity],"Y","N") AS Ethnicity, tblEmployees.Gender, IIf([tblEmployees].[Veteran],"1","0") AS IsaVeteran, IIf([tblEmployees].[Journeyman],"1","0") AS Journeyman, IIf([tblEmployees].[Apprentice],"1","0") AS Apprentice, IIf([tblEmployees].[Foreman],"1","0") AS Foreman, tblPWBenefits.PWSunday AS SundayPrevailingHoursWorked, tblPWBenefits.PWSunday AS SundayNonPrevailingHoursWorked, tblPWBenefits.PWMonday AS MondayPrevailingHoursWorked, tblPWBenefits.NPWMonday AS MondayNonPrevailingHoursWorked, tblPWBenefits.PWTuesday AS TuesdayPrevailingHoursWorked, tblPWBenefits.NPWTuesday AS TuesdayNonPrevailingHoursWorked, tblPWBenefits.PWWednesday AS WednsdayPrevailingHoursWorked, tblPWBenefits.NPWWednesday AS WednsdayNonPrevailingHoursWorked, tblPWBenefits.PWThursday AS ThursdayPrevailingHoursWorked, tblPWBenefits.NPWThursday AS ThursdayNonPrevailingHoursWorked, tblPWBenefits.PWFriday AS FridayPrevailingHoursWorked, tblPWBenefits.NPWFriday AS FridayNonPrevailingHoursWorked, tblPWBenefits.PWSaturday AS SatdayPrevailingHoursWorked, tblPWBenefits.NPWSaturday AS SatdayNonPrevailingHoursWorked, tblPWBenefits.[Total Hrs] AS TotalPrevailingStraightHoursWorked, 0 AS TotalNonPrevailingStraightHoursWorked, tblPWBenefits.OTHrs AS TotalPrevailingOTHoursWorked, 0 AS TotalNonPrevailingOTHoursWorked, tblPWBenefits.[Base Wage] AS PrevailingHourlyWage, 0 AS NonPrevailingHourlyWage, tblPWBenefits.[OT Wage] AS PrevailingOTWage, 0 AS NonPrevailingOTWage, tblPWBenefits.[Gross Earned] AS PrevailingGrossIncome, 0 AS PrevailingNetIncome, 0 AS NonPrevailingGrossIncome, 0 AS NonPrevailingNetIncome, tblPWBenefits.[Pension Earned] AS HourlyPensionRetirement, tblPWBenefits.[HW Rate] AS HourlyHealthInsuranceWelfare, tblPWBenefits.[Vacation Earned] AS HourlyVacationAccrued, tblPWBenefits.[Training Earned] AS HourlyTrainingAccrued, 0 AS IsFundJointlyManaged, 0 AS NonPrevailingDoubleTimeHours, tblPWBenefits.[DT Wage] AS PrevailingDoubleTimeWageRate, 0 AS NonPrevailingDoubleTimeWageRate, 0 AS NoWorktoReport FROM tblEmployers, (tblCounty INNER JOIN (tblPWBenefits INNER JOIN tblEmployees ON tblPWBenefits.Employee = tblEmployees.Employee) ON tblCounty.County = tblPWBenefits.County) INNER JOIN qryJobsPublic ON (tblPWBenefits.Job = qryJobsPublic.[Job]) AND (tblCounty.County = qryJobsPublic.[County]) GROUP BY tblEmployers.ContractorNo, tblPWBenefits.Job, qryJobsPublic.Address, qryJobsPublic.County, qryJobsPublic.CountyFIPS, qryJobsPublic.City, qryJobsPublic.State, qryJobsPublic.ZipCode, qryJobsPublic.Zip4, IIf([qryJobsPublic].[CapitalFund],"1","0"), tblEmployers.Employer_Name, tblEmployers.Contact_First, tblEmployers.Contact_Middle, tblEmployers.Contact_Person, tblEmployers.Address_Line1, tblEmployers.CountyFIPSCode, tblEmployers.City, tblEmployers.State, tblEmployers.ZipCode, tblEmployers.Zip4, tblEmployers.Telephone, tblEmployers.FAX, tblEmployers.Email, tblEmployers.Secondary_Email, qryJobsPublic.PublicBodyName, qryJobsPublic.Contact_Middle, qryJobsPublic.Contact_Person, qryJobsPublic.PublicBodyAddress, qryJobsPublic.PublicBodyCounty, qryJobsPublic.PBCountyFIPS, qryJobsPublic.PublicBodyCity, qryJobsPublic.PublicBodyState, qryJobsPublic.PublicBodyZip, qryJobsPublic.PublicBodyZip4, qryJobsPublic.PublicBodyTelephone, qryJobsPublic.PublicBodySecondaryTelephone, tblEmployees.First_Name, tblEmployees.Middle_Name, tblEmployees.Last_Name, tblEmployees.SSN4, tblEmployees.Telephone, tblEmployees.SecondaryTelephone, tblEmployees.TradeTitle, tblEmployees.Address, tblEmployees.County, " ", tblEmployees.City, tblEmployees.State, tblEmployees.ZipCode, tblEmployees.Zip4, tblEmployees.Race, IIf([tblEmployees]![Ethnicity],"Y","N"), tblEmployees.Gender, IIf([tblEmployees].[Veteran],"1","0"), IIf([tblEmployees].[Journeyman],"1","0"), IIf([tblEmployees].[Apprentice],"1","0"), IIf([tblEmployees].[Foreman],"1","0"), tblPWBenefits.PWSunday, tblPWBenefits.PWMonday, tblPWBenefits.NPWMonday, tblPWBenefits.PWTuesday, tblPWBenefits.NPWTuesday, tblPWBenefits.PWWednesday, tblPWBenefits.NPWWednesday, tblPWBenefits.PWThursday, tblPWBenefits.NPWThursday, tblPWBenefits.PWFriday, tblPWBenefits.NPWFriday, tblPWBenefits.PWSaturday, tblPWBenefits.NPWSaturday, tblPWBenefits.[Total Hrs], tblPWBenefits.OTHrs, tblPWBenefits.[Base Wage], tblPWBenefits.[OT Wage], tblPWBenefits.[Gross Earned], tblPWBenefits.[Pension Earned], tblPWBenefits.[HW Rate], tblPWBenefits.[Vacation Earned], tblPWBenefits.[Training Earned], tblPWBenefits.[DT Wage], 0, tblPWBenefits.Employee, tblPWBenefits.Date HAVING (((tblPWBenefits.Date) Between [Forms]![frmNavigation]![NavigationSubform].[Form]![StartDate] And [Forms]![frmNavigation]![NavigationSubform].[Form]![EndDate])) ORDER BY tblPWBenefits.Job, tblPWBenefits.Employee;

 

[AngelSpeaks]

which form are you executing the query? another tab of navigation form?
if so it's a no, no. only one subform can appear in the navigationsubform at
any given time.
better assign to a Tempvars collection those start/end date on the current
event of your first subform. then use the Tempvars as Criteria of your query.

Yes, the Navigation form has a tab to the Criteria form that has the StartDate and EndDate. The selection criteria works great, I just can'r get it on the answer set. Tempvars is new to me (well many Access stuff is new to me). I shall look into it. Thanks for the suggestion.

 

[Pat Hartman]

Is -- NavigationSubform --- the name of the subform CONTROL? You need to be referencing the control Name rather than the name of the subform object itself.

Also, WHERE and HAVING control selection but they do it at different points in time and both can actually be used in the same query but when you use aggregation, the QBE automatically assumes HAVING. It doesn't make a determination as to which would be more efficient or if you could benefit from using both.

WHERE should always be used when the criteria is against the data as it is selected, which is what you are doing.
HAVING is used whenever you ae aggregating data and you want to select records based on the aggregation.

So for example if you are summing the order amount by customer and you want to select customers whose order values total more than $1000, you would use having But if you want to select only customers from Connecticut, you would use a WHRE clause for that.

Why? Where operates at the beginning of the process and can use indexes so it is more efficient at selecting/eliminating data up front. So in the example above, it would be less efficient to aggregate the data for ALL customers regardless of which state they were in and only at the end elminate all the non-Connecticut values.

 

[AngelSpeaks]

Is -- NavigationSubform --- the name of the subform CONTROL? You need to be referencing the control Name rather than the name of the subform object itself.

Also, WHERE and HAVING control selection but they do it at different points in time and both can actually be used in the same query but when you use aggregation, the QBE automatically assumes HAVING. It doesn't make a determination as to which would be more efficient or if you could benefit from using both.

WHERE should always be used when the criteria is against the data as it is selected, which is what you are doing.
HAVING is used whenever you ae aggregating data and you want to select records based on the aggregation.

So for example if you are summing the order amount by customer and you want to select customers whose order values total more than $1000, you would use having But if you want to select only customers from Connecticut, you would use a WHRE clause for that.

Why? Where operates at the beginning of the process and can use indexes so it is more efficient at selecting/eliminating data up front. So in the example above, it would be less efficient to aggregate the data for ALL customers regardless of which state they were in and only at the end elminate all the non-Connecticut values.

Hi Pat, thanks for responding. I used the Query Designer for the query. I don't know why it has HAVING. I noticed that too when I printed off the SQL, I've never heard of HAVING. I will change that. The form name is frmNavigation. The tab launches a frmCriteria, which has StartDate and EndDate. The query results have the rows with a date between those two dates. What I can't get is the StartDate and EndDate to display in the query results. Those columns are empty and the remaining columns are populated.

 

[AngelSpeaks]

which form are you executing the query? another tab of navigation form?
if so it's a no, no. only one subform can appear in the navigationsubform at
any given time.
better assign to a Tempvars collection those start/end date on the current
event of your first subform. then use the Tempvars as Criteria of your query.

I tried your suggestion on using TempVars and it worked! Thanks

 

[theDBguy]

I tried your suggestion on using TempVars and it worked! Thanks

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[Pat Hartman]

The form name is frmNavigation

It doesn't matter what the form name is. It only matters what the NAME property of the subform CONTROL is and with a navigation form, they are always different and that was most likely the problem.