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.....