Form field to capture a single or multiple selection.

[Swifey]

I currently run a query by using a form to capture the parameters required for the report namely the store and sales period.
It worked fine up until now whereby the company has now decided to merge the sales of two stores together but they still want to keep their unique store numbers.

I have created an additional sales input box and amended my query as follows

Form!Store1 Or Form!Store2

The above works fine when I run the merged sales but if I report just on one store and don’t fill in the additional field the query fails. To get it to work I have to show the store twice.

Is this way practical?

Kind Regards
Lynda

 

[Corticus]

Hi Swifey,

You could try nesting SQL:

SELECT a.store, a.salesperiod
FROM (SELECT b.store, b.store
FROM tblsales as b
WHERE (((b.store)=[enter store])) OR ((([enter store]) Is Null))) AS a
WHERE (((a.salesperiod)=[enter salesperiod])) OR ((([enter salesperiod]) Is Null));

Paste the above SQL in to SQL view of a query. Where I have 'enter store' and such, you would want to refer to the objects on you form that hold this criteria.

HTH

 

[Swifey]

Thank you very much for your help.

Regards
Swifey

 

[Swifey]

Hi I'm sorry to bother you again but I have never used SQL

The following code is from Access query:

SELECT dbo_tBcatlocrollup.catcode, dbo_tBcatlocrollup.catcodesuff, dbo_tSAPMaterials.material AS 18, dbo_tBcatlocrollup!catcode & dbo_tBcatlocrollup!catcodesuff AS Material, Sum(dbo_tBcatlocrollup.quantity) AS SumOfquantity INTO [stage 1 All leg sales Period1]
FROM dbo_tBcatlocrollup LEFT JOIN dbo_tSAPMaterials ON (dbo_tBcatlocrollup.catcode = dbo_tSAPMaterials.catcode) AND (dbo_tBcatlocrollup.catcodesuff = dbo_tSAPMaterials.catcodesuff)
WHERE (((dbo_tBcatlocrollup.salesperiod) Between [Forms]![frontpage]![1start] And [Forms]![frontpage]![1end]) AND ((dbo_tBcatlocrollup.location)=[Forms]![frontpage]![branch]))
GROUP BY dbo_tBcatlocrollup.catcode, dbo_tBcatlocrollup.catcodesuff, dbo_tSAPMaterials.material, dbo_tBcatlocrollup!catcode & dbo_tBcatlocrollup!catcodesuff;

Regards Swifey

 

[Corticus]

Try:

SELECT a.catcode, a.catcodesuff, a.material AS 18, a!catcode & a!catcodesuff AS Material, Sum(a.quantity) AS SumOfquantity INTO [stage 1 All leg sales Period1]
FROM (Select b.catcode, b.catcodesuff, b.material AS 18, b!catcode & b!catcodesuff AS Material, Sum(b.quantity) AS SumOfquantity INTO [stage 1 All leg sales Period1]
FROM dbo_tBcatlocrollup LEFT JOIN dbo_tSAPMaterials ON (dbo_tBcatlocrollup.catcode = dbo_tSAPMaterials.catcode) AND (dbo_tBcatlocrollup.catcodesuff = dbo_tSAPMaterials.catcodesuff) as b
WHERE (b.salesperiod) Between (([Forms]![frontpage]![1start]) And ([Forms]![frontpage]![1end])) AND ((b.location)=([Forms]![frontpage]![branch])) or (([Forms]![frontpage]![branch]) Is Null)) as a
WHERE (a.salesperiod) Between (([Forms]![frontpage]![1start]) And ([Forms]![frontpage]![1end])) AND ((a.location)=([Forms]![frontpage]![branch])) or (([Forms]![frontpage]![branch]) Is Null))
GROUP BY a.catcode, a.catcodesuff, a.material, a!catcode & a!catcodesuff;

Now, because I can't test this, there's a slight chance there might be a syntax error. I just wanted to post this because in theory, it should handle what you need.

An easier method would be to make a new query that looks at the query you gave me (minus any criteria),

Now set your branch criteria, but underneath the criteria, where it says 'or', add Forms!Store1 Is Null or whatever you call the object housing this criteria.

Now make another query, have it refer to the one you just made, and the criteria for the branch, and put in the 'or' field
Forms!Store2 Is Null

For some reason, Access doesn't do well with multiple 'Is Null' criteria, so you have to query a query. The first query looks at the forms first object, and tests the dataset against the criteria for this object, and if its null, returns all values. The second query look at the forms second object (by this, I mean the textbox, or whatever, that would house the second branch), and tests the dataset against this value, and if its null, returns everything.

If you need more help, feel free send your db to me at
corticus@theofficeexperts.com

then I can post a solution I know will work.

HTH,

 

[Swifey]

Thank you I do appreciate your help.

Regards
Lynda