Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    55

    Unanswered: Cyherus--DLookup

    Cyherus-- For some reason I could not reply to the old thread. This is about the RESERVATION database. I tried Dlookup but it gave me a type mismatch error.

    var = DLookup("[ID]", "HISTORY", "[START_DATE] = #" & Forms!HISTORY!START_DATE & "# " And "[END_DATE] = #" & Forms!HISTORY!END_DATE & "# " And "[ITEM_NAME] = '" & Forms!HISTORY!ITEM_NAME & "'")

    Am I doing something wrong here?
    It works for START_DATE but not for
    And "[END_DATE] = #" & Forms!HISTORY!END_DATE & "# "

    Dmesh

  2. #2
    Join Date
    May 2004
    Posts
    55
    I figured that one out..it was some syntax problem..But now I have an issue

    START_DATE END_DATE START_TIME END_TIME ITEM_NAME
    4/27/2004 4/27/2004 8:00:00 9:00:00 X
    4/27/2004 4/27/2004 8:30:00 9:30:00 X

    The user should not be able to rserve item X. How do I check for this?

  3. #3
    Join Date
    Jun 2004
    Posts
    96
    dmesh, search for "X" and start_date and start_time are within the start_date/time and end_date/time in the history table. In this case, you need not care about end_date/time in your booking.

    Cyherus

  4. #4
    Join Date
    May 2004
    Posts
    55
    Quote Originally Posted by Cyherus
    dmesh, search for "X" and start_date and start_time are within the start_date/time and end_date/time in the history table. In this case, you need not care about end_date/time in your booking.

    Cyherus

    I am going to have to worry about end date some time..
    For example
    START_DATE END_DATE START_TIME END_TIME
    4/27/2004 4/28/2004 8 9
    4/27/2004 4/27/2004 2 3

    What do oyu mean by search for X ?? Does that mean null or all?

  5. #5
    Join Date
    Jun 2004
    Posts
    96
    X means the Item_Name.
    In short, you have to first look for the item being book, then decide if the start date/time falls between the history the item, OR end date/time falls between.
    Only when both start and end date/time are not within any date/time booked for X, then the reservation is successful.

    Cyherus

  6. #6
    Join Date
    May 2004
    Posts
    55
    Quote Originally Posted by Cyherus
    X means the Item_Name.
    In short, you have to first look for the item being book, then decide if the start date/time falls between the history the item, OR end date/time falls between.
    Only when both start and end date/time are not within any date/time booked for X, then the reservation is successful.

    Cyherus

    CYHERUS-- I am still a little confused.

    var = DLookup("[ID]", "HISTORY", "[ITEM_NAME] = "" & Forms!HISTORY!ITEM_NAME & "" ")


    If IsNull(var) Then GoTo Line Else var1 = DLookup("[ID]", "HISTORY", "[START_DATE] = #" & Forms!HISTORY!START_DATE & "# And [START_TIME]=#" & Forms!HISTORY!START_TIME & "# ")
    If IsNull(var1) Then GoTo Line Else MsgBox "record found, so you cannot make reservation"




    Line:
    MsgBox "record not found, so you can add and reservation will be successful"
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    What's the command for between "" and ""

    Please bear with me on this one.

    Thanks!!

  7. #7
    Join Date
    Jun 2004
    Posts
    96
    let me try to write out the whole search criteria..
    first of all, you must be careful with the " used.

    varX = DLookup("[ID]", "History", "([item] = """ & Me.txtitem & _
    """ AND [start date time] > #" & Me.txtstartdatetime & _
    "# AND [start date time] < #" & Me.txtenddatetime & "#) " & _
    "OR ([item] = """ & Me.txtitem & _
    """ AND [end date time] > #" & Me.txtstartdatetime & _
    "# AND [end date time] < #" & Me.txtenddatetime & "#) " & _
    "OR ([item] = """ & Me.txtitem & _
    """ AND [start date time] = #" & Me.txtstartdatetime & _
    "# AND [end date time] = #" & Me.txtenddatetime & "#)")

    note: I combined date and time, to avoid compare date and time separately when they can be combined and compared as 1 (date/time). And I also suggest that your history table should just have 2 fields, start_date_time and end_date_time..

    edit: me.txtstartdatetime, me.txtenddatetime, me.item are textboxes on the booking form. in order to not confuse you.

    Cyherus

Posting Permissions

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