RE: CrossTab - parameter with a null response

Tech-Archive recommends: Fix windows errors by optimizing your registry

I would expect the StoreID to contain the values like 1234, H0123,... and the
StoreName to be something like: Best Buy, OfficeMax, Sharper Image,....

I think I just noticed my error...
PARAMETERS [ENTER STORE NUMBERS] 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 Instr(Nz([ENTER STORE NUMBERS], StoreName), StoreName)>0
GROUP BY ItemMaster.itemnbr, ItemMaster.description
ORDER BY StoreMaster.storename
PIVOT StoreMaster.storename;

My expectation would be that you could enter a value like
1234,H0123
to compare these to stores.


--
Duane Hookom
Microsoft Access MVP


"AFSSkier" wrote:

Data stored in [StoreName] field is a text ID
Indexed, Text, A8 (12345, H0123, M1234, etc)

When your suggested SQL is ran, I get a “ENTER STORE NUMBERS” prompt, I
enter 12345 (store ID), 0 records found. (does not prompt again for 2nd
store ID)

Run again, at prompt I leave blank, 0 records found.

The same data in a select query with the following parameter prompt works
fine.
[ENTER STORE NUMBER] Or Like [ENTER STORE NUMBER] Is Null

I hope this answers your questions.

--
Thanks, Kevin


"Duane Hookom" wrote:

Can you take the time to:
1) explain what you mean by "it's still not working". What happens? What
displays? What errors do you get?
2) Describe the data stored in StoreName. We still don't know if these are
numbers or text or short abbreviations or what.
--
Duane Hookom
Microsoft Access MVP


"AFSSkier" wrote:

I cut & pasted your suggested SQL & it's still not working.

Access is changing the SQL to:
WHERE ((([StoreMaster].[storename]=InStr(Nz([ENTER STORE
NUMBERS],[StoreName]),[StoreName]))>0))

& you had:
WHERE StoreMaster.storename=Instr(Nz([ENTER STORE NUMBERS], StoreName),
StoreName)>0
--
Thanks, Kevin


"Duane Hookom" wrote:

Try something like this where the user can enter 1, 2, 3,... store numbers in
the same prompt. The success of this depends on your store number values.
Your expression might need to be modified to accomodate your data values.
PARAMETERS [ENTER STORE NUMBERS] 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=Instr(Nz([ENTER STORE NUMBERS], StoreName),
StoreName)>0
GROUP BY ItemMaster.itemnbr, ItemMaster.description
ORDER BY StoreMaster.storename
PIVOT StoreMaster.storename;

--
Duane Hookom
Microsoft Access MVP


"AFSSkier" wrote:

In a select query, [ENTER STORE NUMBER] Or Like [ENTER STORE NUMBER] Is Null
will work.
The user can click enter, returning all records or
A single StoreNumber, returning one store's recorder or
Enter 1st StoreNumber & 2nd StoreNumber a 2nd prompt, returning records for
2 stores.

What I'm looking for is a parameter in a CrossTab that can do the same so
that 2 store's pricing can be compared side by side for the same item record.
Or all or 1.

--
Thanks, Kevin


"Duane Hookom" wrote:

Are you looking for two different stores? Why would you want two different
prompts? Why are you asking for store number and comparing it to the
StoreName field?
Did you try my suggestion? If you enter nothing in the prompt, it should
display all stores.
--
Duane Hookom
Microsoft Access MVP


"AFSSkier" wrote:

What I'm looking for is a parameter in a CrossTab that can accept an input
from the user, that will return all records if no input is made. Or prompt
again if an imput is made, treating them as separate parameters. The user
would see two input prompt boxes.

[ENTER STORE NUMBER] Or Like [ENTER STORE NUMBER] Is Null

--
Thanks, Kevin


"Duane Hookom" wrote:

PARAMETERS [ENTER STORE NUMBER] 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=Nz([ENTER STORE NUMBER], StoreMaster.StoreName)
GROUP BY ItemMaster.itemnbr, ItemMaster.description
ORDER BY StoreMaster.storename
PIVOT StoreMaster.storename;

--
Duane Hookom
Microsoft Access MVP


"AFSSkier" wrote:

How do I pass a parameter with a null response in a CrossTab Query?

I have the following SQL. I am unable to get the PARAMETERS right.

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
.

Relevant Pages

  • RE: CrossTab - parameter with a null response
    ... FROM (Customers INNER JOIN Orders ON Customers.CustomerID = ... "Duane Hookom" wrote: ... WHERE Instr(Nz([ENTER STORE NUMBERS], StoreName), StoreName)>0 ... Run again, at prompt I leave blank, 0 records found. ...
    (microsoft.public.access.queries)
  • RE: CrossTab - parameter with a null response
    ... the CrossTab SQL below is more what I'm dealing with: ... "Duane Hookom" wrote: ... WHERE Instr(Nz([ENTER STORE NUMBERS], StoreName), StoreName)>0 ... Run again, at prompt I leave blank, 0 records found. ...
    (microsoft.public.access.queries)
  • RE: CrossTab - parameter with a null response
    ... Microsoft Access MVP ... "Duane Hookom" wrote: ... WHERE Instr(Nz([ENTER STORE NUMBERS], StoreName), StoreName)>0 ... Run again, at prompt I leave blank, 0 records found. ...
    (microsoft.public.access.queries)
  • RE: CrossTab - parameter with a null response
    ... View the datasheet of the query and enter ... WHERE Instr(Nz([ENTER STORE NUMBERS], StoreName), StoreName)>0 ... "Duane Hookom" wrote: ... Run again, at prompt I leave blank, 0 records found. ...
    (microsoft.public.access.queries)
  • RE: CrossTab - parameter with a null response
    ... WHERE Instr(Nz([ENTER STORE NUMBERS], StoreName), StoreName)>0 ... "Duane Hookom" wrote: ... StoreName to be something like: ... Run again, at prompt I leave blank, 0 records found. ...
    (microsoft.public.access.queries)