Query criteria from another form's text boxes?

1
votes

I'm trying to accomplish the following:

I want to allow the user to select a quarter from a form's combo box, then click a "View Report" button to view a report. The form's combo box has 4 selections for each quarter number. After the user makes a selection, two text boxes (StartQuarterDateTextbox & EndQuarterDateTextbox) are populated with the quarter's starting date in one and the quarter's ending date in the other.

I want to take the two dates in the text boxes and query on a table (in this case, the tblIdeaDetails table). It should take this date range and select any record with an ideaDateSubmitted field that falls either on or between this date range. The problem is every time the button is clicked, Access prompts me for the dates from the form.

This is what I have as my query (qryIdeasByQuarterDate):

SELECT tblIdeaDetails.ideaID, tblIdeaDetails.ideaDescription, tblIdeaDetails.ideaSubmitter, tblIdeaDetails.ideaDateSubmitted, tblIdeaDetails.ideaComments, tblBenefits.benefitAnimalWelfare, tblBenefits.benefitSafety, tblBenefits.benefitCostSavings, tblBenefits.benefitImprovedPractice, tblBenefits.benefitAdministrative, tblBenefits.benefitOther, tblStatus.status, tblStatus.statusComments, tblStatus.statusPayoutAmount
FROM (tblIdeaDetails INNER JOIN tblBenefits ON tblIdeaDetails.ideaID = tblBenefits.benefitID) INNER JOIN tblStatus ON tblIdeaDetails.ideaID = tblStatus.statusID
WHERE tblIdeaDetails.ideaDateSubmitted >= [Forms]![frmQuarter]![StartQuarterDateTextbox] And tblIdeaDetails.ideaDateSubmitted <= [Forms]![frmQuarter]![EndQuarterDateTextbox];

Is my WHERE clause not set up correctly? I've tried using parenthesis and a few other things, but I'm running out of ideas here.

1
sqlms-accessms-access-2010

1 Answers

0
votes

It sounds like the db engine can't find those text boxes. Reasons that can happen include:

  1. The form is not open in Form View.
  2. The form or text box name is misspelled.
  3. The query is run from outside the Access application session where the form is open.

If none of those apply, all I can suggest is to try simplified queries which focus on just the text boxes ... and hack on those until Access cooperates.

Create 2 new queries, each focused on one of those text boxes.

SELECT [Forms]![frmQuarter]![StartQuarterDateTextbox] AS start_date;

SELECT [Forms]![frmQuarter]![EndQuarterDateTextbox] AS end_date;