Can't Get "ALL" to work in a query - Please Help

[tdoggy777]

Hello -

I have created a simple database with a query that is getting its
parameters passed to it from a form I made. The issue I am having is
twofold....

First, I can't get the "ALL" choice in my dropdown combo box do do
anything. It works, (meaning you can select it as a choice), but I
run a report from my Query and the report shows nothing. Here is the
SQL for my combo box:
SELECT BillingDates.[Billing Date] FROM BillingDates UNION Select
"ALL" From BillingDates
ORDER BY BillingDates.[Billing Date];

Note that there are three total drop down boxes in my form. One for
Billing Date, One for Cardholder, and one for Creditor. They all have
a union select all statement in them.

Here is the SQL from the query I am running that is producing nothing
when I go to run my report which is fed by this query:

SELECT Cardholders.CardHolder, Creditors.Creditor,
Cardholders.CardNumber, BillingDates.[Billing Date],
Transactions.TranDate, Transactions.ChargedTo, Jobs.Job, Codes.Code,
Transactions.Amount
FROM Jobs INNER JOIN (Creditors INNER JOIN (Codes INNER JOIN
(Cardholders INNER JOIN (Transactions INNER JOIN BillingDates ON
Transactions.BillingDate = BillingDates.ID) ON Cardholders.ID =
Transactions.CardHolder) ON Codes.ID = Transactions.Code) ON
Creditors.ID = Transactions.Creditor) ON Jobs.Job = Transactions.Job
WHERE (((Cardholders.CardHolder)=[forms]![ReportbyCard]![CARDNAME])
AND ((Creditors.Creditor)=[forms]![ReportbyCard]![CREDITOR]) AND
((BillingDates.[Billing Date])=[forms]![ReportbyCard]![BILLDATE]));


Any help to fix this would be GREATLY appreciated!

 

[KARL DEWEY]

Try changing it like this ---
WHERE (((Cardholders.CardHolder)=[forms]![ReportbyCard]![CARDNAME])
AND ((Creditors.Creditor)=[forms]![ReportbyCard]![CREDITOR]) AND
((BillingDates.[Billing Date]) Like
IIF([forms]![ReportbyCard]![BILLDATE]="ALL","*",
[forms]![ReportbyCard]![BILLDATE]));

 

[Douglas J. Steele]

Presumably Billing Date is a date field. You can't UNION a text field to a
date field.

As to getting the query to work if All is selected in either of the other
two combo boxes, change your Where clause to

WHERE ((Cardholders.CardHolder=[forms]![ReportbyCard]![CARDNAME])
OR ([forms]![ReportbyCard]![CARDNAME] = "All"))
AND ((Creditors.Creditor=[forms]![ReportbyCard]![CREDITOR])
OR ([forms]![ReportbyCard]![CREDITOR] = "All")
AND (BillingDates.[Billing Date]=[forms]![ReportbyCard]![BILLDATE]);

 

[Justin]

The query is set up to return the actual value of the combo boxes. Unless
you have a record with a Creditor, Cardholder or Date with a actual value of
"All" the query will be empty. I would change the Where statment to:

WHERE (((Cardholders.CardHolder) Like [forms]![ReportbyCard]![CARDNAME])

AND ((Creditors.Creditor) Like [forms]![ReportbyCard]![CREDITOR]) AND
((BillingDates.[Billing Date]) >= #" & [forms]![ReportbyCard]![BILLDATE] & "#));

I would also change the record source for the combo boxes. I would remove
the "ALL" from the selection list, make the default value "*" for the Credtor
and Cardholder. Unless you are looking for a specific date I would change
the date as well. You can mek it greater than the entered date as shown
above. You can include a second date box and search for a date range.

Hello -

I have created a simple database with a query that is getting its
parameters passed to it from a form I made. The issue I am having is
twofold....

First, I can't get the "ALL" choice in my dropdown combo box do do
anything. It works, (meaning you can select it as a choice), but I
run a report from my Query and the report shows nothing. Here is the
SQL for my combo box:
SELECT BillingDates.[Billing Date] FROM BillingDates UNION Select
"ALL" From BillingDates
ORDER BY BillingDates.[Billing Date];

Note that there are three total drop down boxes in my form. One for
Billing Date, One for Cardholder, and one for Creditor. They all have
a union select all statement in them.

Here is the SQL from the query I am running that is producing nothing
when I go to run my report which is fed by this query:

SELECT Cardholders.CardHolder, Creditors.Creditor,
Cardholders.CardNumber, BillingDates.[Billing Date],
Transactions.TranDate, Transactions.ChargedTo, Jobs.Job, Codes.Code,
Transactions.Amount
FROM Jobs INNER JOIN (Creditors INNER JOIN (Codes INNER JOIN
(Cardholders INNER JOIN (Transactions INNER JOIN BillingDates ON
Transactions.BillingDate = BillingDates.ID) ON Cardholders.ID =
Transactions.CardHolder) ON Codes.ID = Transactions.Code) ON
Creditors.ID = Transactions.Creditor) ON Jobs.Job = Transactions.Job
WHERE (((Cardholders.CardHolder)=[forms]![ReportbyCard]![CARDNAME])
AND ((Creditors.Creditor)=[forms]![ReportbyCard]![CREDITOR]) AND
((BillingDates.[Billing Date])=[forms]![ReportbyCard]![BILLDATE]));


Any help to fix this would be GREATLY appreciated!