Can you use the Like statement in a crosstab query criteria ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When I use the Like statement in a crosstab query criteria, I am getting a
jet database engine error message but cannot find anything in the help files
that say it is disallowed.

I want to be able to use the Like statement and also combine it with a user
entered parameter.

Thanks,
Pat
 
You may be having the same problems that I had until I searched the newsgroup
and found my answer in many post.
Crosstab displays an error when you add criteria unless you define the
parameters.

Open the query in design view and click on menu VIEW - View SQL. The first
line should now start with TRANSFORM. You need to add like this before that
line --
PARAMETERS [enter beginning number] Text ( 255 ), [enter ending
number] Text ( 255 );

This example has two parameters but you could have one or more separating by
a comma and ending with a semicolon.
 
Karl -- Thanks very much, this solved my problem.

KARL DEWEY said:
You may be having the same problems that I had until I searched the newsgroup
and found my answer in many post.
Crosstab displays an error when you add criteria unless you define the
parameters.

Open the query in design view and click on menu VIEW - View SQL. The first
line should now start with TRANSFORM. You need to add like this before that
line --
PARAMETERS [enter beginning number] Text ( 255 ), [enter ending
number] Text ( 255 );

This example has two parameters but you could have one or more separating by
a comma and ending with a semicolon.


Topmark said:
When I use the Like statement in a crosstab query criteria, I am getting a
jet database engine error message but cannot find anything in the help files
that say it is disallowed.

I want to be able to use the Like statement and also combine it with a user
entered parameter.

Thanks,
Pat
Click to expand...
Click to expand...
 
How do I pass a parameter in a CrossTab Query?

I have the following SQL that doesn't work.


PARAMETERS [ENTER STORE NUMBER] Text ( 255 ), Like [ENTER STORE NUMBER] Is
Null Text ( 255 );

TRANSFORM First([PriceTable]![baseprice]/[PriceTable]![basepricemult]) AS
[BASE-RTL]
SELECT ItemMaster.itemnbr AS ITMNbr, ItemMaster.description AS [DESC]
FROM (PriceTable LEFT JOIN StoreMaster ON PriceTable.storeid =
StoreMaster.storeid) LEFT JOIN ItemMaster ON PriceTable.itemid =
ItemMaster.itemid
WHERE (((StoreMaster.storename)=[ENTER STORE NUMBER])) OR
(((StoreMaster.storename) Like [ENTER STORE NUMBER] Is Null))
GROUP BY ItemMaster.itemnbr, ItemMaster.description
ORDER BY StoreMaster.storename
PIVOT StoreMaster.storename;

--
Thanks, Kevin


KARL DEWEY said:
You may be having the same problems that I had until I searched the newsgroup
and found my answer in many post.
Crosstab displays an error when you add criteria unless you define the
parameters.

Open the query in design view and click on menu VIEW - View SQL. The first
line should now start with TRANSFORM. You need to add like this before that
line --
PARAMETERS [enter beginning number] Text ( 255 ), [enter ending
number] Text ( 255 );

This example has two parameters but you could have one or more separating by
a comma and ending with a semicolon.


Topmark said:
When I use the Like statement in a crosstab query criteria, I am getting a
jet database engine error message but cannot find anything in the help files
that say it is disallowed.

I want to be able to use the Like statement and also combine it with a user
entered parameter.

Thanks,
Pat
Click to expand...
Click to expand...
 
hi

i'm trying to use a date range as parameters for a crosstab query but it
doesn't select the info between the date. still selects all the records. here
is my SQL

PARAMETERS [From what date?] DateTime, [Till what date?] DateTime;
TRANSFORM Last(form1.MDCCheck) AS LastOfMDCCheck
SELECT form1.StoreName, Count(form1.MDCCheck) AS [Total Of MDCCheck]
FROM form1
GROUP BY form1.StoreName
ORDER BY form1.StoreName
PIVOT form1.DateProcessed;
 
Just declaring the Parameters does not use them. You still need to use them
in a where clause or a having clause.


PARAMETERS [From what date?] DateTime, [Till what date?] DateTime;
TRANSFORM Last(form1.MDCCheck) AS LastOfMDCCheck
SELECT form1.StoreName, Count(form1.MDCCheck) AS [Total Of MDCCheck]
FROM form1

WHERE [DateProcessed] Between [From what date?] AND [Till what date?]

GROUP BY form1.StoreName
ORDER BY form1.StoreName
PIVOT form1.DateProcessed;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Back
Top