Crosstab query that uses Form field as criteria

[cricketbird]

If I run this query as a "select" query, it works fine, but if change it to crosstab, I get the error "The...engine does not recognize [Forms]![Bookings]![StartDate] as a valid field name or expression". Apparently this doesn't happen as a parameter query, but I don't want someone to have to manually enter a date. Is there a way around this?

TRANSFORM Sum([Bookings].Cost) AS SumOfCost
SELECT [Bookings].BookingDate
FROM [Bookings]
WHERE ((([Bookings].BookingDate)>[Forms]![Compare_Diets]![StartDate]))
GROUP BY [Bookings].BookingDate
PIVOT [Bookings].FacilityID;

 

[theDBguy]

Try adding a PARAMETERS declaration on top of your SQL statement.

Sent from phone...

 

[isladogs]

As discussed in another current thread, you should avoid hard coding form controls into queries where possible

Personally, I would use the PARAMETERS declaration approach already suggested by @theDBguy

However, another more convoluted method would be to assign the form value to a public variable in the form textbox after update event
Create a function in a standard module to retrieve that value then use the function in the query

 

[Gasman]

You can use TempVars()?

PARAMETERS [tempvars]![dt] DateTime;
TRANSFORM Format(Sum(tblDaily.CarbsCalc),"Fixed") AS SumOfCarbsCalc
SELECT WeekdayName(Weekday([DailyDate],2)) AS [Day], tblDaily.DailyDate, Format(Sum([tblDaily].[CarbsCalc]),"Fixed") AS Total
FROM tblDaily INNER JOIN tblLookUp ON tblDaily.MealTypeFK = tblLookUp.LookUpID
WHERE (((tblDaily.DailyDate)>=[tempvars]![dt]))
GROUP BY WeekdayName(Weekday([DailyDate],2)), tblDaily.DailyDate
ORDER BY tblDaily.DailyDate
PIVOT tblLookUp.LookUpValue;

tempvars("dt").value = #08/10/2025#
? tempvars("dt").value
10/08/2025

 

[Pat Hartman]

As discussed in another current thread, you should avoid hard coding form controls into queries where possible

No one has actually identified a specific problem associated with using form field references in queries so "avoid" is a muddy suggestion based on no facts.

The issue is almost certainly as already pointed out. ALL parameters must be defined when used directly in a crosstab or even in a query referenced by the crosstab.

 

[Gasman]

No one has actually identified a specific problem associated with using form field references in queries so "avoid" is a muddy suggestion based on no facts.

The issue is almost certainly as already pointed out. ALL parameters must be defined when used directly in a crosstab or even in a query referenced by the crosstab.

I look on it the same as hard coding values.
You yourself advocate not doing that, but to read them from somewhere else.
Also does not work if query can be called from more than one form?

 

[CJ_London]

Also does not work if query can be called from more than one form?

That’s when I would use a tempvar

All very well saying ‘don’t use references to form controls in a query’ but without clarification as to what to use as an alternative is not helpful. I don’t see using tempvars as a solution since that still needs to be populated so there is still a ‘link’ back to the form, although I accept the form can be closed once the tempvar is populated and before the query is run

 

[Pat Hartman]

I look on it the same as hard coding values.
You yourself advocate not doing that, but to read them from somewhere else.
Also does not work if query can be called from more than one form?

But it is not hard-coding a value. It is specifying where to obtain the value. Your other alternatives are:
1. TempVars. These work fine but are essentially no different in concept except that before you run a query, you need code to place the actual value into the TempVar
2. Use embedded SQL and never use Querydefs. I have lots of issues with this but many people think it is the only solution. In this solution your code refers to the form control to pick up the "hard-coded" value which is then embedded into the SQL string. So instead of the querydef being:

Where somefield = Forms!myform!myfield

Your code says:

"Where somefield = " & Forms!myform!myfield

But I guess that isn't hard-coding a value

Somehow, some value needs to be provided to the query. The simplest solution is directly referencing the form field that holds the value. If you're inclined to change control names and form names, that's on your head. But, you still need to find the places where your code obtains the value that the query needs and somehow feeds it to the query.

 

[Ken Sheridan]

