Using SQL with Set Command in VB

[Ekhart]

I am trying to take data from an open form and link it to a record on a table using OpenRecordset. Since I have similar code elsewhere I have to assume it has something to do with pulling the data from the form unless I am missing something else Or the fact that this one uses a JOIN.

Set rsConsults = dbs.OpenRecordset("SELECT Scheduler_Sheet.ConsultNumber, Scheduler_Sheet.LastName, Scheduler_Sheet.FirstName, Scheduler_Sheet.SSN, Scheduler_Sheet.Clinic, tblConsultAlias.[Service Name], Scheduler_Sheet.Program FROM Scheduler_Sheet INNER JOIN tblConsultAlias ON Scheduler_Sheet.Clinic = tblConsultAlias.Clinic WHERE (Scheduler_Sheet.[ConsultNumber]) = '" & Forms!frmToChart!ConsultNumber & "'")

I get an error 'Datatype mismatch in criteria expression'.
I also tried assigning the data from the form to a variable and got the same error...

Dim FromForm As String
FromForm = Forms!frmToChart.Form!ConsultNumber
Set rsConsults = dbs.OpenRecordset("SELECT Scheduler_Sheet.ConsultNumber, Scheduler_Sheet.LastName, Scheduler_Sheet.FirstName, Scheduler_Sheet.SSN, Scheduler_Sheet.Clinic, tblConsultAlias.[Service Name], Scheduler_Sheet.Program FROM Scheduler_Sheet INNER JOIN tblConsultAlias ON Scheduler_Sheet.Clinic = tblConsultAlias.Clinic WHERE (Scheduler_Sheet.[ConsultNumber]) = '" & FromForm & "'")

I do use the following code elsewhere and it works fine:

Set RSReceiveName = CurrentDb.OpenRecordset("SELECT VHALogin, Fname, Lname, VistaAccess, VistaVerify, SaveVistaLogin FROM tblUpdatedEmployees WHERE (tblUpdatedEmployees.[VHALogin]) = '" & GetUserName & "'")

[pbaldy]

If ConsultNumber has a numeric data type, you don't want the apostrophes around the value.

[davegri]

WHERE (Scheduler_Sheet.[ConsultNumber]) = '" & Forms!frmToChart!ConsultNumber & "'")

If ConsultNumber actually is defined as a number then it can't have the quotes around it:

WHERE Scheduler_Sheet.[ConsultNumber] = " & Forms!frmToChart!ConsultNumber)

[Ekhart]

That got it, thanks! Something so simple yet easy to miss.