Recordset using string variable unsuccessful

  • Thread starter Thread starter Gail
  • Start date Start date
G

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
 
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")
 
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
Click to expand...


.
Click to expand...
 
Back
Top