Recordset using string variable unsuccessful

[Gail]

Hi Guys
Am trying to set up an Access database for training
records. Have most of it set up and am on a tight
timeline to finish at present.

I had expected the following code to return all training
records for a particular person (PayRollNo obtained from a
combo box) However, the code is not returning any records.

Dim strPayRollNo As String
strPayRollNo = cboNameEntry

Set rectblTraining = CurrentDb.OpenRecordset("SELECT * " _
& "FROM tblTraining WHERE PayRollNo = 'strPayRollNo' ORDER
BY Date DESC")

It is something to do with my variable as this returns the
relevant data (for a particular PayRollNo) successfully

Set rectblTraining = CurrentDb.OpenRecordset("SELECT * " _
& "FROM tblTraining WHERE PayRollNo = '105744' ORDER BY
Date DESC")

In the past I have used the following code successfully,
utilising the variable lngOrderItemId. In that case
however the variable was a long integer and not a string.

Set rectblOrderItem = CurrentDb.OpenRecordset ("SELECT * "
_
& "FROM tblOrderItem WHERE OrderItemId = " &
lngOrderItemId)

Any help you can offer is appreciated.
Gail

 

[Graham Mandeno]

Hi Gail

The problem is that you are selecting the record(s) where PayRollNo matches
'strPayRollNo' (the literal string) rather than those where it matches the
*value* of strPayRollNo.

Try this:

Set rectblTraining = CurrentDb.OpenRecordset("SELECT * " _
& "FROM tblTraining WHERE PayRollNo = '" _
& strPayRollNo & "' ORDER BY Date DESC")

 

[Gail]

Thanks Graham
Works like a charm.
Gail

-----Original Message-----
Hi Gail

The problem is that you are selecting the record(s) where PayRollNo matches
'strPayRollNo' (the literal string) rather than those where it matches the
*value* of strPayRollNo.

Try this:

Set rectblTraining = CurrentDb.OpenRecordset("SELECT * " _
& "FROM tblTraining WHERE PayRollNo = '" _
& strPayRollNo & "' ORDER BY Date DESC")

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Guys
Am trying to set up an Access database for training
records. Have most of it set up and am on a tight
timeline to finish at present.

I had expected the following code to return all training
records for a particular person (PayRollNo obtained from a
combo box) However, the code is not returning any records.

Dim strPayRollNo As String
strPayRollNo = cboNameEntry

Set rectblTraining = CurrentDb.OpenRecordset("SELECT * " _
& "FROM tblTraining WHERE PayRollNo = 'strPayRollNo' ORDER
BY Date DESC")

It is something to do with my variable as this returns the
relevant data (for a particular PayRollNo) successfully

Set rectblTraining = CurrentDb.OpenRecordset("SELECT * " _
& "FROM tblTraining WHERE PayRollNo = '105744' ORDER BY
Date DESC")

In the past I have used the following code successfully,
utilising the variable lngOrderItemId. In that case
however the variable was a long integer and not a string.

Set rectblOrderItem = CurrentDb.OpenRecordset ("SELECT * "
_
& "FROM tblOrderItem WHERE OrderItemId = " &
lngOrderItemId)

Any help you can offer is appreciated.
Gail

.