strSQL code

L

Larry Salvucci

I'm having a problem getting this code to work. I keep getting a data type
mismatch error. What I'm trying to do is create a strSQL to only select
records that equal the employeeID. Where am I going wrong?

Function getCalendarData() As Boolean
Dim rs As DAO.Recordset
Dim strDate As String
Dim strCode As String
Dim i As Integer
Dim strSQL As String
Dim strEmployee As String

strEmployee = [Forms]![frmEmployeeMain]![ID]

strSQL = "SELECT t_employeeAttendance.*, t_employeeAttendance.employeeID
FROM t_employeeAttendance WHERE t_employeeAttendance.employeeID = " &
strEmployee & ";"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set colCalendarDates = New Collection
With rs
If (Not .BOF) Or (Not .EOF) Then
.MoveLast
.MoveFirst
End If
If .RecordCount > 0 Then
For i = 1 To .RecordCount
strDate = .Fields("attendanceDate")
strCode = .Fields("statusCode")
colCalendarDates.Add strCode, strDate
.MoveNext
Next i
End If
.Close
End With
'// return date collection
Set rs = Nothing
End Function
 
K

Ken Snell \(MVP\)

employeeID is a text field, right? So you need to delimit the value being
matched with ' characters:

strSQL = "SELECT t_employeeAttendance.*, t_employeeAttendance.employeeID
FROM t_employeeAttendance WHERE t_employeeAttendance.employeeID = '" &
strEmployee & "';"
 
L

Larry Salvucci

Yes you are correct. It's working now. Thank you.

Ken Snell (MVP) said:
employeeID is a text field, right? So you need to delimit the value being
matched with ' characters:

strSQL = "SELECT t_employeeAttendance.*, t_employeeAttendance.employeeID
FROM t_employeeAttendance WHERE t_employeeAttendance.employeeID = '" &
strEmployee & "';"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Larry Salvucci said:
I'm having a problem getting this code to work. I keep getting a data type
mismatch error. What I'm trying to do is create a strSQL to only select
records that equal the employeeID. Where am I going wrong?

Function getCalendarData() As Boolean
Dim rs As DAO.Recordset
Dim strDate As String
Dim strCode As String
Dim i As Integer
Dim strSQL As String
Dim strEmployee As String

strEmployee = [Forms]![frmEmployeeMain]![ID]

strSQL = "SELECT t_employeeAttendance.*,
t_employeeAttendance.employeeID
FROM t_employeeAttendance WHERE t_employeeAttendance.employeeID = " &
strEmployee & ";"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set colCalendarDates = New Collection
With rs
If (Not .BOF) Or (Not .EOF) Then
.MoveLast
.MoveFirst
End If
If .RecordCount > 0 Then
For i = 1 To .RecordCount
strDate = .Fields("attendanceDate")
strCode = .Fields("statusCode")
colCalendarDates.Add strCode, strDate
.MoveNext
Next i
End If
.Close
End With
'// return date collection
Set rs = Nothing
End Function
Click to expand...
Click to expand...
 
L

Larry Salvucci

Ken,
What if the employeeID was a number instead of text? How would the code be
written then? Do I just remove the ' character?

Ken Snell (MVP) said:
employeeID is a text field, right? So you need to delimit the value being
matched with ' characters:

strSQL = "SELECT t_employeeAttendance.*, t_employeeAttendance.employeeID
FROM t_employeeAttendance WHERE t_employeeAttendance.employeeID = '" &
strEmployee & "';"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Larry Salvucci said:
I'm having a problem getting this code to work. I keep getting a data type
mismatch error. What I'm trying to do is create a strSQL to only select
records that equal the employeeID. Where am I going wrong?

Function getCalendarData() As Boolean
Dim rs As DAO.Recordset
Dim strDate As String
Dim strCode As String
Dim i As Integer
Dim strSQL As String
Dim strEmployee As String

strEmployee = [Forms]![frmEmployeeMain]![ID]

