SQL Problem

[pw2374]

I have run into a problem with the data reports, it appears to me that you can not refer to outside variables in the SQL Command in the Data Environment designer. Below is a sample of my code:

-------------------------------------------------------------------

"SELECT tblPatientData.*, tblLoan.*, `tblEquipmentCat`.`Description`, `tblEquipmentCat`.`ReplacementPrice` FROM tblPatientData INNER JOIN ((tblEquipmentCat INNER JOIN tblEquipment_Inven ON `tblEquipmentCat`.`Equipment_CatCode` = `tblEquipment_Inven`.`EquipmentCatCode`) INNER JOIN tblLoan ON `tblEquipment_Inven`.`EquipmentIDCode` = `tblLoan`.`EquipmentIDCode`) ON `tblPatientData`.`NHS_Number` = `tblLoan`.`NHS_Number` WHERE `tblloan`.`NHS_Number` = '" & ExpiredNHSNo & "'"

-------------------------------------------------------------------

The ExpiredNHSNo is the Variable that contains the expired NHS No of the patient. After checking this variable was reciving the data correctly (which it was) I ran the data report based on this SQL Command, yet nothing came up. The variable contained the patient ID I had sent to the data report (0000002).

When the reference to the variable (ExpiredNHSNo) is removed and replaced with a literal patient number ('00000002') the Data Report retrives the correct data and displays it in the report.

Is there a solution to this problem and if not is there any other way of passing a value to the SQL Command so it retrives the Patient Details I want.

[drekab]

The SQL shouldn't have a problem with the variables, but if you want to test that, you could dim a string then set the concatenate your variables into that string, then just pass the one string.

Dim sSQL as String

sSQL="SELECT * FROM [Table] WHERE [Table].[Field]='" & Text1.Text & "'"

rs.Open sSQL, conn


Hope that helps.