Fields not required

Bob, your version works perfectly.
I have changed SELECT * to SELECT customer, outdate, outwardtime, indate,
inwardtime, iif([input the date] = indate, 1, 2) AS sortkey
The customer going 'outdate' have a 'outwardtime'.
The customer arriving 'indate' have an 'inwardtime'

I am getting the 'outwardtime' and 'inwardtime' showing for all customers.
if a customer is going 'outdate' they cannot have an 'inwardtime' until a
later date
and visa versa.


Can anyone help please
-------------------------------------------------------------------------------
I want to find from the "Booking" table all "Customers" whose[color=blue]
> "Indate" matches the "wildcard" input and all "Customers"
> whose "Outdate" matches the same "Wildcard.
>
> No "Customer" would ever have the same "Indate" and "Outdate".
>
> I would have a list of customers coming "IN" and a list of
> customers going "OUT on the same date.
>
> I can then deal with both customers on the same day. The
> Report would show the "INs" in the left hand column, the
> "OUTs" in the right hand column.
>
> Does this help?[/color]

the third version of the SQL I posted will work. You, just need
to add some grouping on the report to get the column to advance
columms on the switch of date field. the easiest way is to add a
boolean expression to the query,

SELECT *, iif([input the date] = indate, 1, 2) AS sortkey
FROM Bookings
WHERE [input the date] = indate
OR [input the date] = outdate
ORDER BY iif([input the date] = indate, 1, 2); .

Go to the Page Setup menu item, make the detail section of the
report 1/2 the width of the header, set the order to "Down, then
Across"

Open the Sorting and Grouping dialog, group on sortkey. set a
header for the group,
Set the group header property New Row or Column to "before" and
voila, its done.


--
Bob Quintal

re: Fields not required

"Gordon Youd" <gordon@gyoud.demon.co.uk> wrote in
news:e1paoe$9oa$1$8300dec7@news.demon.co.uk:
[color=blue]
> Bob, your version works perfectly.
> I have changed SELECT * to SELECT customer, outdate,
> outwardtime, indate, inwardtime, iif([input the date] =
> indate, 1, 2) AS sortkey The customer going 'outdate' have a
> 'outwardtime'. The customer arriving 'indate' have an
> 'inwardtime'
>
> I am getting the 'outwardtime' and 'inwardtime' showing for
> all customers. if a customer is going 'outdate' they cannot
> have an 'inwardtime' until a later date
> and visa versa.
>
>
> Can anyone help please[/color]

yes, there are several options.

1) since this is a report, if I understood you correctly, from
past correspondence, you can set a calculated field on the
report.

this would contain an expression, that uses the same sortkey to
choose which time to display:
=iif(sortkey=1,[inwardtime],[outwardtime])

2) add a similar expression into the query. Since sortkey isn't
available to use in an expression in the query, you have to nest
the iif inside the new iif.

iif(iif([input the date] = indate, 1,
2)=1,[inwardtime],[outwardtime]) as DeskTime (I gave it that
name because that's when I think they'll be at the hotel's
desk.)

3) you can use the sortkey to make one textbox or the other
invisible on the report, with a little code in the detail
section OnFormat Event..


Q

[color=blue]
> --------------------------------------------------------------[/color]
-[color=blue]
> ----------------
> I want to find from the "Booking" table all "Customers" whose[color=green]
>> "Indate" matches the "wildcard" input and all "Customers"
>> whose "Outdate" matches the same "Wildcard.
>>
>> No "Customer" would ever have the same "Indate" and
>> "Outdate".
>>
>> I would have a list of customers coming "IN" and a list of
>> customers going "OUT on the same date.
>>
>> I can then deal with both customers on the same day. The
>> Report would show the "INs" in the left hand column, the
>> "OUTs" in the right hand column.
>>
>> Does this help?[/color]
>
> the third version of the SQL I posted will work. You, just
> need to add some grouping on the report to get the column to
> advance columms on the switch of date field. the easiest way
> is to add a boolean expression to the query,
>
> SELECT *, iif([input the date] = indate, 1, 2) AS sortkey
> FROM Bookings
> WHERE [input the date] = indate
> OR [input the date] = outdate
> ORDER BY iif([input the date] = indate, 1, 2); .
>
> Go to the Page Setup menu item, make the detail section of the
> report 1/2 the width of the header, set the order to "Down,
> then Across"
>
> Open the Sorting and Grouping dialog, group on sortkey. set a
> header for the group,
> Set the group header property New Row or Column to "before"
> and voila, its done.
>
>[/color]



--
Bob Quintal

PA is y I've altered my email address.

re: Fields not required

Many thanks for the help Bob.

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 of his choice on an
outdate.
Pickup any intime passengers on the same date and take them home.

So far I have purchased 3 books on Access & SQL to solve the problems, but
they do not seem to clearly state how not to show a field.

