Why is this not recognised as a valid fieldname or expression... grrrrrr! lol (1 Viewer)

[tezread]

PARAMETERS [Forms]![frmRptDialogSingle]![begdate] DateTime, [Forms]![frmRptDialogSingle]![enddate] DateTime;
TRANSFORM Count(qry_cardiology1.EpisodeID) AS CountOfEpisodeID
SELECT qry_cardiology1.Days
FROM qry_cardiology1
WHERE (((qry_cardiology1.RequestDatetime) Between [Forms]![frmRptDialogSingle]![begdate] And [Forms]![frmRptDialogSingle]![enddate]))
GROUP BY qry_cardiology1.Days
PIVOT qry_cardiology1.ReferringHospital;

this was working fine this query but now it is saying the fieldname Requestdatetime is not recognised by the jet engine!

why?

 

[Yarp]

Try looking up the error code you receive on the error message via a search engine. I usually find a clue that helps me solve my coding mistakes this way. Without knowing your table and form design, it is pretty hard for anyone to diagnose why it isn't working.

 

[vbaInet]

Try wrapping all your field names in square brackets:

qry_cardiology1.[RequestDatetime]

 

[SOS]

Crosstabs are picky. Create a query (select only) that uses the parameters to select the data from and then use that query as the basis for your crosstab. You will want to specify the parameters in both even though the crosstab really doesn't have criteria directly on it.

 

[vbaInet]

You can still use parameters in crosstab queries but you have to define them in the Parameters list, but like SOS said they can be quite tricky.

Have a look at this (under the Handle Paramters section):

http://allenbrowne.com/ser-67.html

Or this (under the Using Parameters section):

http://www.fmsinc.com/tpapers/queries/index.html