Comparing Fields

[Guest]

I get a run time error with this code 3075 and can't figure out what sintax
error access is refering to can anyone help me please.

Function SkillState(ClockNo, SkillID)

Dim sq, sqmid, sqend As String
Dim rst As DAO.Recordset
Dim DB As DAO.Database


sq = "SELECT tblLevels.SkillID, tblLevels.Startdate, tblLevels.Achievedate,
tblPersons.[Clock No] FROM tblPersons INNER JOIN tblLevels ON
tblPersons.nameID = tblLevels.nameID WHERE (((tblLevels.SkillID)= """
sqmid = """ AND((tblPersons.[Clock No]) = """
sqend = """));"

sq = sq & Str(SkillID) & sqmid & Str(ClockNo) & sqend

Set rst = CurrentDb.OpenRecordset(sq, dbOpenSnapshot)
SkillState = 0
If rst.Fields(Startdate) <> Null Then SkillState = 1
If rst.Fields(Achievedate) <> Null Then SkillState = 2
rst.Close

End Function

 

[John Spencer]

Your first DIM line is declaring Sq, sqMid as variants not strings.

Dim sq as String, sqMid as String, sqEnd as String

Next, Sq=... should probably read

sq = "SELECT tblLevels.SkillID, tblLevels.Startdate, tblLevels.Achievedate,
tblPersons.[Clock No] FROM tblPersons INNER JOIN tblLevels ON
tblPersons.nameID = tblLevels.nameID WHERE (((tblLevels.SkillID)= """ &
sqmid & """ AND((tblPersons.[Clock No]) = """ & sqend & """));"