Crosstab query that uses Form field as criteria (1 Viewer)

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