Pass Date/Time from Form to Crosstab

[Tom Brown]

I have created a crosstab query for a form which I am putting into a report
as a subform. I have created a popup form which asks the user to enter the
Settle date which he wants the data in the report to contain. I am having a
problem getting the crosstab query to accept the parameters for the textbox
in the popup form where the date is entered. I keep getting the "Invalid
bracketing of name '[#Forms!frm_Settle_Date!txt_Settle_Date#]' " error. I
have tried the name without the #, and several other ways with no success.

The SQL for the crosstab is below.

PARAMETERS [[#Forms]!frm_Settle_Date![txt_Settle_Date#]] DateTime;
TRANSFORM Sum(tbl_Dep_Summary.Dep_Amt) AS Amount
SELECT tbl_Dep_Summary.Dep_Slip_No AS [Dep Slip No],
tbl_Dep_Summary.Bank_Acct_No AS [Acct No], tbl_Dep_Summary.Dep_Bank_Name AS
[Bank Name], tbl_Dep_Summary.Dep_No_Bags AS [No Bags],
tbl_Dep_Summary.Settle_Date AS [Settle Date], Sum(tbl_Dep_Summary.Dep_Amt)
AS [Dep Total]
FROM tbl_Dep_Summary
GROUP BY tbl_Dep_Summary.Dep_Slip_No, tbl_Dep_Summary.Bank_Acct_No,
tbl_Dep_Summary.Dep_Bank_Name, tbl_Dep_Summary.Dep_No_Bags,
tbl_Dep_Summary.Settle_Date
PIVOT tbl_Dep_Summary.Dep_Type;

Can someone please tell me how to set the parameters correctly?

Thanks,

Tom Brown

 

[Guest]

Hi Tom,

You don't need the hashes at all, just reference the field on the form.

Forms!frm_Settle_Date.txt_Settle_Date

Hope that helps.

Damian.

 

[John W. Vinson]

I have created a crosstab query for a form which I am putting into a report
as a subform. I have created a popup form which asks the user to enter the
Settle date which he wants the data in the report to contain. I am having a
problem getting the crosstab query to accept the parameters for the textbox
in the popup form where the date is entered. I keep getting the "Invalid
bracketing of name '[#Forms!frm_Settle_Date!txt_Settle_Date#]' " error. I
have tried the name without the #, and several other ways with no success.

The syntax is

PARAMETERS [Forms]![frm_Settle_Date]![txt_Settle_Date] DateTime;

The DateTime argument applies to parameters and variables; the # is needed
only when you're passing a literal text string date value.

The reason your query isn't using the parameter is... your query doesn't use
the parameter. You define a parameter in the PARAMETERS clause, but if you
want it used in the query, you have to include it - presumably in a WHERE
clause, I don't know which field you want it to search. Guessing: try

PARAMETERS [Forms]![frm_Settle_Date]![txt_Settle_Date] DateTime;
TRANSFORM Sum(tbl_Dep_Summary.Dep_Amt) AS Amount
SELECT tbl_Dep_Summary.Dep_Slip_No AS [Dep Slip No],
tbl_Dep_Summary.Bank_Acct_No AS [Acct No], tbl_Dep_Summary.Dep_Bank_Name AS
[Bank Name], tbl_Dep_Summary.Dep_No_Bags AS [No Bags],
tbl_Dep_Summary.Settle_Date AS [Settle Date], Sum(tbl_Dep_Summary.Dep_Amt)
AS [Dep Total]
FROM tbl_Dep_Summary
WHERE [Settle_Date] = [Forms]![frm_Settle_Date]![txt_Settle_Date]
GROUP BY tbl_Dep_Summary.Dep_Slip_No, tbl_Dep_Summary.Bank_Acct_No,
tbl_Dep_Summary.Dep_Bank_Name, tbl_Dep_Summary.Dep_No_Bags,
tbl_Dep_Summary.Settle_Date
PIVOT tbl_Dep_Summary.Dep_Type;

John W. Vinson [MVP]

 

[Tom Brown]

John,

Just replaced my code with yours, and it worked! Thanks for the correction
and
explanation.

Tom

I have created a crosstab query for a form which I am putting into a
report
as a subform. I have created a popup form which asks the user to enter
the
Settle date which he wants the data in the report to contain. I am having
a
problem getting the crosstab query to accept the parameters for the
textbox
in the popup form where the date is entered. I keep getting the "Invalid
bracketing of name '[#Forms!frm_Settle_Date!txt_Settle_Date#]' " error. I
have tried the name without the #, and several other ways with no success.

The syntax is

PARAMETERS [Forms]![frm_Settle_Date]![txt_Settle_Date] DateTime;

The DateTime argument applies to parameters and variables; the # is needed
only when you're passing a literal text string date value.

The reason your query isn't using the parameter is... your query doesn't
use
the parameter. You define a parameter in the PARAMETERS clause, but if you
want it used in the query, you have to include it - presumably in a WHERE
clause, I don't know which field you want it to search. Guessing: try

PARAMETERS [Forms]![frm_Settle_Date]![txt_Settle_Date] DateTime;
TRANSFORM Sum(tbl_Dep_Summary.Dep_Amt) AS Amount
SELECT tbl_Dep_Summary.Dep_Slip_No AS [Dep Slip No],
tbl_Dep_Summary.Bank_Acct_No AS [Acct No], tbl_Dep_Summary.Dep_Bank_Name
AS
[Bank Name], tbl_Dep_Summary.Dep_No_Bags AS [No Bags],
tbl_Dep_Summary.Settle_Date AS [Settle Date], Sum(tbl_Dep_Summary.Dep_Amt)
AS [Dep Total]
FROM tbl_Dep_Summary
WHERE [Settle_Date] = [Forms]![frm_Settle_Date]![txt_Settle_Date]
GROUP BY tbl_Dep_Summary.Dep_Slip_No, tbl_Dep_Summary.Bank_Acct_No,
tbl_Dep_Summary.Dep_Bank_Name, tbl_Dep_Summary.Dep_No_Bags,
tbl_Dep_Summary.Settle_Date
PIVOT tbl_Dep_Summary.Dep_Type;

John W. Vinson [MVP]