Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Clash (A2000)

    If I have an item, Lets say "Item1" and this item is booked out from 13/11/2002 and due back 18/11/2002.

    If a user tried to select this item on & between these dates, how would I let the user know the item is already booked and kick him back to a selection form.

    I'm not sure on how to start this.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Clash (A2000)

    It depends on the way your dates are stored.

    If you have a table like this:

    <table border=1><td valign=bottom>ItemID</td><td valign=bottom>DateOut</td><td valign=bottom>DateBack</td><td valign=bottom>item1</td><td align=right valign=bottom>13-11-2002</td><td align=right valign=bottom>18-11-2002</td><td valign=bottom>item1</td><td align=right valign=bottom>22-11-2002</td><td align=right valign=bottom>24-11-2002</td><td valign=bottom>item2</td><td align=right valign=bottom>12-11-2002</td><td align=right valign=bottom>13-11-2002</td><td valign=bottom>item2</td><td align=right valign=bottom>16-11-2002</td><td align=right valign=bottom>18-11-2002</td></table>
    you can find out if there are records for ItemID = item1 with SelectedDate >= DateOut and SelectedDate <= DateBack, where SelectedDate is the date entered by the user. You could use DCount for this, or open a recordset and see if the RecordCount is > 0. For example:

    If DCount("*", "tblBookings", "ItemID = Forms!frmWhatever!txtItemID And DateOut <= Forms!frmWhatever!txtSelectedDate And DateBack >= Forms!frmWhatever!txtSelectedDate") > 0 Then
    MsBox "Already booked!"
    ...
    End If

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Clash (A2000)

    Thanks Hans.
    I'm sure that will give me a start.

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Clash (A2000)

    Here's what I have so far which throws a syntax error which I cannot see why.
    Also it doesn't check between ( >= & =) FromDate & ThruDate, only as dates selected .
    I'm not sure where to put them.


    Dim CourtesySelect As Integer
    CourtesySelect = DCount("*", "qrySelectCourtesy", "unitid=" & Me.UnitID & "'and fromdate=#" & _
    Me.FromDate & "# thrudate=#" & Me.ThruDate & "#")
    If CourtesySelect > 0 Then
    ' Warn the user
    MsgBox "This Vehicle Is Already Booked"
    ' Cancel the update
    Cancel = True
    End If
    End Sub

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Clash (A2000)

    Sorry Hans

    This just ain't working for me.
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Clash (A2000)

    Hi Dave,

    There were some problems left:

    1) Apparently, the less-than and greater-than signs became HTML codes when you copied an pasted my code into your database.
    2) BuildCriteria seems to expect a reference to the Microsoft DAO ... Object Library.
    3) I had left an error in the last bit of code I posted - the From and Thru dates should be "crossed" when comparing them. I will correct my previous reply.

    I have attached the modified database.
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Date Clash (A2000)

    <P ID="edit" class=small>(Edited by HansV on 15-Nov-02 17:16. there was a mistake in the last bit of code, which has been corrected now.)</P>Dave,

    There are several problems with<pre>CourtesySelect = DCount("*", "qrySelectCourtesy", "unitid=" & _
    Me.UnitID & "'and fromdate=#" & _
    Me.FromDate & "# thrudate=#" & Me.ThruDate & "#")</pre>

    1) There is a superfluous single quote ' before "and fromdate".
    2) There is no "and" before thrudate.
    3) If you have a FromDate *and* ThruDate in the table and on the form, you need to use >= and <= in a special form (not =).
    4) If your PC uses dd-mm-yy format or a variation thereof, the SQL will come out wrong, because SQL expects US date format mm/dd/yy.

    Try<pre>CourtesySelect = DCount("*", "qrySelectCourtesy", _
    "UnitID=Forms!frmWhatever!UnitID And " & _
    "FromDate<=Forms!frmWhatever!ThruDate And " & _
    "ThruDate>=Forms!frmWhatever!FromDate")</pre>

    Replace frmWhatever by the name of the form you are using. If that doesn't work, try<pre>CourtesySelect = DCount("*", "qrySelectCourtesy", _
    "UnitID=" & Me!UnitID & " And " & _
    BuildCriteria("FromDate", dbDate, "<=" & Me!ThruDate) & " And " & _
    BuildCriteria("ThruDate", dbDate, ">=" & Me!FromDate))</pre>

    BuildCriteria converts dates to US format.

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Clash (A2000)

    Hans
    I checked it before re-plying.
    Thanks.
    it works fine.

Posting Permissions

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