Error selecting specific record 1

[paulminne]

hi,

I have a form with a button on it. On the click event of the button, an input box appears where a date is inputted by the user.

I am trying to match a specific record from a query where the "JobDate" entered is the same as the JobDate from a record in the query. However I am getting an error stating 'no value given for one or more of the required parameters'.

This is the code:

**********************************
Private Sub cmdWeekly_Click()

On Error GoTo Err_cmdWeekly

Dim strMsg As String
Dim strInput As String
Dim db As Database
Dim rs As ADODB.Recordset
Dim strSQL As String

Set rs = New ADODB.Recordset

rs.ActiveConnection = CurrentProject.Connection

stDocName = "WeeklyJobs"

strMsg = "Please enter the week commencement date! (dd-mmm-yyyy)"
strInput = InputBox(prompt:=strMsg, Title:="Gammasonics - Compliance Database", _
xPos:=2000, yPos:=2000)
stLinkCriteria = "[JobDate]=" & strInput

strSQL = "SELECT * FROM JobsBooked2 WHERE Job.JobID = " & _
strInput

Set db = CurrentDb

'Open the recordset
rs.Open strSQL

If rs.EOF Then
MsgBox "There are no jobs for this date"
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_cmdWeekly:
Exit Sub

Err_cmdWeekly:
MsgBox Err.Description
Resume Exit_cmdWeekly

End Sub

***************************************

Any help as to why I am getting this error is appreciated.

- Paul

 

[paulminne]

in the line:
strSQL = "SELECT * FROM JobsBooked2 WHERE Job.JobID = " & _
strInput

JobID should of been JobDate.

regards,

- Paul

 

[JohnLowell]

You need to change your SQL statement to this:

stLinkCriteria = "[JobDate]= #" & strInput & "#"

strSQL = "SELECT * FROM JobsBooked2 WHERE " & stLinkCriteria

 

[paulminne]

Thankyou so much for you help...this worked great.

You cant comprehend how much frustration this little query was giving me..

Regards,

- Paul