ALL parameters must be defined when used directly in a crosstab or even in a query referenced by the crosstab.

One caveat, which applies to any query, not just crosstabs, if the following syntax is being used to make a parameter optional:

WHERE (somefield = Forms!myform!myfield OR Forms!myform!myfield IS NULL)

the parameter should be declared, if anything, as a Variant data type. Otherwise it can never be NULL. The exception is the DateTime type, in which case the parameter should be declared as DateTime, which allows NULL. Declaring a DateTime parameter is advisable as it avoids a parameter being inadvertently misinterpreted as an arithmetical expression.

 

[tvanstiphout]

if myField occurs on several forms, this syntax can be used:
Form.myField

 

[sonic8]

if myField occurs on several forms, this syntax can be used:
Form.myField

I believe this will only work if the query is the Record source of the form in question.
In this case you can also omit "Form.".

PS: Of course, if there are ambiguities between form controls and table columns in the query, you should not omit "Form."

 

[DHookom]

From my experience, parameter types are NOT always required when referencing a form control in a crosstab query. Setting the column headings should remove the requirement. This of course will only work when the column headings are known, static values like months, days, numbers,etc. for instance:
Column Headings: "Q1”, "Q2", "Q3", "Q4"

 

[Pat Hartman]

From my experience, parameter types are NOT always required when referencing a form control in a crosstab query.

That's pretty obscure. When there is one unusual exception (all column headings are known ahead of time), I would simply just define the parameters for all Crosstabs and leave it at that.

PS, Just a reminder, if you specify column headings, then the crosstab will IGNORE any row that doesn't have a specified column heading. Therefore, Except for date parts, I would be very careful about defining column headings. For example, you have 5 departments and you always want a column for every department even when there is no data selected for a particular department so you decide to add column headings as a solution. That works fine until someone decides to add a sixth department. It will never show in the crosstab unless you change the SQL to add the sixth department to the column headings.

 

[DHookom]

That's pretty obscure. When there is one unusual exception (all column headings are known ahead of time), I would simply just define the parameters for all Crosstabs and leave it at that.

PS, Just a reminder, if you specify column headings, then the crosstab will IGNORE any row that doesn't have a specified column heading. Therefore, Except for date parts, I would be very careful about defining column headings. For example, you have 5 departments and you always want a column for every department even when there is no data selected for a particular department so you decide to add column headings as a solution. That works fine until someone decides to add a sixth department. It will never show in the crosstab unless you change the SQL to add the sixth department to the column headings.

You can always add some simple VBA code to loop through the unique departments to build your crosstab SQL statement. I do, however, reserve the column headings mostly to date related values since most of my crosstabs are record sources for reports.

 

[sonic8]

if you specify column headings, then the crosstab will IGNORE any row that doesn't have a specified column heading.

Well, that's the point of defining column headers, isn't it?
But, as a reminder, particularly for newbies, that might be sensible.

 

[Pat Hartman]

Well, that's the point of defining column headers, isn't it?

Not really. Criteria in the query is what one would expect to control what the query shows. If I want to see billing hours by person/task for each month and I want to see all people as columns, I might not see Suzie in August if she has been on vacation. In order for a column to show for Suzie, I would need to adjust the column headings. Granted, a left join might solve this problem but that might not work in all cases.

 

[DHookom]

Well, that's the point of defining column headers, isn't it?
But, as a reminder, particularly for newbies, that might be sensible.

I will use Column Headings to create daily, weekly, or monthly totals for the past ten units. For example in the Northwind, to get the last ten days of sales by salesperson, use this SQL which looks at the txtEndingDate.

TRANSFORM Sum([Quantity]*[UnitPrice]) AS Expr2
SELECT Employees.LastName
FROM Employees INNER JOIN (Orders INNER JOIN OrderDetails
  ON Orders.OrderID = OrderDetails.OrderID) ON Employees.EmployeeID = Orders.EmployeeID
GROUP BY Employees.LastName
PIVOT "D" & DateDiff("d",[OrderDate],[Forms]![frmSelectDate]![txtEndingDate])
In ("D9","D8","D7","D6","D5","D4","D3","D2","D1","D0");

When using in a report, it is easy to convert the column labels to text boxes with DateAdd() values.