Regards, Gordon.

------------------------------------------------------------------------------------------------------------------------
"Bob Quintal" <rquintal@sympatico.ca> wrote in message
news:Xns97A652F431F11BQuintal@207.35.177.135...[color=blue]
> "Gordon Youd" <gordon@gyoud.demon.co.uk> wrote in
> news:e1paoe$9oa$1$8300dec7@news.demon.co.uk:
>[color=green]
>> Bob, your version works perfectly.
>> I have changed SELECT * to SELECT customer, outdate,
>> outwardtime, indate, inwardtime, iif([input the date] =
>> indate, 1, 2) AS sortkey The customer going 'outdate' have a
>> 'outwardtime'. The customer arriving 'indate' have an
>> 'inwardtime'
>>
>> I am getting the 'outwardtime' and 'inwardtime' showing for
>> all customers. if a customer is going 'outdate' they cannot
>> have an 'inwardtime' until a later date
>> and visa versa.
>>
>>
>> Can anyone help please[/color]
>
> yes, there are several options.
>
> 1) since this is a report, if I understood you correctly, from
> past correspondence, you can set a calculated field on the
> report.
>
> this would contain an expression, that uses the same sortkey to
> choose which time to display:
> =iif(sortkey=1,[inwardtime],[outwardtime])
>
> 2) add a similar expression into the query. Since sortkey isn't
> available to use in an expression in the query, you have to nest
> the iif inside the new iif.
>
> iif(iif([input the date] = indate, 1,
> 2)=1,[inwardtime],[outwardtime]) as DeskTime (I gave it that
> name because that's when I think they'll be at the hotel's
> desk.)
>
> 3) you can use the sortkey to make one textbox or the other
> invisible on the report, with a little code in the detail
> section OnFormat Event..
>
>
> Q
>
>[color=green]
>> --------------------------------------------------------------[/color]
> -[color=green]
>> ----------------
>> I want to find from the "Booking" table all "Customers" whose[color=darkred]
>>> "Indate" matches the "wildcard" input and all "Customers"
>>> whose "Outdate" matches the same "Wildcard.
>>>
>>> No "Customer" would ever have the same "Indate" and
>>> "Outdate".
>>>
>>> I would have a list of customers coming "IN" and a list of
>>> customers going "OUT on the same date.
>>>
>>> I can then deal with both customers on the same day. The
>>> Report would show the "INs" in the left hand column, the
>>> "OUTs" in the right hand column.
>>>
>>> Does this help?[/color]
>>
>> the third version of the SQL I posted will work. You, just
>> need to add some grouping on the report to get the column to
>> advance columms on the switch of date field. the easiest way
>> is to add a boolean expression to the query,
>>
>> SELECT *, iif([input the date] = indate, 1, 2) AS sortkey
>> FROM Bookings
>> WHERE [input the date] = indate
>> OR [input the date] = outdate
>> ORDER BY iif([input the date] = indate, 1, 2); .
>>
>> Go to the Page Setup menu item, make the detail section of the
>> report 1/2 the width of the header, set the order to "Down,
>> then Across"
>>
>> Open the Sorting and Grouping dialog, group on sortkey. set a
>> header for the group,
>> Set the group header property New Row or Column to "before"
>> and voila, its done.
>>
>>[/color]
>
>
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.[/color]

re: Fields not required

Bob, further to my previous post.

Yes it is a Report, from which I can see Out going and In going on the same
date, and view the OutTime and the InTime.

Regards, Gordon.


-------------------------------------------------------------------------------------------------------------
"Gordon Youd" <gordon@gyoud.demon.co.uk> wrote in message
news:e1qthr$fhf$1$8300dec7@news.demon.co.uk...[color=blue]
> Many thanks for the help Bob.
>
> 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 of his choice on
> an outdate.
> Pickup any intime passengers on the same date and take them home.
>
> So far I have purchased 3 books on Access & SQL to solve the problems, but
> they do not seem to clearly state how not to show a field.
>
> Regards, Gordon.[/color]

re: Fields not required

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.

re: Fields not required

"Gordon Youd" <gordon@gyoud.demon.co.uk> wrote in
news:e25pic$im5$1$8300dec7@news.demon.co.uk:
[color=blue]
> 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.
>[/color]
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.

re: Fields not required

Sorry Bob, cannot get it to work, just blank fields.

Gordon.

----------------------------------------------------------------
"Bob Quintal" <rquintal@sympatico.ca> wrote in message
news:Xns97AAB6003F960BQuintal@207.35.177.135...[color=blue]
> "Gordon Youd" <gordon@gyoud.demon.co.uk> wrote in
> news:e25pic$im5$1$8300dec7@news.demon.co.uk:
>[color=green]
>> 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.
>>[/color]
> 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.[/color]