Form passing values to 2 queries

[Guest]

I have 2 queries looking at a sql dbase to gather commission data. I have
the classic situation of always having Salesperson1, but occasionally having
salesperson2 in a split. The first query gathers all invocing records based
on the date. The second query is basically the same, except it currently has
the "<>isnull" criteria set to focus on only valid salesperson2 records.

Now I want to give our controller a simple form collecting the 3 values ;
salesID, beginning invoice date and ending invoice date. I want the enduser
to be able to either enter a single sales code along with the dates, or to
leave the salesid field blank (or enter a wildcard value) to have the queries
cast their "widest net"...in other words, act as they are setup by default
with qry1 getting all Sales1 adn qry2 getting all records where
salesid<>isnull.

For qry1 i've tried this criteria syntax:
IIf(IsNull([Forms]![formListParams]![SalesID]),Not
([dbo_OP_SLSAHEAD].[Salesperson_Id_1])="isnull",([Forms]![formListParams]![SalesID]))...so
when the form field is null, look at all non-null sales1 records in the
table, else use the form.

For qry2, I've tried similar syntax to test for a null form!salesID field
and if true, then get any non-null sales2id record from the table, else use
the form. I am either using an incorrect implementation of logic or syntax.
Can someone propose a good solution. Seems simple, but I've been staring too
long! Thanks, Dave

 

[JohnFol]

Something like 3 criteria are needed, ie

Select from table where
(salesID = Forms!FormName!SalesID) or isnull(Forms!FormName!SalesID)
and
(InvoiceDate >= Forms!FormName!StartInvoiceDate) or
isnull(Forms!FormName!StartInvoiceDate)
and
(InvoiceDate <= Forms!FormName!EndInvoiceDate) or
isnull(Forms!FormName!endInvoiceDate)

 

[Guest]

John, I tried entering both criteria: salesID = Forms!FormName!SalesID) or
isnull(Forms!FormName!SalesID
in the criteria fields on the queries, but the report, just hourglasses.
CPU churns at 100% until I end task. I'm I using the wrong syntax or am I
applying the criteria inappropriately?
I tried entering a default value of "*" in the SalesID field on teh form
thinking that the form would either pass that or an actual salesID, but if I
leave the SalesID field at "*", I get 0 records returned and I expect a few
thousand.
Thanks,
Dave Miller

Something like 3 criteria are needed, ie

Select from table where
(salesID = Forms!FormName!SalesID) or isnull(Forms!FormName!SalesID)
and
(InvoiceDate >= Forms!FormName!StartInvoiceDate) or
isnull(Forms!FormName!StartInvoiceDate)
and
(InvoiceDate <= Forms!FormName!EndInvoiceDate) or
isnull(Forms!FormName!endInvoiceDate)

I have 2 queries looking at a sql dbase to gather commission data. I have
the classic situation of always having Salesperson1, but occasionally
having
salesperson2 in a split. The first query gathers all invocing records
based
on the date. The second query is basically the same, except it currently
has
the "<>isnull" criteria set to focus on only valid salesperson2 records.

Now I want to give our controller a simple form collecting the 3 values ;
salesID, beginning invoice date and ending invoice date. I want the
enduser
to be able to either enter a single sales code along with the dates, or to
leave the salesid field blank (or enter a wildcard value) to have the
queries
cast their "widest net"...in other words, act as they are setup by default
with qry1 getting all Sales1 adn qry2 getting all records where
salesid<>isnull.

For qry1 i've tried this criteria syntax:
IIf(IsNull([Forms]![formListParams]![SalesID]),Not
([dbo_OP_SLSAHEAD].[Salesperson_Id_1])="isnull",([Forms]![formListParams]![SalesID]))...so
when the form field is null, look at all non-null sales1 records in the
table, else use the form.

For qry2, I've tried similar syntax to test for a null form!salesID field
and if true, then get any non-null sales2id record from the table, else
use
the form. I am either using an incorrect implementation of logic or
syntax.
Can someone propose a good solution. Seems simple, but I've been staring
too
long! Thanks, Dave