engine does not recognize <name> as a valid field name or expression.(Error 3070)

[Phil]

Another BS error with Access.

I have a form, with two unbound fields, STARTDATE and ENDDATE.
I have a crosstab QUERY, with those two fields as criteria for the query.
I have those two variables in Query parameters.
I get the above error. I shouldn't, becasue they two fields are
perfectly valid.

What's worse, if I change it to a select query, it runs, but ASKS ME for
the values for STARTDATE and ENDDATE even though they are on the form.

My query as a crosstab:
PARAMETERS STARTDATE DateTime, ENDDATE DateTime;
TRANSFORM
Sum(invoice_d!ship_qty*invoice_d!price*(1-(invoice_d!discount/100))) AS
Ext_Price
SELECT customer.name, invoice_h.invoice_id
FROM reason INNER JOIN (territory INNER JOIN (customer_type INNER JOIN
(customer INNER JOIN (invoice_h INNER JOIN invoice_d ON
(invoice_h.company_id = invoice_d.company_id) AND (invoice_h.invoice_id
= invoice_d.invoice_id)) ON customer.customer_id =
invoice_h.customer_id) ON customer_type.customer_type_id =
customer.customer_type_id) ON territory.territory_id =
customer.territory_id) ON reason.reason_id = invoice_h.reason_id
WHERE
(((invoice_h.invoice_date)>=[Forms]![Credit_Reports_Form]![STARTDATE]
And (invoice_h.invoice_date)<=[Forms]![Credit_Reports_Form]![ENDDATE])
AND ((invoice_h.status)=8 Or (invoice_h.status)=9) AND
((territory.company_id)=1) AND ((invoice_h.company_id)=1) AND
((invoice_d.company_id)=1) AND ((customer.company_id)=1) AND
((customer_type.company_id)=1))
GROUP BY customer.name, invoice_h.invoice_id, invoice_h.inv_memo,
invoice_h.invoice_date
ORDER BY customer.name, invoice_h.invoice_id, reason.name
PIVOT reason.name;

My query as a select query"
PARAMETERS STARTDATE DateTime, ENDDATE DateTime;
SELECT customer.name, invoice_h.invoice_id, reason.name,
Sum(invoice_d!ship_qty*invoice_d!price*(1-(invoice_d!discount/100))) AS
Ext_Price
FROM reason INNER JOIN (territory INNER JOIN (customer_type INNER JOIN
(customer INNER JOIN (invoice_h INNER JOIN invoice_d ON
(invoice_h.company_id = invoice_d.company_id) AND (invoice_h.invoice_id
= invoice_d.invoice_id)) ON customer.customer_id =
invoice_h.customer_id) ON customer_type.customer_type_id =
customer.customer_type_id) ON territory.territory_id =
customer.territory_id) ON reason.reason_id = invoice_h.reason_id
WHERE
(((invoice_h.invoice_date)>=[Forms]![Credit_Reports_Form]![STARTDATE]
And (invoice_h.invoice_date)<=[Forms]![Credit_Reports_Form]![ENDDATE])
AND ((invoice_h.status)=8 Or (invoice_h.status)=9) AND
((territory.company_id)=1) AND ((invoice_h.company_id)=1) AND
((invoice_d.company_id)=1) AND ((customer.company_id)=1) AND
((customer_type.company_id)=1))
GROUP BY customer.name, invoice_h.invoice_id, invoice_h.inv_memo,
invoice_h.invoice_date, reason.name
ORDER BY customer.name, invoice_h.invoice_id, reason.name;

 

[Duane Hookom]

Should be
PARAMETERS [Forms]![Credit_Reports_Form]![STARTDATE] DateTime,
[Forms]![Credit_Reports_Form]![ENDDATE] DateTime;


--
Duane Hookom
MS Access MVP

Another BS error with Access.

I have a form, with two unbound fields, STARTDATE and ENDDATE.
I have a crosstab QUERY, with those two fields as criteria for the query.
I have those two variables in Query parameters.
I get the above error. I shouldn't, becasue they two fields are perfectly
valid.

