ALL query not working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tdoggy777 - 26 Mar 2008 16:06 GMT

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!

Reply to this Message

mscertified - 26 Mar 2008 18:11 GMT

> SELECT BillingDates.[Billing Date] FROM BillingDates UNION Select
> "ALL" From BillingDates
> ORDER BY BillingDates.[Billing Date];

This SQL is formed wrong, what are you trying to retrieve?
The second part of the UNION will send back one row and one column filled
with 'ALL'.
You need some training in SQL if you think this will do anything useful.
Look in Acccess SQL Help.

> Hello -
>
[quoted text clipped - 9 lines]
> "ALL" From BillingDates
> ORDER BY BillingDates.[Billing Date];

Reply to this Message

John Spencer - 26 Mar 2008 18:40 GMT

The problem is that you don't have card holders named ALL or Billing dates
with the value ALL or creditors named ALL.

You can build the queries where clause on the fly, or pass a filter string
that you build on the fly to the the report or try to modify your 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]
OR [forms]![ReportbyCard]![CARDNAME] = "All")
AND (Creditors.Creditor= [forms]![ReportbyCard]![CREDITOR]
OR [forms]![ReportbyCard]![CREDITOR] = "All")
AND
(BillingDates.[Billing Date]=
IIF([forms]![ReportbyCard]![BILLDATE]
="All",#01/01/1899#,CDate([forms]![ReportbyCard]![BILLDATE] ))
OR [forms]![ReportbyCard]![BILLDATE] = "All")

Access could turn that into a mess that is too complex to run.

If you are calling the report from a form, I would drop the where clause and
use something like the following in the code for the button that is opening
the report

Dim StrWhere as string

If IsDate(Me.BiilDate) Then
StrWhere = " AND [Billing Date] = #" & me.BillDate & "#"
End If

IF Me.Creditor <> "ALL" Then
StrWhere = StrWhere & " AND Creditor = """ & me.Creditor & """"
End IF

IF me.CardName <> "ALL" Then
StrWhere = StrWhere & " AND CardHolder = """ & me.CardName & """"
End IF

strWhere = Mid(StrWhere, 5) "strip off any leading " AND "

Docmd.OpenReport "YourReportName",acViewPreview,,strWhere

Signature

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

> Hello -
>
[quoted text clipped - 32 lines]
>
> Any help to fix this would be GREATLY appreciated!

Reply to this Message