Using VBA to amend queries

[RainLover]

There is no way I can test without some data from you.

So give this a try.

Option Compare Database
Option Explicit

Private Sub AmendQueries_Click()
    
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim oQuery As QueryDef
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("TableofCompetencies", dbOpenDynaset)
      
    Do Until rs.EOF
    Set oQuery = db.QueryDefs(rs!competency)
    
    oQuery.SQL = " SELECT NCCACompetency.[NI Number], " & _
                " NCCACompetency.[" & rs!competency & "] " & _
                " FROM NCCACompetency " & _
                " WHERE NCCACompetency.[" & rs!competency & "] " & _
                " Is Not Null "
            
    rs.MoveNext
    Loop
   
End Sub

 

[thedawn]

Hi Rain

I think I may have confused you - the two subroutines that I included in my latest post were the ones that are actually working.

If I explain the process I am trying to automate from start to finish and provide the initial data maybe that may help.

I am receiving data from an external system. This is shown in the attached spreadsheet (Competency.xls)

This shows personal data and competency expiry data for a number of different people (all personal data xxxxx now)

I need to convert this into different formats but also need to change some of the data due to different expiry dates for different groups of people.

The first two subroutines that I sent across create:

1) a Query for each column of data (whether blank or not)
2) amends each query so that the second column name is changed eg

the query I copy has 2 columns one called NI Number and one called PTS AC the subroutines above create copy queries with the name changed to each column in the spreadsheet and then amends the query to have the secon column changed to the competency (around 200 queries)

I then create another set of queries to amend dates this creates another set of queries with names i use from a table but when i then do my next subroutine which is the one i have posted in the forum i can't get the SQL to work.

If there is another way to do it then that would be fine if you have ideas.

The subroutine that I cant get the SQL working on is:

Private Sub AmendNOQueries_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tmpSQL As String
Dim oQuery As QueryDef

   Set db = CurrentDb()
   Set rs = db.OpenRecordset("TableofTrackSafetyNO", dbOpenDynaset)
   
   
   Do Until rs.EOF
   Set oQuery = db.QueryDefs(rs!competency)
   tmpSQL = "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 & "] INNER JOIN NonOperational ON " & _
   "[" & rs!competency & "]![NI Number] = [NonOperational]![NINUMBER] WHERE " & _
   "(((IIf([" & rs!competency & "]![NI Number]=[NonOperational]![NINUMBER],""No"",""Yes""))=""No""));"
   
   Debug.Print tmpSQL
   oQuery.SQL = tmpSQL
   
   rs.MoveNext
   Loop
   
   rs.Close
   Set db = Nothing
End Sub

Thanks

Rich

 

[RainLover]

theDawn

This is a challenge.

Does the format of the spread sheet ever change. Are the headings exactly the same name and in the same position every time.

 

[thedawn]

Hi Rain

The spreadsheet in question will always be the same and the output will always be the same

Thanks

Rich

 

[RainLover]

Rich

I will try and help but please don't just rely on me.

 

[thedawn]

Rain

Thanks for even looking at it - if I could get that SQL correct I can then move on and complete the outputs

I do think it is not being able to put quotation marks around the NOCOSS part of the SQL - if I could do that I think it may work

I am working on trying to sort it myself also Rain but I am most appreciative

Cheers

Rich