referencing a form field's value, in a crosstab query?

[Mark]

I am struggling with the referencing a form field's value, within a crosstab query. The SQL is:

TRANSFORM Sum(tbl2.PO_UNIT_PRICE*tbl2.PO_QUANTITY_ORDERED) AS Amount
SELECT "dB" AS Source, Year([PO_NEED_BY_DATE]) AS [Year], tbl3.PO_VENDOR_NAME AS Vendor, tbl2.PO_ITEM_MANUFACTURER AS Manufacturer
FROM tbl1 INNER JOIN (((tbl4 INNER JOIN tbl5 ON tbl4.RQSTN_DIST_ID = tbl5.RQSTN_DIST_ID) INNER JOIN tbl2 ON tbl4.PO_LINE_ID = tbl2.PO_LINE_ID) INNER JOIN tbl3 ON tbl2.PO_HEADER_ID = tbl3.PO_HEADER_ID) ON tbl1.PO_LINE_ID = tbl2.PO_LINE_ID
WHERE (((tbl1.PO_NEED_BY_DATE)>=[Forms]![iFrmRptSel]![txtStart]) AND ((tbl2.PO_CANCEL_FLAG)="N" Or (tbl2.PO_CANCEL_FLAG) Is Null) AND ((tbl3.PO_CANCEL_FLAG)="N" Or (tbl3.PO_CANCEL_FLAG) Is Null)) OR (((tbl2.PO_CANCEL_FLAG)="N" Or (tbl2.PO_CANCEL_FLAG) Is Null) AND ((tbl3.PO_CANCEL_FLAG)="N" Or (tbl3.PO_CANCEL_FLAG) Is Null))
GROUP BY "dB", Year([PO_NEED_BY_DATE]), tbl3.PO_VENDOR_NAME, tbl2.PO_ITEM_MANUFACTURER
PIVOT tbl5.RQSTN_EXPENSE_TYPE;

I get 'The Microsoft Jet database engine does not recognize <name> as a valid field name or expression. (Error 3070)' as the error mssg, with the [Forms]![iFrmRptSel]![txtStart] as the 'name' it is referring to.

Does it think its a parameter? Is there a different syntax I should use?

 

[Chris Large]

Hi

You need to declare any parameters used in a crosstab
query. Add the following as the first line of your SQL

PARAMETERS [Forms]![iFrmRptSel]![txtStart] Text ( 255 );

hth

Chris

-----Original Message-----
I am struggling with the referencing a form field's

value, within a crosstab query. The SQL is:

TRANSFORM Sum

(tbl2.PO_UNIT_PRICE*tbl2.PO_QUANTITY_ORDERED) AS Amount

SELECT "dB" AS Source, Year([PO_NEED_BY_DATE]) AS [Year],

tbl3.PO_VENDOR_NAME AS Vendor, tbl2.PO_ITEM_MANUFACTURER
AS Manufacturer

FROM tbl1 INNER JOIN (((tbl4 INNER JOIN tbl5 ON

tbl4.RQSTN_DIST_ID = tbl5.RQSTN_DIST_ID) INNER JOIN tbl2
ON tbl4.PO_LINE_ID = tbl2.PO_LINE_ID) INNER JOIN tbl3 ON
tbl2.PO_HEADER_ID = tbl3.PO_HEADER_ID) ON tbl1.PO_LINE_ID
= tbl2.PO_LINE_ID

WHERE (((tbl1.PO_NEED_BY_DATE)>=[Forms]![iFrmRptSel]!

[txtStart]) AND ((tbl2.PO_CANCEL_FLAG)="N" Or
(tbl2.PO_CANCEL_FLAG) Is Null) AND ((tbl3.PO_CANCEL_FLAG)
="N" Or (tbl3.PO_CANCEL_FLAG) Is Null)) OR
(((tbl2.PO_CANCEL_FLAG)="N" Or (tbl2.PO_CANCEL_FLAG) Is
Null) AND ((tbl3.PO_CANCEL_FLAG)="N" Or
(tbl3.PO_CANCEL_FLAG) Is Null))

