Hi hopefully someone will be able to help me
I am using VBA and querydefs to amend some queries that I have created.
I have a table in which I have a list of names that I want to use for fields in these queries and i am opening this recordset and trying to get the code to scroll through and amend the queries - I am hoping I am fairly close but need a final push
below is the code
I am getting a syntax error around the oQuery line and i am going cross eyed working out where
Thanks
Thedawn
I am using VBA and querydefs to amend some queries that I have created.
I have a table in which I have a list of names that I want to use for fields in these queries and i am opening this recordset and trying to get the code to scroll through and amend the queries - I am hoping I am fairly close but need a final push
below is the code
Code:
Private Sub AmendOPQueries_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim oQuery As QueryDef
Set db = CurrentDb()
Set rs = db.OpenRecordset("TableofTrackSafetyNO", dbOpenDynaset)
Do Until rs.EOF
Set oQuery = db.QueryDefs(rs!competency)
oQuery.SQL = ("SELECT [" & rs!competency & "].[NI Number], IIf([" & rs!competency & "]![NI Number]=[NonOperational]![NINUMBER],'No','Yes') AS Operational, [" & rs!competency & "].[" & rs!competency & "], CStr([" & rs!competency & "]![" & rs!competency & "]) AS TextExpiryDate, Left([TextExpiryDate],6) & CStr(CInt(Right([TextExpiryDate],4))-1) AS ActivityDate, " & rs!competency & " AS MyCourseCode FROM [" & rs!competency & "] LEFT JOIN NonOperational ON [" & rs!competency & "].[NI Number] = NonOperational.NINUMBER WHERE (((IIf([" & rs!competency & "]![NI Number]=[NonOperational]![NINUMBER],'No','Yes'))='No'));")
rs.MoveNext
Loop
rs.Close
Set db = Nothing
End Sub
I am getting a syntax error around the oQuery line and i am going cross eyed working out where
Thanks
Thedawn