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 "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?
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 6 1373
"Gordon Youd" <go****@gyoud.demon.co.uk> wrote in
news:e1*******************@news.demon.co.uk: 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
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
--------------------------------------------------------------
- ---------------- I want to find from the "Booking" table all "Customers" whose "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?
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
PA is y I've altered my email address.
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" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135... "Gordon Youd" <go****@gyoud.demon.co.uk> wrote in news:e1*******************@news.demon.co.uk:
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
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
-------------------------------------------------------------- - ---------------- I want to find from the "Booking" table all "Customers" whose "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?
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
PA is y I've altered my email address.
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" <go****@gyoud.demon.co.uk> wrote in message
news:e1*******************@news.demon.co.uk... 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, 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.
"Gordon Youd" <go****@gyoud.demon.co.uk> wrote in
news:e2*******************@news.demon.co.uk: 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.
Sorry Bob, cannot get it to work, just blank fields.
Gordon.
----------------------------------------------------------------
"Bob Quintal" <rq******@sympatico.ca> wrote in message
news:Xn**********************@207.35.177.135... "Gordon Youd" <go****@gyoud.demon.co.uk> wrote in news:e2*******************@news.demon.co.uk:
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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Jon Smyth |
last post by:
I am trying to create a database programmatically using vb5 with the DAO 3.6
object. I'm using the following segment, but I'm not having any luck. I
can't seen to find much help either. Any...
|
by: CSDunn |
last post by:
Hello,
I have a situation with MS Access 2000 in which I need to display report
data in spreadsheet orientation (much like a datasheet view for a form). If
you think of the report in terms of what...
|
by: bufbec1 |
last post by:
I am pretty good with Access, but do not understand VBA. I have
researched this topic and see only VBA answers, so I hope someone can
help with my specific question.
I have 2 fields for an...
|
by: allyn44 |
last post by:
HI--what I am trying to do is 2 things:
1. Open a form in either data entry mode or edit mode depending on
what task the user is performing
2. Cancel events tied to fields on the form if I am in...
|
by: danavni |
last post by:
I have a form where the user need to fill some fields. some of these
fields are address fields (street, city, state, zip)
i want to validate that atleast one of these fields if filled with
data....
|
by: TORQUE |
last post by:
Hi,
Im wondering if anyone can help me with a problem.
I have a form with more than 50 unbound fields.
Some of the fields will be blank from time to time.
This seems to be where im having...
|
by: Tom van Stiphout |
last post by:
I want to indicate requiredness by setting the background color of the
control. Ideally I would call a sub in Form_Load to just do its thing.
Below is what I have so far, but I realize this is of...
|
by: Dave |
last post by:
I have multiple forms that will create an object. Basically a energy
efficiency measure object. The measure object will have a couple of
required properties set but after that it can have 10-20...
|
by: KMEscherich |
last post by:
Using ACCESS '97
Hi there. Am wondering what would be the best way for Access to recognize that each of the following fields have changed and drop in a date in the DATE_NOT_REQ control. It seems...
|
by: KMEscherich |
last post by:
Hi there, am wondering if there is a way to have this code capture 2 dates. You see, I have several fields and some are REQUIRED fields and some are NON-REQUIRED fields. I am attempting to capture...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |