Results 1 to 12 of 12
  1. #1
    Join Date
    May 2004
    Posts
    80

    Question Unanswered: IIf statements, validation problems - answers needed!

    I have two tables, one called Tables and the other called Reservations. In Tables, I have a Yes/No field called Occupied, and a Yes/No field called Reserved. In Reservations, I have the fields Date and Time.

    What I need is a relatively simple control source for Occupied, so that when the current time is equal to or after the value specified in Time, then the Occupied checkbox will be ticked, and similarly, when it is returned as false, then it will remain blank.

    I tried using the following IIf statement to achieve this, but it doesn't seem to like checking data across tables, as it removes the square brackets around [Reservations]![Time] by default, and my pathetic knowledge of coding means I don't know how to fix it.

    =IIf([Time]>=Reservations!Time,Yes,No)

    Also, how can I use a similar statement to check to see whether there is a record in Reservations that relates to the table number I enter into the TableNo field of Tables, so that the Reserved Yes/No checkbox is ticked if it finds a record? (Somehow, I suppose, it will need to validate that the reservation record it finds in Reservations is applicable to the current date - no matter about the time - or else the Reserved checkbox would be almost permanently ticked!)

    I also have a table called Orders (for full details about my table structure, see the attachment) with a Yes/No field called Paid. When this is checked (to represent the 'ending' of a reservation) then I need both the Occupied and the Reserved Yes/No fields in Tables to be unchecked, provided that:

    - The TableNo of the Reservation that is contained within the Orders table is the same as that which is in the TableNo field at that time.

    Any suggestions or comments would be much appreciated.

    - Grace
    Attached Thumbnails Attached Thumbnails screendumpdbforums.gif  
    Last edited by graceadair; 05-14-04 at 05:39.

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ok, i'm slightly confused.

    are we in a query?

    if we are, you will want the field Reservations!Time in your query and your query iif should be fine.

    if we are not in a query ... i'm still confused.



    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Grace

    I noticed you have had no response as yet so thought I'd offer my humble opinion on a couple of things.
    The first thing I would suggest would be to change your Time and Date field names if at all possible - there is a time statement, a time function and a date function in Access. As a rule of thumb it is wise to avoid naming any of your fields or objects any name used by Access to mean something else, as you can confuse yourself, Access or sometimes both at the same time. We have all done it - I speak from experience. Your iif statement takes a second glance to read, despite it being quite simple in meaning.

    Another possible consideration is the storing of data that can be derived from other data. This is, under normal circumstances, a database faux pas - unless you have a very good reason to do it. In particular, your derived field is time dependant so not only is it derived but the value could potentially change at any second, which means you need to update every value in the table (or at least test if each one needs updating) pretty well constantly. Derive it as you need it and you need only calculate as much as you show to the user.

    Anyway, I will try and offer something more constructive. I'm not sure where your iff statement is sat - is it in the control source of a form control? if so, is the Reservations!Time part of the form record source, and the Reservations object unambiguously a table name (e.g. it's not the name of the form or anything)? Actually, I've just reread your iif statement - and I think you've proved my first point! The Time function shouldn't be enclosed in [...] - only object names should be so enclosed. Also, don't worry about []s disappearing. their main useful purpose is to indicate that a name with a space in it is one object (e.g. [object name]).

    Anyway, I've probably done your head in enough by now and my wine glass is empty. see how you go and let us know. Then we can look at the rest of the post.
    Cheers.

    Yeah, ok. It took a long time to write and Izy got in there before me.
    Last edited by pootle flump; 05-14-04 at 15:32.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    May 2004
    Posts
    80

    Well....it's actually in the control source...

    From what you guys were saying, I think I really don't know much of what I'm doing, I just trundle along trying to pick up bits and pieces of things from everywhere (which is why I'm confusing people so much!) Although I did manage to successfully make a control source code last night, and it works, I was so proud of that!!

    Anyway, back to my original post. I was trying to figure all of this out in a control source, and so the [Reservations]![Time] thingie was what I assumed to be a way of extracting the data, if you like, from another table. I could of course (and am guessing, actually) that I'm wrong in this area. I was up until 2am last night trying to work it all, and it frustrated me so much, especially because I don't have enough knowledge at all to grasp the simpler concepts.

    If you guys do have a solution for my original problems, then that's great, but if you can offer any better solutions to achieve a similar outcome without all the messiness of deriving external data, as you mentioned, then that would be great too.

    I think, somehow, that I'm gonna need to buy one of those Dummies books.

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Firstly, I agree with pootle flump 100% on naming of fields etc.

    From your description Occupied and Reservation could be in the one table. Where it would be different is if Occupied stored details on a customer and you wanted to keep records for each reservation associated with or made by that custom.

    But if it is one of stuff then I assume you make an entry that a reservation has been made for Mr Smith on 20 May 2004 at 8pm and what you swant is something that shows the current date and time has equalled or exceeded 20/4/2004.

    A very simple first up solution to the problem is to have form based on a table that contains the Mr Smith and his date and time.

    Lets call the fields Customer and ArrivalTime and ArrivalDate and call the form BookingForm

    You now make a simple macro where the action is OpenForm and the Where condition is

    [ArrivalTime]>=Time() And [ArrivalDate]>=Date()

    Let us say you have 300 records in your form. If you place a command button on the form with the above macro running OnClick or place a label on the form and the above macro running off OnClick and then click on either then:

    If 11 records met the above conditions of
    [ArrivalTime]>=Time() And [ArrivalDate]>=Date()

    Then the navigation buttons at the bottom of the form would show 1 of 11 Filtered.

    If you store Reservations in a separate table then the same Where condition applies but applies to a form made on the second table.

    Generally with this sort of thing you would have the form that opened for all the records that met [ArrivalTime]>=Time() And [ArrivalDate]>=Date() a continuous form. Thus if there were 11 records that met the conditions they would all display on the one screen at the same time, in other words, a tabular display.

    If you are using a Where statement like

    [ArrivalTime]>=Time() And [ArrivalDate]>=Date()

    and if it refers to a form that is different to the form from where the macro will run then instead of just field names you need to fully refer to the other form such as:

    [Forms]![FormName]![ArrivalTime]>=Time() And [Forms]![FormName]![ArrivalDate]>=Date()

    Mike

  6. #6
    Join Date
    May 2004
    Posts
    80

    Clarification on other stuff

    Quote Originally Posted by Mike375

    You now make a simple macro where the action is OpenForm and the Where condition is

    [ArrivalTime]>=Time() And [ArrivalDate]>=Date()

    Let us say you have 300 records in your form. If you place a command button on the form with the above macro running OnClick or place a label on the form and the above macro running off OnClick and then click on either then:

    If 11 records met the above conditions of
    [ArrivalTime]>=Time() And [ArrivalDate]>=Date()

    Then the navigation buttons at the bottom of the form would show 1 of 11 Filtered.

    If you store Reservations in a separate table then the same Where condition applies but applies to a form made on the second table.

    Generally with this sort of thing you would have the form that opened for all the records that met [ArrivalTime]>=Time() And [ArrivalDate]>=Date() a continuous form. Thus if there were 11 records that met the conditions they would all display on the one screen at the same time, in other words, a tabular display.

    If you are using a Where statement like

    [ArrivalTime]>=Time() And [ArrivalDate]>=Date()

    and if it refers to a form that is different to the form from where the macro will run then instead of just field names you need to fully refer to the other form such as:

    [Forms]![FormName]![ArrivalTime]>=Time() And [Forms]![FormName]![ArrivalDate]>=Date()
    I am pretty sure I understand where you are coming from, but I just want to make sure. Is your suggestion about putting a macro in a button on the Tables form, so that when it is clicked it returns all records from the Reservations table, whose ArrivalDate and ArrivalTime fields are equal to the current date and time?

    If so, could you please explain to me (in steps, so that I know exactly what I need to do) in order to do this? As I've recently discovered, there are so many ways of implementing macros and queries and codes and expressions, that my poor little blonde head just can't quite keep up with it.

    Part of my reasoning for this was that, as part of the assignment, we need to implement a query that runs between at least two tables to achieve an outcome - in this case, the Tables table and the Reservations table. For one of my outcomes, I'd like the user to be able to search for all available tables according to custom criteria (parameters). If I use a macro instead to call up all records according to this, then do you have any ideas of how I can get a list of all the table numbers currently not occupied or reserved in report format, via a convential query?

    Alternatively, would your macro idea work at all when combined with a subform to show only a list of records in datasheet format of only those tables whose ArrivalDate and ArrivalTime attributes are not equal to the current date and time? Perhaps this is too complicated.

    In short, I just need a simple way of running a query involving Tables and Reservations to produce a report with a list of all available tables at a user-inputted date and time, and without the user having to manually tick or untick the Occupied or Reserved checkboxes.

    I hope I haven't confused you too much. If this is what you were originally telling me, then I'm sorry. Otherwise, thanks.

  7. #7
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    I am pretty sure I understand where you are coming from, but I just want to make sure. Is your suggestion about putting a macro in a button on the Tables form, so that when it is clicked it returns all records from the Reservations table, whose ArrivalDate and ArrivalTime fields are equal to the current date and time?

    That is right. Or if had one table it would either filter the form you are on or open another. I figured this type of thing was best because it sounded like a cafe or restaurant and if someone phoned for a table at 8.15pm then a click on the button or label would display all the records of people who have not yet turned up for their reservation.

    If so, could you please explain to me (in steps, so that I know exactly what I need to do) in order to do this? As I've recently discovered, there are so many ways of implementing macros and queries and codes and expressions, that my poor little blonde head just can't quite keep up with it.

    Part of my reasoning for this was that, as part of the assignment, we need to implement a query that runs between at least two tables to achieve an outcome - in this case, the Tables table and the Reservations table. For one of my outcomes, I'd like the user to be able to search for all available tables according to custom criteria (parameters). If I use a macro instead to call up all records according to this, then do you have any ideas of how I can get a list of all the table numbers currently not occupied or reserved in report format, via a convential query?


    I did not realise that you were doing it as an assignment. My stuff might not be suitable for assignment purposes.

    But anyway The Where condition in the maco become Is Null. Basically the searches or criteria are about the same on a macro as a query.

    Are you familiar with changing the record source of a form? Lets say the macor opens a form displaying 11 records and that is based on the Time and Date criteria. In a query you would put the time criteria in the field you have for time and the Date() in the criteria for the field you have for the date. When the criteria are on the same line it act as AND. If put one on one line and the other on the line below then it becomes OR that is Time or Date.

    Such a query will return the same 11 records. However a query is a bit useless once it is open a compared to a form. Now back to changing the record source of the form.

    In a macro it is done with the SetValue action.

    The Item is

    [Forms]![ReservationForm].[RecordSource]

    and the expression is

    "Yourqueryname"

    Alternatively, would your macro idea work at all when combined with a subform to show only a list of records in datasheet format of only those tables whose ArrivalDate and ArrivalTime attributes are not equal to the current date and time? Perhaps this is too complicated.

    The openform macro action also has the paramter to select how the form is opened or displayed and DataSheet is one of the views.

    In short, I just need a simple way of running a query involving Tables and Reservations to produce a report with a list of all available tables at a user-inputted date and time, and without the user having to manually tick or untick the Occupied or Reserved checkboxes.

    My thinking is directed towards actual use rather than assigment. For example it is reasonable to say that one of the times when you want to see a list of people who are late for their reservation is because someone else has phoned up about a table. So you want something real handy to make another reservation. For example you might have a macro or code running OnClick on one of the fields of the list of records where people are late and that macro clears their entry and opens up a form ready for the new reservation etc.

    I hope I haven't confused you too much. If this is what you were originally telling me, then I'm sorry. Otherwise, thanks.

    Not a problem. It can be real hair pulling stuff at times.

    In general, I think getting advice for assigment is best coming from either someone who works in a larger company or a consultant who does much of his/her work for larger companies. They tend do it in the more acceptable or copybook manner which I imagine would line up much closer to assignment criteria.

    Mike

  8. #8
    Join Date
    Jul 2003
    Posts
    73
    I'm with the others here - Occupied and Reserved shouldn't really be on the Tables table - as they vary according to time. You could, of course, check the reservations every minute and update the two fields accordingly - but that could be a bit of a waste.

    If you wanted to get a list of the tables - and whether they are currently occupied by somebody - you could write:

    Code:
    SELECT a.TableNo, a.Capacity, 'Occupied' as TableState FROM Tables a, Reservations b
    WHERE a.TableNo = b.TableNo
    and b.DateTime = #5/15/2004 11:00:00#
    UNION
    SELECT a.TableNo, a.Capacity, 'Not-Occupied' as TableState 
    FROM Tables as a LEFT OUTER JOIN Reservations as b ON a.TableNo = b.TableNo
    WHERE (b.DateTime IS Null OR NOT b.DateTime =  #5/15/2004 11:00:00#)
    AND NOT EXISTS (SELECT 1 FROM Tables c, Reservations d WHERE a.TableNo = c.TableNo AND c.TableNo = d.TableNo and d.DateTime = #5/15/2004 11:00:00#)
    Now, let me (try) to explain it

    For the example - I've combined your Date and Time field. I've used #5/15/2004 11:00:00# as an example time that would normally be inputted by a user.

    The Left Outer Join basically says - get me ALL of the tables details, and any of the Reservation details if they join on TableNo. If there are no reservation details for a table - the reservation fields are left Null (we'll use this later on).

    The first part of the query gets the tables that have a reservation in the Reservations table for the given DateTime. It set's the virtual column (just for this query) of TableState to 'occupied'.
    The second part looks for Tables that either don't have any reservations at all (Reservations.PartySize Is Null) or the reservation is for a different time (OR NOT b.DateTime = #5/15/2004 11:00:00#). These ones set the TableState to 'Not-Occupied'.

    Now this would be fine - but if table 1 has a reservation for the time you're looking for - AND a different time - it would be included in both parts of the query. So the:

    Code:
    AND NOT EXISTS (SELECT 1 FROM Tables c, Reservations d WHERE a.TableNo = c.TableNo AND c.TableNo = d.TableNo and d.DateTime = #5/15/2004 11:00:00#)
    is basically to ensure these records have not already come up in the 'Occupied' records.

    This query could be saved as a query Build a new query in design mode - don't add any tables and go to SQL mode), and used for a report / whatever.

    You could also do things like:

    Tables that have never been reserved:

    Code:
    SELECT a.TableNo, a.Location
    FROM Tables as a LEFT OUTER JOIN Reservations b ON a.TableNo = b.TableNo
    WHERE b.PartySize IS Null
    Have I confused you yet? 'Cause I certainly am!!! That's why this post took so long
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  9. #9
    Join Date
    Jul 2003
    Posts
    73
    Also, if you wanted the form you use to view the Tables to show it's current state (i.e. is it occupied) - which is kind of what you wanted with the Occupied field on the table - you could use the query I had above as the Form's Record Source - and bind a label called Occupied to the TableState column.

    Let me know if you need to understand this a little better (as it is your assignment - I guess you should know what you've done )
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  10. #10
    Join Date
    May 2004
    Posts
    80

    Holy moley....great, but whoa

    Thanks Mike and Joel for your replies...they look great...

    I think I'm going to have to put them away and read them some other time. I've been at databases all day and I'm fed up to death with the sight of them. And then I just found a criterion for our assignment that we cannot have fields depending on other fields, except for the keys.



    I HATE TEACHERS AND STUPID SACE MODERATORS!!

    Sorry for the outburst. I am so, so frustrated.

    But thanks again for everything, and I will definitely keep trying (and using all your excellent work).

    - Grace

  11. #11
    Join Date
    Jul 2003
    Posts
    73
    I can remember my Access Assignment when I was in Year 11 - man I lost a lot of sleep with that one. And back then I think we were using Access 97 - so it wasn't as nice as you have it now

    Don't worry - just keep annoying your teacher (or us ) with questions until you start to understand it. It does get easier after a while! Then you'll be replying to these kinds of threads with all the answers

    Have a good weekend!
    Joel Dixon
    Analyst Programmer
    Melbourne, Australia

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Grace

    Just wanted to throw in a few lines of support! Don't start talking about poor little blond heads - the only easy answers are the ones you already know; some of the stuff covered so far is reasonably complex (you think we didn't need to read Joel's SQL statement fairly slowly?). To find the silver linings- not being able to include fields whose values are based on other fields is pretty well where this discussion started, so a lot of the other posts actually address how to derive the values rather than store them so at least you aren't starting out from scratch.
    Keep plugging away and it will come together. Also, and you may not be in the mood for looking at it all philosophically, always remember that if you write something and it works then you never have to bother understanding it. If it doesn't then you have find out why. I've learnt far more from correcting my frequent mistakes than from any book.
    Best of luck
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •