Cross Tab Query - Date Range fails

[Peter Hibbs]

I have tables
tblBusinessSources which has two fields :-
SourceItem (Text) and SortOrder (Number)
and it has 12 records.

tblMortgage has numerous fields but the relevant ones are :-
ID (AutoNumber)
Status (Text)
BusSource (Text)
MortgageDate (Date)

The table tblBusinessSources feeds the field BusSource on the Mortgage
form which is a Combo box control.

I am trying to create a Cross Tab query (which will be bound to a List
Box on form called frmFSAReports) which shows the total number of
records for each Status group and BusSource. So the Status field will
show each column and the SourceItem from tblBusinessSources will show
the rows. I want to show the totals for records where the MortgageDate
falls within a set date range which is determined by two fields on the
form frmFSAReports. The SQL is below.

TRANSFORM Count(tblMortgage.ID) AS CountOfID
SELECT tblBusinessSources.SourceItem
FROM tblBusinessSources LEFT JOIN tblMortgage ON
tblBusinessSources.SourceItem = tblMortgage.BusSource
WHERE (((tblMortgage.MortgageDate) Between
[Forms]![frmFSAReports]![txtStartDate] And
[Forms]![frmFSAReports]![txtEndDate]))
GROUP BY tblBusinessSources.SourceItem, tblBusinessSources.SortOrder,
tblMortgage.MortgageDate
ORDER BY tblBusinessSources.SortOrder
PIVOT tblMortgage.Status;

The problem is that Access throws up the following error message when
I run the query from the form -

The Microsoft Jet database engine does not recognize
'[Forms]![frmFSAReports]![txtStartDate]' as a valid field name or
expression.

If I hard-wire the dates in like this :-

TRANSFORM Count(tblMortgage.ID) AS CountOfID
SELECT tblBusinessSources.SourceItem
FROM tblBusinessSources LEFT JOIN tblMortgage ON
tblBusinessSources.SourceItem = tblMortgage.BusSource
WHERE (((tblMortgage.MortgageDate) Between #1/1/1900# And #1/1/2020#))
GROUP BY tblBusinessSources.SourceItem, tblBusinessSources.SortOrder,
tblMortgage.MortgageDate
ORDER BY tblBusinessSources.SortOrder
PIVOT tblMortgage.Status;

it works OK.

Why does Jet not like the references to the two text fields. They are
definitely valid, some other queries on the same form use them for
normal queries, it is just the Cross Tab query that throws a hissy
fit. Any ideas on how to fix this problem.

Peter Hibbs.

 

[Allen Browne]

You need to explicitly declare the parameters in a crosstab:
http://allenbrowne.com/ser-67.html#Param

 

[scubadiver]

Have you explicitly stated the parameters. Right click in the top half of the
query window and select parameters. In the left column type the criteria.

[Forms]![frmFSAReports]![txtStartDate]
[Forms]![frmFSAReports]![txtEndDate]

and choose date/time in the format.

what happens?

 

[Douglas J. Steele]

With crosstab queries, I believe you must declare the parameters. Try:

PARAMETERS [Forms]![frmFSAReports]![txtStartDate] DateTime,
[Forms]![frmFSAReports]![txtEndDate] DateTime;
TRANSFORM Count(tblMortgage.ID) AS CountOfID
SELECT tblBusinessSources.SourceItem
FROM tblBusinessSources LEFT JOIN tblMortgage ON
tblBusinessSources.SourceItem = tblMortgage.BusSource
WHERE (((tblMortgage.MortgageDate) Between
[Forms]![frmFSAReports]![txtStartDate] And
[Forms]![frmFSAReports]![txtEndDate]))
GROUP BY tblBusinessSources.SourceItem, tblBusinessSources.SortOrder,
tblMortgage.MortgageDate
ORDER BY tblBusinessSources.SortOrder
PIVOT tblMortgage.Status;


Of course, frmFSAReports must be open when you're running the query: Access
will not open the form for you.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have tables
tblBusinessSources which has two fields :-
SourceItem (Text) and SortOrder (Number)
and it has 12 records.

tblMortgage has numerous fields but the relevant ones are :-
ID (AutoNumber)
Status (Text)
BusSource (Text)
MortgageDate (Date)

The table tblBusinessSources feeds the field BusSource on the Mortgage
form which is a Combo box control.

I am trying to create a Cross Tab query (which will be bound to a List
Box on form called frmFSAReports) which shows the total number of
records for each Status group and BusSource. So the Status field will
show each column and the SourceItem from tblBusinessSources will show
the rows. I want to show the totals for records where the MortgageDate
falls within a set date range which is determined by two fields on the
form frmFSAReports. The SQL is below.

TRANSFORM Count(tblMortgage.ID) AS CountOfID
SELECT tblBusinessSources.SourceItem
FROM tblBusinessSources LEFT JOIN tblMortgage ON
tblBusinessSources.SourceItem = tblMortgage.BusSource
WHERE (((tblMortgage.MortgageDate) Between
[Forms]![frmFSAReports]![txtStartDate] And
[Forms]![frmFSAReports]![txtEndDate]))
GROUP BY tblBusinessSources.SourceItem, tblBusinessSources.SortOrder,
tblMortgage.MortgageDate
ORDER BY tblBusinessSources.SortOrder
PIVOT tblMortgage.Status;

The problem is that Access throws up the following error message when
I run the query from the form -

The Microsoft Jet database engine does not recognize
'[Forms]![frmFSAReports]![txtStartDate]' as a valid field name or
expression.

If I hard-wire the dates in like this :-

TRANSFORM Count(tblMortgage.ID) AS CountOfID
SELECT tblBusinessSources.SourceItem
FROM tblBusinessSources LEFT JOIN tblMortgage ON
tblBusinessSources.SourceItem = tblMortgage.BusSource
WHERE (((tblMortgage.MortgageDate) Between #1/1/1900# And #1/1/2020#))
GROUP BY tblBusinessSources.SourceItem, tblBusinessSources.SortOrder,
tblMortgage.MortgageDate
ORDER BY tblBusinessSources.SortOrder
PIVOT tblMortgage.Status;

it works OK.

Why does Jet not like the references to the two text fields. They are
definitely valid, some other queries on the same form use them for
normal queries, it is just the Cross Tab query that throws a hissy
fit. Any ideas on how to fix this problem.

Peter Hibbs.

 

[Peter Hibbs]

Thanks guys, I learned something new today.

Peter Hibbs.