Crosstab Lookup

[Guest]

Thank you for looking at my question,

I have created a crosstab query with a date row. I would like to make a
“between†statement in the criteria but every time I run it it tells me “The
Microsoft Jet database engine does not recognize ‘[Enter Start Date]’ as a
valid field name or expressionâ€. Is there a way to make it take the date
range?

My criteria statement in the [Date] row is:
“Between [Enter Start Date] And [Enter End Date]â€

Please no VB answers, I don’t understand code.

Thanks in advance,

Cage

 

[Guest]

In a crosstab query you must define you paramenters.
Open in design view and click on menu QUERY - Parameters.
Copy the criteria exactly into the parameter and then type in TEXT as data
type. Put the parameters on separate lines.
Save.
Run.

 

[Guest]

So are you saying that I would not be able to have a popup box that I can
input the date each time I open the report, I would have to go into the
criteria of the [Date] each time and change the [Date] manually than run?

In a crosstab query you must define you paramenters.
Open in design view and click on menu QUERY - Parameters.
Copy the criteria exactly into the parameter and then type in TEXT as data
type. Put the parameters on separate lines.
Save.
Run.
--
KARL DEWEY
Build a little - Test a little

Thank you for looking at my question,

I have created a crosstab query with a date row. I would like to make a
“between†statement in the criteria but every time I run it it tells me “The
Microsoft Jet database engine does not recognize ‘[Enter Start Date]’ as a
valid field name or expressionâ€. Is there a way to make it take the date
range?

My criteria statement in the [Date] row is:
“Between [Enter Start Date] And [Enter End Date]â€

Please no VB answers, I don’t understand code.

Thanks in advance,

Cage

 

[Guest]

No that is not what I said. You are not having "popup box" but a prompt to
input the parameter.
Just use the criteria as you said -- Between [Enter Start Date] And [Enter
End Date]

Copy the [Enter Start Date] and paste into the parameter.

Then copy [Enter End Date] and paste into the next parameter line.

Another way is to edit the SQL by inserting a line like ---
PARAMETERS [Enter Start Date] TEXT, [Enter End Date] TEXT;
TRANSFORM .......

--
KARL DEWEY
Build a little - Test a little

So are you saying that I would not be able to have a popup box that I can
input the date each time I open the report, I would have to go into the
criteria of the [Date] each time and change the [Date] manually than run?

In a crosstab query you must define you paramenters.
Open in design view and click on menu QUERY - Parameters.
Copy the criteria exactly into the parameter and then type in TEXT as data
type. Put the parameters on separate lines.
Save.
Run.
--
KARL DEWEY
Build a little - Test a little

Thank you for looking at my question,

I have created a crosstab query with a date row. I would like to make a
“between†statement in the criteria but every time I run it it tells me “The
Microsoft Jet database engine does not recognize ‘[Enter Start Date]’ as a
valid field name or expressionâ€. Is there a way to make it take the date
range?

My criteria statement in the [Date] row is:
“Between [Enter Start Date] And [Enter End Date]â€

Please no VB answers, I don’t understand code.

Thanks in advance,

Cage

 

[Duane Hookom]

Wouldn't the datatype of the parameters be DATE/TIME rather than TEXT?
Also, IMHO parameter prompts are not good user interface. All criteria
should be entered into controls on forms.


--
Duane Hookom
MS Access MVP

No that is not what I said. You are not having "popup box" but a prompt
to
input the parameter.
Just use the criteria as you said -- Between [Enter Start Date] And [Enter
End Date]

Copy the [Enter Start Date] and paste into the parameter.

Then copy [Enter End Date] and paste into the next parameter line.

Another way is to edit the SQL by inserting a line like ---
PARAMETERS [Enter Start Date] TEXT, [Enter End Date] TEXT;
TRANSFORM .......

--
KARL DEWEY
Build a little - Test a little

So are you saying that I would not be able to have a popup box that I can
input the date each time I open the report, I would have to go into the
criteria of the [Date] each time and change the [Date] manually than run?

In a crosstab query you must define you paramenters.
Open in design view and click on menu QUERY - Parameters.
Copy the criteria exactly into the parameter and then type in TEXT as
data
type. Put the parameters on separate lines.
Save.
Run.
--
KARL DEWEY
Build a little - Test a little


:

Thank you for looking at my question,

I have created a crosstab query with a date row. I would like to
make a
"between" statement in the criteria but every time I run it it tells
me "The
Microsoft Jet database engine does not recognize '[Enter Start Date]'
as a
valid field name or expression". Is there a way to make it take the
date
range?

My criteria statement in the [Date] row is:
"Between [Enter Start Date] And [Enter End Date]"

Please no VB answers, I don't understand code.

Thanks in advance,

Cage