Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Carrying Dates forward (2000 SR-1)

    I want to create a new record in tblRental that takes the date in the PaidtoDate field from last
    room rental record for a particular room and puts it in the RentBeginDate field for that room in the
    new record. The PaidtoDate field is a calculated field from qryRentCalc that adds the number of
    days in the rental period plus 1 to the RentBeginDate. The RentBeginDate is a field in tblRental

    Any suggestions for a novice.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Carrying Dates forward (2000 SR-1)

    FIrst tell us how you're creating the new record? Are you doing it in a form or with and update query or using a recordset in code? In any case, you're going to wind up using something approximately like this, assuming that your room number field is called "room":

    DMax("PaidtoDate","qryRentCalc","[room]=" & [room])

    That should return the latest paidtodate for that room. The exact syntax would depend on where and how you were creating the record.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Carrying Dates forward (2000 SR-1)

    The new record will be created from a form.

    I want the new record to contain some identical information from the old record as well as having the RentBeginDate in the new record to be the same as the PaidtoDate from the old record.

    Old record
    LastName = Jones
    FirstName = John
    RoomNumber = 110
    RentBeginDate = 06/01/02
    NumberofDays = 7
    RentEndDate = 06/07/02
    PaidtoDate = 06/08/02

    New Record
    LastName = Jones
    FirstName = John
    RoomNumber = 110
    RentBeginDate = 06/08/02
    NumberofDays = 7
    RentEndDate = 06/14/02
    PaidtoDate = 06/15/02

    Thanks

    Tom

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Carrying Dates forward (2000 SR-1)

    But how are you creating the new record? Just by going to the new record with the recordselector, by clicking an Add button, or how?

    Do you have a parent form displaying information for John Jones with a subform showing his rental record or a continuous main form or what? Is the recordset filtered down to just John Jones or does it show everyone? Are the records sorts or filtered in any particular way?
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Carrying Dates forward (2000 SR-1)

    The main menu has a button

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Carrying Dates forward (2000 SR-1)

    So the "current guest" information that you want to carry forward is the result of the form filling in the information after the operator enters the name and room number? Or is the operator required to enter all the other information on that guest as well? Logically, it seems like it would be easier to find the particular guest and room (and most recent RentBeginDate) and make the extend button available on that form. Then the information would already be there for you to carry forward.
    Charlotte

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Carrying Dates forward (2000 SR-1)

    I notice that you have a RentEndDate and a PaidToDate. These allways seem to be 1 day apart, is this correct?
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Carrying Dates forward (2000 SR-1)

    That is correct. The RentEndDate is the last night in the rental period. If the guest is checking out, (s)he leaves the next day.

    Tom

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Carrying Dates forward (2000 SR-1)

    So PaidToDate is actually the departure date?
    Charlotte

  10. #10
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Carrying Dates forward (2000 SR-1)

    The PaidtoDate is the departure date if the guest is leaving. If the guest is staying, it is the renewal date i.e. the RentBeginDate of the next rental period.

    In regard to an earlier post,

    I not sure how to find the last record. I tried using the Dmax() function in both the
    RentBeginDate field and also in the PaidtoDate field (not at the same time).

    The query is qryReceiptListingLast

    I tried putting the following in the criteria of the 2 date fields in order to get the most recent record for the room. If possible, I need to use the [PaidtoDate] to check for latest record.

    Dmax(

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Carrying Dates forward (2000 SR-1)

    You have to specify criteria with DMax that will give you the latest date for that guest and room, not just the latest date. Try something like this:

    Dmax(
    Charlotte

  12. #12
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Carrying Dates forward (2000 SR-1)

    I understand now that a field that you aren't using for numeric calculations shoud be set up as a text field. Originally, the [RoomNumber] field was set up as a numeric field.

    Is there a way to modify the function to use a numberic field? Currently, qryReceiptListingLast is a parameter query that asks for the RoomNumber when run.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Carrying Dates forward (2000 SR-1)

    <hr> a field that you aren't using for numeric calculations shoud be set up as a text field.<hr>
    You can start a lot of debates with a statement like that. It's one of those rules that isn't really all that hard and fast. If your room numbers are always numeric (i.e., no room 301-A) then you can certainly use a number if you wish. You don't use numeric fields for things like telephone numbers or zip codes, etc., but searches and indexes on numeric fields are faster than on text, so the real answer is "it depends."

    If the query is a parameter query, you aren't going to be able to use DMax on it because you can't pass the parameter in properly. So either use the table itself or a query without the parameter to return the latest date. You could do it in code by setting a querydef object to the query and then passing it the parameter in code, but if you aren't comfortable with VBA code, it would be simpler to just use a query without the parameter, at least for purposes of the Dmax, which uses its own criteria to filter the result.
    Charlotte

  14. #14
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Carrying Dates forward (2000 SR-1)

    Many thanks for yur patience and assistance.

    I had trouble getting the DMax function to work properly. As an alternative, I set up a parameter
    query with [Enter Room Number} as the parameter. I sorted [PaidtoDate] in descending order
    and set the Top Value Property = 1. Initial testing returns the latest record for the requested
    room.

    Now that I have the latest record for the room available, I need to pass values in this record to the
    new record. I will do some more research and re-post if I can't figure it out.

    Thanks again.

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Carrying Dates forward (2000 SR-1)

    You can open a recordset based on that query and retrieve the information from its fields, but you have to be careful handling parameter queries in code because you may not see the parameter dialogs.
    Charlotte

Posting Permissions

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