strSQL = "SELECT t_employeeAttendance.*,
t_employeeAttendance.employeeID
FROM t_employeeAttendance WHERE t_employeeAttendance.employeeID = " &
strEmployee & ";"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set colCalendarDates = New Collection
With rs
If (Not .BOF) Or (Not .EOF) Then
.MoveLast
.MoveFirst
End If
If .RecordCount > 0 Then
For i = 1 To .RecordCount
strDate = .Fields("attendanceDate")
strCode = .Fields("statusCode")
colCalendarDates.Add strCode, strDate
.MoveNext
Next i
End If
.Close
End With
'// return date collection
Set rs = Nothing
End Function
Click to expand...
Click to expand...
 
P

Pieter Wijnen

yes, you wouldn't need the apostrophy

pieter

Larry Salvucci said:
Ken,
What if the employeeID was a number instead of text? How would the code be
written then? Do I just remove the ' character?

Ken Snell (MVP) said:
employeeID is a text field, right? So you need to delimit the value being
matched with ' characters:

strSQL = "SELECT t_employeeAttendance.*,
t_employeeAttendance.employeeID
FROM t_employeeAttendance WHERE t_employeeAttendance.employeeID = '" &
strEmployee & "';"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message
I'm having a problem getting this code to work. I keep getting a data
type
mismatch error. What I'm trying to do is create a strSQL to only select
records that equal the employeeID. Where am I going wrong?

Function getCalendarData() As Boolean
Dim rs As DAO.Recordset
Dim strDate As String
Dim strCode As String
Dim i As Integer
Dim strSQL As String
Dim strEmployee As String

strEmployee = [Forms]![frmEmployeeMain]![ID]

strSQL = "SELECT t_employeeAttendance.*,
t_employeeAttendance.employeeID
FROM t_employeeAttendance WHERE t_employeeAttendance.employeeID = " &
strEmployee & ";"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set colCalendarDates = New Collection
With rs
If (Not .BOF) Or (Not .EOF) Then
.MoveLast
.MoveFirst
End If
If .RecordCount > 0 Then
For i = 1 To .RecordCount
strDate = .Fields("attendanceDate")
strCode = .Fields("statusCode")
colCalendarDates.Add strCode, strDate
.MoveNext
Next i
End If
.Close
End With
'// return date collection
Set rs = Nothing
End Function
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell \(MVP\)

Yes, in that case your original code would have worked fine.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


Larry Salvucci said:
Ken,
What if the employeeID was a number instead of text? How would the code be
written then? Do I just remove the ' character?

Ken Snell (MVP) said:
employeeID is a text field, right? So you need to delimit the value being
matched with ' characters:

strSQL = "SELECT t_employeeAttendance.*,
t_employeeAttendance.employeeID
FROM t_employeeAttendance WHERE t_employeeAttendance.employeeID = '" &
strEmployee & "';"

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


message
I'm having a problem getting this code to work. I keep getting a data
type
mismatch error. What I'm trying to do is create a strSQL to only select
records that equal the employeeID. Where am I going wrong?

Function getCalendarData() As Boolean
Dim rs As DAO.Recordset
Dim strDate As String
Dim strCode As String
Dim i As Integer
Dim strSQL As String
Dim strEmployee As String

strEmployee = [Forms]![frmEmployeeMain]![ID]

strSQL = "SELECT t_employeeAttendance.*,
t_employeeAttendance.employeeID
FROM t_employeeAttendance WHERE t_employeeAttendance.employeeID = " &
strEmployee & ";"

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set colCalendarDates = New Collection
With rs
If (Not .BOF) Or (Not .EOF) Then
.MoveLast
.MoveFirst
End If
If .RecordCount > 0 Then
For i = 1 To .RecordCount
strDate = .Fields("attendanceDate")
strCode = .Fields("statusCode")
colCalendarDates.Add strCode, strDate
.MoveNext
Next i
End If
.Close
End With
'// return date collection
Set rs = Nothing
End Function
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

populate listbox form database table 0
VBA works in orignal but fails in copy of database 1
dlookup and dao.recordset 4
Query shows no results 1
How to reference a field of a recordset with a variable 4
export vba not working 0
Run-Time error 3061 3
Continuation of problem generating Sales Order Number 1

Top