Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    9

    Unanswered: help with a dateIn and Dateout query

    I trying have a go at create a basic booking system.

    Im trying to design a query that will tell me which properties are vacant and are currently occupied. However Im a bit stumped as to how go about entering a criteria to give me list of the occupancy and vacany.I only want the record that are current. I dont the query to list all the previous occupants. I need to be able to use this list so that I see what properties are avaible.

    does any one have any thought on how I go about achieving this. Im sure it is very simple but Im brain dead today.

  2. #2
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59

    Something similar

    When I'm designing databases that have to capture information about people that may move off a project, I don't want to delete them (or their related records) when they go. I may need to see who worked on something even if they no longer work here. But I don't want them showing up in list boxes and reports of current personnel.

    It sounds kind of like your problem.

    To fix this, when someone leaves (or when a room booking is over - i.e. they've used the room and now it's free again) I check a box that makes them "inactive."

    Just add a "Current" or "Active" boolean field to your booking records. When a booking is complete and they've used the space, mark the current field as "No" Then you can filter only those records where "[Current] = -1"

    Magee

  3. #3
    Join Date
    Feb 2004
    Posts
    9
    Thats exactly what I was after. I was thinking of some kind of flag but I just wasnt sure where it need to be flagged. Its a good way to handle my situation.

    So you saying i should use a tick box that says something like active. I will need to uncheck the active box when a departure date is entered in the booking form.


    Thanks for the tip.

  4. #4
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59

    Yep!

    That's exactly right.

    Even better, if you're ready to try that kind of thing (don't know what level Access user you are), you can add an onChange event to check and uncheck that field for you.

    So when you change the booking departure date (add a date - the field is not null), you can have it automatically set the Active field to false or 0.

    Magee
    Last edited by mageem; 02-04-04 at 01:09.

  5. #5
    Join Date
    Feb 2004
    Posts
    9
    Yeah I thought of that Too!

    Do you mean something like this

    Private Sub DateIn_AfterUpdate()
    Me.ChkActive = True

    Private Sub Text14_AfterUpdate()
    Me.ChkActive = False
    End Sub

  6. #6
    Join Date
    Feb 2004
    Posts
    9
    I dont suppose you know why I cant get an event procedure to update a form. Im using the below to update a form when the DateIn field in entered. Im certain that the my subform is called subfrmTenant_Property_Bookings

    Forms!frmProperties!subfrmTenant_Property_Bookings .Form.Requery

    see the attached.
    Attached Files Attached Files

  7. #7
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59
    Almost. You'd need to evaluate the condition before checking the field.

    Private Sub DateIn_AfterUpdate()
    If Not Nz(Me.FieldName) Then
    Me.ChkActive=-1 'true
    End If

    'As always, my usual caveat: I'm on a Mac and am working from memory. You'll want to double-check the syntax.

    Then to your other stuff. Assuming your default value is either null or false, this is sufficient.

    Magee

    About the subform thing. I can't remember object reference syntax ever. I have to look it up every time. I suppose now that I'm doing Access all the time that'll change.

    I'm way out of bounds of what I know without looking it up. But the error message that you uploaded gives a good clue. It thinks that you're referring to a field, not a form. I'd start with double-checking the object reference syntax for referring to subforms.
    Magee

    Originally posted by blinky
    Yeah I thought of that Too!

    Do you mean something like this

    Private Sub DateIn_AfterUpdate()
    Me.ChkActive = True

    Private Sub Text14_AfterUpdate()
    Me.ChkActive = False
    End Sub
    Last edited by mageem; 02-04-04 at 18:53.

  8. #8
    Join Date
    Feb 2004
    Posts
    9
    I'll get back to you a bit later..im told that i have to be on a training right now..dam!

Posting Permissions

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