DLookup problem

[Paul Netzel]

Good Day; thanks in advance for any assistance.
I am designing a scheduling database and am trying to use Dlookup in order to find out if a particular date on the form is within a pay period. I then want to display the pay period start date in the unbound field on a form that is designed to add a recordset to my main table. I keep getting a run error 13 and cannot figure out what is incorrect. I have used the # for dates and several other variations in the coding with the same result. I am trying to use this pay period start date in order to filter employees schedules processing of their request when the final schedule is approved for posting, which leads to another obstacle I am experiencing but will not address on this thread. Below is the coding for this form, everything works except calculatePPDate sub. I can provide more detail if needed. I have look through many posts on several different sites and have not been able to find an answer that addresses this proble. Thanks again in advance. Paul

Sub CalculatePPDate()
Dim txtPPDate As Date
txtPPDate = DLookup("PP Start", "PayPeriod", "[txtStartDate]=> [Tables]![PayPeriod]![PP Start]" And "[txtStartDate]=< [Tables]![PayPeriod]![PP End]")
End Sub



'Create a sub to calculate the end date
Sub CalculateEndDate()
txtEndDate = txtStartDate + txtDays
End Sub

Sub CalculateTotalDays()
txtTotDays = (txtEndDate - txtStartDate) + 1
End Sub

Private Sub txtDays_LostFocus()
CalculateEndDate
CalculateTotalDays
CalculatePPDate
End Sub

'Call this code in the Updated event and the StartDate After_Update event
Private Sub txtStartDate_AfterUpdate()
CalculateEndDate
CalculateTotalDays
CalculatePPDate
End Sub

'Call this code in the Updated event and the StartDate Lost_Focus event
Private Sub txtStartDate_LostFocus()
CalculateEndDate
CalculateTotalDays
CalculatePPDate
End Sub


'Create a Recordset
Private Sub cmdCreate_Click()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("CalendarT")

'Loop through the no of days
For i = 0 To txtDays

rs.AddNew

rs!Staff = txtStaff
rs!DateEntered = txtDateEntered
rs!EnteredBy = txtEnteredBy
rs!StartRequestDate = txtStartDate + i
rs!AdditionalDaysRequested = txtDays
rs!TotalDaysRequested = txtTotDays
rs!EndRequestDate = txtEndDate
rs!Request = txtDescription
rs!RequestNotes = txtNotes
rs!Approved = txtApproved
rs!DateApproved = txtDateApproved
rs!Changed = txtChanged
rs!ChangeDate = txtChangeDate
rs!ChangedRequest = txtChangedRequest
rs!PPDate = txtPPDate
rs.Update

Next i

' Clean Up and Close
rs.Close
db.Close

Set rs = Nothing
Set db = Nothing

If MsgBox("Do you want to create another request?", vbYesNo + vbQuestion, "Create Request") = vbYes Then
CalendarME
Else
StaffRequestForm
End If


End Sub
Public Sub Msgboxcreate()

'If MsgBox("Do you want to create another request?", vbYesNo + vbQuestion, "Create Request") = vbYes Then
'CalendarME2
'Else
'StaffRequestForm2

'End If

End Sub

[RuralGuy]

The AND should be *inside* the quotes as well.
"[txtStartDate]=> [Tables]![PayPeriod]![PP Start] And [txtStartDate]=< [Tables]![PayPeriod]![PP End]")

[June7]

Variables must be outside the quote marks and concatenated into the statement - otherwise you have the literal text "txtStartDate" and not the value of txtStartDate. I presume txtStartDate is an input parameter - is this a textbox on form and is txtPPDate also textbox on form? Also, need [] when names have spaces or special characters/punctuation (underscore is exception) - advise avoiding their use. Also, need # delimiter for date input. Try BETWEEN AND operator, it is inclusive for the begin/end range values. Don't use the Tables collection reference and no need to prefix with tablename.

Me.txtPPDate = DLookup("[PP Start]", "PayPeriod", "#" & Me.txtStartDate & "# BETWEEN [PP Start] AND [PP End]")

[Paul Netzel]

Thanks Rural Guy for your suggestion, unfortunately this solution did not work, however, the solution posted by June 7 did work. I appreciate your efforts and assistance. Paul

[RuralGuy]

Glad to hear you got it resolved.