GROUP BY "dB", Year([PO_NEED_BY_DATE]),

tbl3.PO_VENDOR_NAME, tbl2.PO_ITEM_MANUFACTURER

PIVOT tbl5.RQSTN_EXPENSE_TYPE;

I get 'The Microsoft Jet database engine does not

recognize <name> as a valid field name or expression.
(Error 3070)' as the error mssg, with the [Forms]!
[iFrmRptSel]![txtStart] as the 'name' it is referring to.

 

[Rebecca Riordan]

Your syntax appears correct, are you sure that the form is open, and that
you haven't misspelled anything?

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

I am struggling with the referencing a form field's value, within a crosstab query. The SQL is:

TRANSFORM Sum(tbl2.PO_UNIT_PRICE*tbl2.PO_QUANTITY_ORDERED) AS Amount
SELECT "dB" AS Source, Year([PO_NEED_BY_DATE]) AS [Year],

tbl3.PO_VENDOR_NAME AS Vendor, tbl2.PO_ITEM_MANUFACTURER AS Manufacturer

FROM tbl1 INNER JOIN (((tbl4 INNER JOIN tbl5 ON tbl4.RQSTN_DIST_ID =

tbl5.RQSTN_DIST_ID) INNER JOIN tbl2 ON tbl4.PO_LINE_ID = tbl2.PO_LINE_ID)
INNER JOIN tbl3 ON tbl2.PO_HEADER_ID = tbl3.PO_HEADER_ID) ON tbl1.PO_LINE_ID
= tbl2.PO_LINE_ID

WHERE (((tbl1.PO_NEED_BY_DATE)>=[Forms]![iFrmRptSel]![txtStart]) AND

((tbl2.PO_CANCEL_FLAG)="N" Or (tbl2.PO_CANCEL_FLAG) Is Null) AND
((tbl3.PO_CANCEL_FLAG)="N" Or (tbl3.PO_CANCEL_FLAG) Is Null)) OR
(((tbl2.PO_CANCEL_FLAG)="N" Or (tbl2.PO_CANCEL_FLAG) Is Null) AND
((tbl3.PO_CANCEL_FLAG)="N" Or (tbl3.PO_CANCEL_FLAG) Is Null))

GROUP BY "dB", Year([PO_NEED_BY_DATE]), tbl3.PO_VENDOR_NAME, tbl2.PO_ITEM_MANUFACTURER
PIVOT tbl5.RQSTN_EXPENSE_TYPE;

I get 'The Microsoft Jet database engine does not recognize <name> as a

valid field name or expression. (Error 3070)' as the error mssg, with the
[Forms]![iFrmRptSel]![txtStart] as the 'name' it is referring to.

 

[Mark]

Chris, that gets me further, but I then get

---
This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables. (Error 3071)
Possible causes:

You typed an expression that has invalid syntax. For example, an operand or operator may be missing, you may have typed an invalid character or comma, or you may have typed text without enclosing it with quotation marks ( " ). Check the expression to make sure you typed it correctly.
You typed an expression that is too complex. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning some parts to variables.
---

Any ideas? (And yes the form/field is accurate). Thanks so much...


----- Chris Large wrote: -----

Hi

You need to declare any parameters used in a crosstab
query. Add the following as the first line of your SQL

PARAMETERS [Forms]![iFrmRptSel]![txtStart] Text ( 255 );

hth

Chris

-----Original Message-----
I am struggling with the referencing a form field's

value, within a crosstab query. The SQL istbl2.PO_UNIT_PRICE*tbl2.PO_QUANTITY_ORDERED) AS Amount

SELECT "dB" AS Source, Year([PO_NEED_BY_DATE]) AS [Year],

tbl3.PO_VENDOR_NAME AS Vendor, tbl2.PO_ITEM_MANUFACTURER
AS Manufacturer

