By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,121 Members | 1,706 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

Problem passing in parameter dates from form to query. (Urgent)

P: 33
Hi there,

I have a form with which I enter data. A sort order, start date and end date.

Once the data is entered, a report is generated. But I get and error.

Error: "The Microsoft Access database engine does not recognize '[Forms]![AuditPTETotals]![startDate]' as a valid field name or expression.

This is my crosstab (AuditPTETotals_CrosstabDateRange) query which uses the data retrieved by another query (AuditPTETotalsDateRange)

Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Sum(AuditPTETotalsDateRange.PTEtotal) AS SumOfPTEtotal
  2. SELECT AuditPTETotalsDateRange.companyName, AuditPTETotalsDateRange.regNum, Sum(AuditPTETotalsDateRange.PTEtotal) AS [Total Of PTEtotal]
  3. FROM AuditPTETotalsDateRange
  4. GROUP BY AuditPTETotalsDateRange.companyName, AuditPTETotalsDateRange.regNum
  5. PIVOT AuditPTETotalsDateRange.description;
And this is AuditPTETotalsDateRange

Expand|Select|Wrap|Line Numbers
  1. SELECT CUSTOMER.regNum, CUSTOMER.companyName, ScrapTireType.description, ScrapTireType.PTEamount, ScrapCollectionTireType.amount, ScrapCollection.PTEtotal, ScrapCollection.Date
  2. FROM (ScrapCollection INNER JOIN CUSTOMER ON ScrapCollection.regNum = CUSTOMER.regNum) INNER JOIN (ScrapTireType INNER JOIN ScrapCollectionTireType ON (ScrapTireType.scrapTireTypeID = ScrapCollectionTireType.scrapTireTypeID) AND (ScrapTireType.scrapTireTypeID = ScrapCollectionTireType.scrapTireTypeID)) ON ScrapCollection.scrapCollectionID = ScrapCollectionTireType.scrapCollectionID
  3. WHERE (((ScrapCollection.Date) Between [Forms]![AuditPTETotals]![startDate] And [Forms]![AuditPTETotals]![endDate]));
I have used this approach in a similar way to reference forms fields in a query but this time, I am not sure what I'm doing wrong. Please help.

Thank you
Jun 4 '12 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 100+
P: 2,321
Its been a while since I have referenced form controls in queries, but my suggestion would be:
Try removing the square brackets you have placed around Forms
Jun 4 '12 #2

P: 33
Thanks for the reply but no luck. It's really weird because I have referenced forms fields in queries like this before and have had no problems. Could it be because I am using a query as my source for my cross tab query?
Jun 4 '12 #3

P: 393
If it's a bound field, check to be sure that the field name and the control name aren't the same.
Jun 4 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.