What's worse, if I change it to a select query, it runs, but ASKS ME for
the values for STARTDATE and ENDDATE even though they are on the form.

My query as a crosstab:
PARAMETERS STARTDATE DateTime, ENDDATE DateTime;
TRANSFORM
Sum(invoice_d!ship_qty*invoice_d!price*(1-(invoice_d!discount/100))) AS
Ext_Price
SELECT customer.name, invoice_h.invoice_id
FROM reason INNER JOIN (territory INNER JOIN (customer_type INNER JOIN
(customer INNER JOIN (invoice_h INNER JOIN invoice_d ON
(invoice_h.company_id = invoice_d.company_id) AND (invoice_h.invoice_id =
invoice_d.invoice_id)) ON customer.customer_id = invoice_h.customer_id) ON
customer_type.customer_type_id = customer.customer_type_id) ON
territory.territory_id = customer.territory_id) ON reason.reason_id =
invoice_h.reason_id
WHERE
(((invoice_h.invoice_date)>=[Forms]![Credit_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Credit_Reports_Form]![ENDDATE]) AND
((invoice_h.status)=8 Or (invoice_h.status)=9) AND
((territory.company_id)=1) AND ((invoice_h.company_id)=1) AND
((invoice_d.company_id)=1) AND ((customer.company_id)=1) AND
((customer_type.company_id)=1))
GROUP BY customer.name, invoice_h.invoice_id, invoice_h.inv_memo,
invoice_h.invoice_date
ORDER BY customer.name, invoice_h.invoice_id, reason.name
PIVOT reason.name;

My query as a select query"
PARAMETERS STARTDATE DateTime, ENDDATE DateTime;
SELECT customer.name, invoice_h.invoice_id, reason.name,
Sum(invoice_d!ship_qty*invoice_d!price*(1-(invoice_d!discount/100))) AS
Ext_Price
FROM reason INNER JOIN (territory INNER JOIN (customer_type INNER JOIN
(customer INNER JOIN (invoice_h INNER JOIN invoice_d ON
(invoice_h.company_id = invoice_d.company_id) AND (invoice_h.invoice_id =
invoice_d.invoice_id)) ON customer.customer_id = invoice_h.customer_id) ON
customer_type.customer_type_id = customer.customer_type_id) ON
territory.territory_id = customer.territory_id) ON reason.reason_id =
invoice_h.reason_id
WHERE
(((invoice_h.invoice_date)>=[Forms]![Credit_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Credit_Reports_Form]![ENDDATE]) AND
((invoice_h.status)=8 Or (invoice_h.status)=9) AND
((territory.company_id)=1) AND ((invoice_h.company_id)=1) AND
((invoice_d.company_id)=1) AND ((customer.company_id)=1) AND
((customer_type.company_id)=1))
GROUP BY customer.name, invoice_h.invoice_id, invoice_h.inv_memo,
invoice_h.invoice_date, reason.name
ORDER BY customer.name, invoice_h.invoice_id, reason.name;

 

[Phil]

Should be
PARAMETERS [Forms]![Credit_Reports_Form]![STARTDATE] DateTime,
[Forms]![Credit_Reports_Form]![ENDDATE] DateTime;

That works, thanx. Funny thing is I tried that, although I put it into
the query parameters of the builder, rather then the SQL, and it dumped
complaining about brackets. God I hate Access.

Appreciate the help.

 

[Duane Hookom]

I love Access but it can be frustrating at times. There does seem to be an
"invalid bracketing" bug when specifying parameters. Many times I have gone
to the SQL view to remove extra []s that Access seems to think I need and
then complains about.

--
Duane Hookom
MS Access MVP
--

Should be
PARAMETERS [Forms]![Credit_Reports_Form]![STARTDATE] DateTime,
[Forms]![Credit_Reports_Form]![ENDDATE] DateTime;

That works, thanx. Funny thing is I tried that, although I put it into
the query parameters of the builder, rather then the SQL, and it dumped
complaining about brackets. God I hate Access.

Appreciate the help.