FROM tbl1 INNER JOIN (((tbl4 INNER JOIN tbl5 ON

tbl4.RQSTN_DIST_ID = tbl5.RQSTN_DIST_ID) INNER JOIN tbl2
ON tbl4.PO_LINE_ID = tbl2.PO_LINE_ID) INNER JOIN tbl3 ON
tbl2.PO_HEADER_ID = tbl3.PO_HEADER_ID) ON tbl1.PO_LINE_ID
= tbl2.PO_LINE_ID

WHERE (((tbl1.PO_NEED_BY_DATE)>=[Forms]![iFrmRptSel]!

[txtStart]) AND ((tbl2.PO_CANCEL_FLAG)="N" Or
(tbl2.PO_CANCEL_FLAG) Is Null) AND ((tbl3.PO_CANCEL_FLAG)
="N" Or (tbl3.PO_CANCEL_FLAG) Is Null)) OR
(((tbl2.PO_CANCEL_FLAG)="N" Or (tbl2.PO_CANCEL_FLAG) Is
Null) AND ((tbl3.PO_CANCEL_FLAG)="N" Or
(tbl3.PO_CANCEL_FLAG) Is Null))

GROUP BY "dB", Year([PO_NEED_BY_DATE]),

tbl3.PO_VENDOR_NAME, tbl2.PO_ITEM_MANUFACTURER

PIVOT tbl5.RQSTN_EXPENSE_TYPE;

recognize <name> as a valid field name or expression.
(Error 3070)' as the error mssg, with the [Forms]!
[iFrmRptSel]![txtStart] as the 'name' it is referring to.

 

[Duane Hookom]

You must select Query|Parameters and enter:
[Forms]![iFrmRptSel]![txtStart] Date/Time

--
Duane Hookom
MS Access MVP

I am struggling with the referencing a form field's value, within a crosstab query. The SQL is:

TRANSFORM Sum(tbl2.PO_UNIT_PRICE*tbl2.PO_QUANTITY_ORDERED) AS Amount
SELECT "dB" AS Source, Year([PO_NEED_BY_DATE]) AS [Year],

tbl3.PO_VENDOR_NAME AS Vendor, tbl2.PO_ITEM_MANUFACTURER AS Manufacturer

FROM tbl1 INNER JOIN (((tbl4 INNER JOIN tbl5 ON tbl4.RQSTN_DIST_ID =

tbl5.RQSTN_DIST_ID) INNER JOIN tbl2 ON tbl4.PO_LINE_ID = tbl2.PO_LINE_ID)
INNER JOIN tbl3 ON tbl2.PO_HEADER_ID = tbl3.PO_HEADER_ID) ON tbl1.PO_LINE_ID
= tbl2.PO_LINE_ID

WHERE (((tbl1.PO_NEED_BY_DATE)>=[Forms]![iFrmRptSel]![txtStart]) AND

((tbl2.PO_CANCEL_FLAG)="N" Or (tbl2.PO_CANCEL_FLAG) Is Null) AND
((tbl3.PO_CANCEL_FLAG)="N" Or (tbl3.PO_CANCEL_FLAG) Is Null)) OR
(((tbl2.PO_CANCEL_FLAG)="N" Or (tbl2.PO_CANCEL_FLAG) Is Null) AND
((tbl3.PO_CANCEL_FLAG)="N" Or (tbl3.PO_CANCEL_FLAG) Is Null))

GROUP BY "dB", Year([PO_NEED_BY_DATE]), tbl3.PO_VENDOR_NAME, tbl2.PO_ITEM_MANUFACTURER
PIVOT tbl5.RQSTN_EXPENSE_TYPE;

I get 'The Microsoft Jet database engine does not recognize <name> as a

valid field name or expression. (Error 3070)' as the error mssg, with the
[Forms]![iFrmRptSel]![txtStart] as the 'name' it is referring to.