Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    awhittle23 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Location
    England
    Posts
    17

    Calculating Date Difference

    Hi,

    I have a car park booking system.


    In this system, when taking a customers booking, are two fields. Departure_Date and Return_date. There is also a third Field which is number of days.

    What i need is, when you select The departure and return dates, i need the Number of days field to auto populate with the number of days the customer will be gone for.

    I've tried to find info on how this can be done and have seen a Datediff function. DateDiff("d",[Departure_date],[Return_date]).. However, when i use this function is does not bring up any information.

    Can anyone help me get this number of days field to auto update with the difference between my two selected dates.

    Thanks in advance

    Antony

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    If the field has a null, you will get a null.
    if null, use Date()

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Are you using a form the enter the two dates or are you entering the dates directly into the table?



    Should be using a form. In the control source property for the "number of days" control, you can enter
    Code:
    =DateDiff("d",[Departure_date],[Return_date])
    This would mean the number of days is not stored in the table. Since you can always calculate the number of days from the fields [Departure_date] & [Return_date], this is not a big deal.

    As a rule, shouldn't really store the results of calculations.

    ---------------------------------

    You can use a query as the record source for the form and have a column in the query
    Code:
    NumOfDays: DateDiff("d",[Departure_date],[Return_date])
    Again, this calculation is not stored.

    ---------------------------------

    You can have code in the after update event of the controls for [Departure_date] & [Return_date] to push the result into the control for the number of days.
    If the control names are "tbDeparture_date", "tbReturn_date" and "tbNumOfDays", the code would look like
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub tbDeparture_date_AfterUpdate()
       Me.NumberOfDays = 0
    
        If Not IsNull(Me.tbDeparture_date) And Not IsNull(Me.Return_date) Then
            Me.NumberOfDays = DateDiff("d", [Departure_date], [Return_date])
        End If
    End Sub
    
    
    Private Sub Return_date_AfterUpdate()
        Me.NumberOfDays = 0
    
        If Not IsNull(Me.tbDeparture_date) And Not IsNull(Me.Return_date) Then
            Me.NumberOfDays = DateDiff("d", [Departure_date], [Return_date])
        End If
    End Sub
    The results of the calculation would be stored if the control is bound to a field.

  4. #4
    awhittle23 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Location
    England
    Posts
    17
    Steve, That VBA Code Worked a treat amigo..

    Maybe you can help with stage two of my database..

    I have a table named MG_Prices and one name PR_Prices. Both tables have Month and 1-30 days in it. For each day there is a price.
    January 1-30
    February 1-30
    etc

    What it needs next is...

    Once i select a meet and greet (or park and ride) from drop down menu under control name Service. I need a field named price to do a calculation and store the price.
    Price will check for the service selected (in this case meet and greet) and the number_of_days which we calculated earlier and then look at departure_date month and then check the mg_price table to locate the month, followed by the number of days 1-30 then update the price field.


    Do you think this is possible??
    and do you know how i could achieve this?

    Cheers in advance for your help if you can help with my second query or not..

    Antony.

  5. #5
    awhittle23 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Location
    England
    Posts
    17
    Steve, That VBA Code Worked a treat amigo..

    Maybe you can help with stage two of my database..

    I have a table named MG_Prices and one name PR_Prices. Both tables have Month and 1-30 days in it. For each day there is a price.
    January 1-30
    February 1-30
    etc

    What it needs next is...

    Once i select a meet and greet (or park and ride) from drop down menu under control name Service. I need a field named price to do a calculation and store the price.
    Price will check for the service selected (in this case meet and greet) and the number_of_days which we calculated earlier and then look at departure_date month and then check the mg_price table to locate the month, followed by the number of days 1-30 then update the price field.


    Do you think this is possible??
    and do you know how i could achieve this?

    Cheers in advance for your help if you can help with my second query or not..

    Antony.

    Quote Originally Posted by ssanfu View Post
    Are you using a form the enter the two dates or are you entering the dates directly into the table?



    Should be using a form. In the control source property for the "number of days" control, you can enter
    Code:
    =DateDiff("d",[Departure_date],[Return_date])
    This would mean the number of days is not stored in the table. Since you can always calculate the number of days from the fields [Departure_date] & [Return_date], this is not a big deal.

    As a rule, shouldn't really store the results of calculations.

    ---------------------------------

    You can use a query as the record source for the form and have a column in the query
    Code:
    NumOfDays: DateDiff("d",[Departure_date],[Return_date])
    Again, this calculation is not stored.

    ---------------------------------

    You can have code in the after update event of the controls for [Departure_date] & [Return_date] to push the result into the control for the number of days.
    If the control names are "tbDeparture_date", "tbReturn_date" and "tbNumOfDays", the code would look like
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub tbDeparture_date_AfterUpdate()
       Me.NumberOfDays = 0
    
        If Not IsNull(Me.tbDeparture_date) And Not IsNull(Me.Return_date) Then
            Me.NumberOfDays = DateDiff("d", [Departure_date], [Return_date])
        End If
    End Sub
    
    
    Private Sub Return_date_AfterUpdate()
        Me.NumberOfDays = 0
    
        If Not IsNull(Me.tbDeparture_date) And Not IsNull(Me.Return_date) Then
            Me.NumberOfDays = DateDiff("d", [Departure_date], [Return_date])
        End If
    End Sub
    The results of the calculation would be stored if the control is bound to a field.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Sure, it is possible.

    I have a table named MG_Prices and one name PR_Prices. Both tables have Month and 1-30 days in it. For each day there is a price.
    Why do you have 2 tables for prices? Better would be 1 table with an additional field maybe named "ServiceType" that would have value of either "MG_Price"or "PR_Price".

    What does "1-30" mean?

  7. #7
    awhittle23 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Location
    England
    Posts
    17
    The two price lists work differently.

    Meet and greet you can book per day so thats why we do 1-30 days. day 1 will be £25 and for 2 days may be £26 and so on. so if you want 30 days parking in June it will be £120
    Park and Ride works differently.
    We dont have 1-30 days of different prices. its an upto figure. So anything upto 8 days is a price and then anything upto 15 days and anything upto 22 days and finally anything upto 30 days. This then has an on peak / off peak/ cost.

    I havn't quite worked out how to get the system to check the times and see if it relays as an on/off peak then do the calculation. maybe create a table of 24 hours and with an ion/off peak column and then to link it that way.

    Currently all i need to do now is check the service, currently meet and greet in the service field. then to check month of departure and then to find thre number_of_days fielnd and link it up to the number of days in the price to update my price field..

    hope that makes sense.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I still think one table is the way to proceed, but..... it is your dB.
    More questions....


    --------------------------------
    Meet and greet you can book per day so thats why we do 1-30 days. day 1 will be £25 and for 2 days may be £26 and so on. so if you want 30 days parking in June it will be £120
    So do you have 365 records, 1 for each day or just 30 records for 30 days?
    You had
    January 1-30
    February 1-30
    etc
    Jan has 31 days???
    Feb has 28 (usually)???


    What about having a Departure_date of June 5,2016 and a Return_date of July 20,2016? That is 45 days. How do you calc the cost?


    --------------------------------
    Park and Ride works differently
    So there is a 1 week rate, a 2 week rate, a 3 week rate and a 4 week rate? 4 records?

    This then has an on peak / off peak/ cost
    Does this relate to the month?
    (Here our peak time May - Sept.........aka Tourist season. Prices for rental cars/ hotels go way up).


    --------------------------------
    Currently all i need to do now is check the service, currently meet and greet in the service field. then to check month of departure and then to find thre number_of_days fielnd and link it up to the number of days in the price to update my price field.
    Exactly how to do it using VBA, but need to add checks to ensure controls are not null.

  9. #9
    awhittle23 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Location
    England
    Posts
    17
    --------------------------------

    So do you have 365 records, 1 for each day or just 30 records for 30 days?
    You had

    Jan has 31 days???
    Feb has 28 (usually)???


    What about having a Departure_date of June 5,2016 and a Return_date of July 20,2016? That is 45 days. How do you calc the cost?


    ************** The reason its only 30 is because thats the maximum stay. if a customer needs to stay longer then price is custom entry.
    so i suppose there will be a check to say more than 30 then enter manual price. the main thing tro this is when they book in june 5th it will check upto 30 days booking and offer that price. does matter that 30 days will run into july we stick with the first months prices.

    total number of records is 360.
    --------------------------------

    So there is a 1 week rate, a 2 week rate, a 3 week rate and a 4 week rate? 4 records? ******* yes this is correct.


    Does this relate to the month? ***** time of day
    (Here our peak time May - Sept.........aka Tourist season. Prices for rental cars/ hotels go way up).

    ********************* The on peak and off peak is time based. so between 07:00 - 19:00 when a car arrives will be on peak. anything else will be off peak. Although we need the ability to change whats classed as on and off peak hours through a 24 hour period. Which is why i will create a table with 24 hours and either on or off peak besides those hours.
    That way when we do the park and ride checks it will check arrival time rather than departure date as meet and greet does.

    So there will be 4 records 1 for each week. with two prices for each record, on peak and off peak.
    --------------------------------

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You should probably do this on a COPY of your dB.... just in case.

    The code is mostly air code because I don't know your form name, control names or table structures. You will have to make changes to to code, changing control names and field names to have this run without errors.

    On the form that has the controls for "Departure_Date" and "Return_date", add a button and name it "btnCalcPrice". Then open the module for the form and paste in the following:
    Code:
    Option Compare Database '<< should be at the top of every code module
    Option Explicit         '<< should be at the top of every code module
    
    ' Purpose:
    'Currently all i need to do now is check the service,
    'currently meet and greet in the service field.
    'then to check month of departure and
    'then to find thre number_of_days fielnd and
    'link it up to the number of days in the price to update my price field..
    '
    'Date: 6/15/2016
    
    Private Sub btnCalcPrice_Click()
        On Error GoTo btnCalcPrice_Click_Err
    
        Dim r As DAO.Recordset
    
        Dim sSQL As String
        Dim vServiceType As String
        Dim vDepMonth As String
        Dim sWHERE As String
        Dim vNumOfDays As Integer
        Dim vStayprice As Double
    
    
        ' get service type - meet and greet or park and ride
        If IsNull(Me.ServiceType) Then
            MsgBox "Missing Service Type"
            Exit Sub
        Else
            vServiceType = Me.ServiceType   '  MG or PR
        End If
    
        'get departure month
        If IsNull(Me.departure_date) Then
            MsgBox "Missing departure date"
            Exit Sub
        Else
            vDepMonth = MonthName(Month(Me.departure_date))
        End If
    
        'get length of stay
        If IsNull(Me.NumOfDays) Then
            MsgBox "Number of days missing"
            Exit Sub
        Else
            vNumOfDays = Me.NumOfDays
        End If
    
        'default price
        vStayprice = 0
    
        Select Case vServiceType
            Case "MG"
                sSQL = "SELECT StayPrice FROM MG_Prices WHERE StayMonth = '" & vDepMonth & "' AND StayDay = " & vNumOfDays
                Debug.Print sSQL
                Set r = CurrentDb.OpenRecordset(sSQL)
                If Not r.BOF And Not r.EOF Then
                    vStayprice = r!Stayprice
                End If
                r.Close
    
            Case "PR"
    
                Select Case vNumOfDays
                    Case Is < 8
                        sWHERE = "NumOfDays = 7"
                    Case Is < 15
                        sWHERE = "NumOfDays = 14"
                    Case Is < 22
                        sWHERE = "NumOfDays = 21"
                    Case Else
                        sWHERE = "NumOfDays = 30"
                End Select
    
                sSQL = "SELECT Stayprice FROM PR_Prices WHERE " & sWHERE
                Set r = CurrentDb.OpenRecordset(sSQL)
                If Not r.BOF And Not r.EOF Then
                    vStayprice = r!Stayprice
                End If
                r.Close
    
            Case Else
                MsgBox "Error"
                Exit Sub
        End Select
    
        'Stayprice is the name of the text box control on the form that has the price
        Me.Stayprice = vStayprice
        
    btnCalcPrice_Click_Exit:
        'clean up
        Set r = Nothing
    
        Exit Sub
    
    
    btnCalcPrice_Click_Err:
        MsgBox Error$
        Resume btnCalcPrice_Click_Exit
    
    End Sub
    Once this is working, you can work on peak time prices.....

  11. #11
    awhittle23 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Location
    England
    Posts
    17
    Hi,

    I mage the changes to the code and have made them on your code here so you can what form controls are different to mine.
    So far i had a couple of error but managed to work them out.. I no longer get any errors but it does not update the price on the system.

    i made changes to my table structure to match your naming convention so it was easier to work and it didn't cause any disruption to the rest of my database.

    Any Ideas?? My Guess is there is something noit right with this part of the code

    Select Case vServiceType
    Case "Meet & Greet" 'Leaving this at MG brings up the msg box Error. I changed to match my service name Meet & Greet and no more error.
    sSQL = "SELECT StayPrice FROM MG_Price WHERE StayMonth = '" & vDepMonth & "' AND StayDay = " & vNumOfDays
    Debug.Print sSQL
    Set r = CurrentDb.OpenRecordset(sSQL)
    If Not r.BOF And Not r.EOF Then
    vStayprice = r!Stayprice
    End If
    r.Close

    Really Appreciate your help on this too.. ;-)

    Code:
    Option Compare Database '<< should be at the top of every code module
    Option Explicit         '<< should be at the top of every code module
    
    ' Purpose:
    'Currently all i need to do now is check the service,
    'currently meet and greet in the service field.
    'then to check month of departure and
    'then to find thre number_of_days fielnd and
    'link it up to the number of days in the price to update my price field..
    '
    'Date: 6/15/2016
    
    Private Sub btnCalcPrice_Click()
        On Error GoTo btnCalcPrice_Click_Err
    
        Dim r As DAO.Recordset
    
        Dim sSQL As String
        Dim vServiceType As String
        Dim vDepMonth As String
        Dim sWHERE As String
        Dim vNumOfDays As Integer
        Dim vStayprice As Double
    
    
        ' get service type - meet and greet or park and ride
        If IsNull(Me.Service) Then                                    'me.service instead of me.servicetype
            MsgBox "Missing Service Type"
            Exit Sub
        Else
            vServiceType = Me.Service   '  MG or PR
        End If
    
        'get departure month
        If IsNull(Me.departure_date) Then
            MsgBox "Missing departure date"
            Exit Sub
        Else
            vDepMonth = MonthName(Month(Me.departure_date))
        End If
    
        'get length of stay
        If IsNull(Me.Number_Of_Days) Then                  ' Me.Number_Of_Days instead of me.numofdays
            MsgBox "Number of days missing"
            Exit Sub
        Else
            vNumOfDays = Me.Number_Of_Days
        End If
    
        'default price
        vStayprice = 0
    
        Select Case vServiceType
            Case "MG" 'Leaving this at MG brings up the msg box Error.  I changed to match my service name Meet & Greet and no more error
                sSQL = "SELECT StayPrice FROM MG_Price WHERE StayMonth = '" & vDepMonth & "' AND StayDay = " & vNumOfDays
                Debug.Print sSQL
                Set r = CurrentDb.OpenRecordset(sSQL)
                If Not r.BOF And Not r.EOF Then
                    vStayprice = r!Stayprice
                End If
                r.Close
    
            Case "PR"  ' also changeds to Park & Ride
    
                Select Case vNumOfDays
                    Case Is < 9
                        sWHERE = "NumOfDays = 8"
                    Case Is < 16
                        sWHERE = "NumOfDays = 15"
                    Case Is < 23
                        sWHERE = "NumOfDays = 22"
                    Case Else
                        sWHERE = "NumOfDays = 30"
                End Select
    
                sSQL = "SELECT Stayprice FROM PR_Prices WHERE " & sWHERE
                Set r = CurrentDb.OpenRecordset(sSQL)
                If Not r.BOF And Not r.EOF Then
                    vStayprice = r!Stayprice
                End If
                r.Close
    
            Case Else
                MsgBox "Error"
                Exit Sub
        End Select
    
        'Stayprice is the name of the text box control on the form that has the price
        Me.Stayprice = vStayprice
        
    btnCalcPrice_Click_Exit:
        'clean up
        Set r = Nothing
    
        Exit Sub
    
    
    btnCalcPrice_Click_Err:
        MsgBox Error$
        Resume btnCalcPrice_Click_Exit
    
    End Sub
    Once this is working, you can work on peak time prices.....[/QUOTE]

  12. #12
    awhittle23 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Location
    England
    Posts
    17
    Steve... Just want to say a massive thank you amigo..

    Went through the code again and found it wouldn't accept the me.stayprice and kept bringing up a compile error.. i had changed from price.. so i just put it back and its all working now..
    Just want to say a massive thank you..

    I will now try to work out the Park and Ride and peak times etc...

    Cheers buddy

    I may need to call on your expertise again if that's okay??

  13. #13
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I may need to call on your expertise again if that's okay??
    No problem..... Happy to help..

  14. #14
    awhittle23 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2016
    Location
    England
    Posts
    17

    Peak Time Help

    Quote Originally Posted by ssanfu View Post
    No problem..... Happy to help..

    Steve, hope you don't mind me asking for more help.. ;-)

    I spoke with the boss and the peak times should be an easy thing to do, rather than having a time based peak time we will have an enable check box control.

    I have created a table called peak, with a control of peak which is a yes/no.
    This is linked to a form named PeakEnabled.
    This form has a check box to enable or disable peak times.

    I have also added a peakprice to the meet and greet service. so i need the code to check if the peak is enabled and offer the peak prices if it is. if not it can use the stayprice as currently doing in your code.

    Now for the Park and Ride Service

    Table is called PR_Price - This has 4 records

    Numofdays 8 , 15, 22, 30
    stayprice £21, £30, £39, £48
    Peak Price £31, £40, £49, £58

    Hope this is enough info to help with the code.

    And thanks again..

    Antony..

  15. #15
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Won't be hard, but I don't understand how the form PeakEnabled, control Peak and table Peak relate to tables MG_price and PR_price. What is the structure of Peak?

    Any chance you can post your dB? If you want to keep the dB private, maybe you have a dropbox acct and will PM me the link?

    Don't need real people names/records - a record for Mickey Mouse (MG) and a record for Donald duck (PR) would do.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-07-2014, 07:04 AM
  2. Replies: 3
    Last Post: 04-24-2013, 10:44 AM
  3. Replies: 1
    Last Post: 02-12-2013, 03:48 AM
  4. Replies: 10
    Last Post: 12-27-2011, 01:20 PM
  5. Replies: 1
    Last Post: 12-14-2011, 05:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums