Re: Fields not required



"Gordon Youd" <gordon@xxxxxxxxxxxxxxxxx> wrote in
news:e25pic$im5$1$8300dec7@xxxxxxxxxxxxxxxx:

Bob, I still cannot prevent an "InwardTime" showing on an
"OutwardDate Customer" or an "OutwardTime" showing on an
"InwardDate Customer".

To put you in the picture, the database is for cars to and
from airports. Pickup a passenger from home at outtime, take
to airport on an outdate. Pickup any intime passengers on the
same date and take them home.

The database holds "CustomerID", OutwardDate", "OutwardTime",
"InwardDate", "InwardTime"

The report I want to create is to see on a particular date,
which customers are going out and which customers are coming
in. I can then see if a driver dropping off a customer could
wait and pick up an incoming passenger.

I open report for the 10/10/2006 and see,
"Mr Brown" going out "10/10/2006", "15:00", coming in
"20/10/2006",
"12:00".
"Mr Green" coming in "10/10/2006", "16:00", going out
"5/10/2006", "09:00"

Mr Brown should not show (coming in "20/10/2006", "12:00")
Mr Green should not show (going out "5/10/2006", "09:00")

Bob, using your SQL, I get the customers going out and in on
the selected date, in the right columns on the report.
I just cannot suppress the wrong info....

Regards, Gordon.

I don't remember exactly What I gave you as code, but If I start
from fresh, here's something that should work at the query.

PARAMETERS
[Date To Report]

SELECT
IIF([InwardDate]= [Date to report], "IN","OUT")
as destination,
CustomerID,
IIF([InwardDate]= [Date to report],[InwardTime],[OutwardTime])
as EventTime
WHERE InwardDate = [Date To Report]
OR OutwardDate = [Date to Report]
ORDER BY
IIF([InwardDate]= [Date to report], 1,2),
IIF([InwardDate]= [Date to report],[InwardTime],[OutwardTime])

Play with that, till you get something you can use.


--
Bob Quintal

PA is y I've altered my email address.
.



Relevant Pages

  • Re: Sum of numbers
    ... Administrator to come in to insure we have this critical report by April. ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)
  • Re: Programmer (or A Techinical person) in a Tester ...
    ... And then you report it ... And then if the bug happens again, boom - the developers have two ... Imagine the cook who just makes food or imagines his/her customers ... worse skills, nicht wahr? ...
    (comp.software.testing)
  • Re: Sum of numbers
    ... Administrator to come in to insure we have this critical report by April. ... You say the union query 'only shows fields from the first table'. ... Do you mean that you want a multicolumn report with all customers' names ...
    (microsoft.public.access.reports)