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

M

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?
 
C

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.
 
R

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...

Mark said:
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.
 
M

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 is:(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;
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.
 
D

Duane Hookom

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

--
Duane Hookom
MS Access MVP


Mark said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top