Execute a Select query in VB

[gmazza76]

Good afternoon

I have created a button to run specific queries I have in Access to update tables etc.... but I am having an issue it seems when I want to update a "select query"
Is there away to run the query as is without writing it in as sql as I have people on the team who understand the queries, but not when looking at it in SQL format?

I am using the following

Dim answer As Integer

answer = MsgBox("Has the daily CSAT information been uplaoded?", vbQuestion + vbYesNo)
    If answer = vbYes Then
        DoCmd.SetWarnings False 'turns off warnings
        DoCmd.RunSQL "Delete Table1.* From Table1;"
            CurrentDb.Execute "Query 1"
            CurrentDb.Execute "Query 2"
            CurrentDb.Execute "Query 3"
            CurrentDb.Execute "Query 4"
            CurrentDb.Execute "Query 5" ' Select Query

        DoCmd.SetWarnings True 'turns warning back on
    Else
        Exit Sub
    End If
End Sub

thanks in advance
Gavin

 

[Micron]

First, AFAIK you cannot Execute a select query, so I can't see how that would even run.
What do you mean by updating your select query? That could mean editing the sql of the query or it could mean updating the data retrieved by the query.

 

[gmazza76]

@Micron thanks for the advice, I have updated in SQL as per the code below but I am having an issue with the following in BOLD
When I run the query by double clicking no problem the "'NC, DF etc.." are populated correctly but when I use a button to auto run these they come up blank.

Any ideas on where I am going wrong?

strSQL = "INSERT INTO AllCSATData ( [Date], [Week No Week No], Quarter, [Year], [Month], [ID1], [Name], [Department], [TL], [CSAT Score], [CSAT Outcome], [Customer Journey], [Aligned To], [Subsidary ] )" & _
                "SELECT DateValue([TodaysDate]) AS [Date], DatePart(w,[TodaysDate],2,2) AS [Week No], 'Q' & Format([TodaysDate],'q') AS Quarter, Format([TodaysDate],'yyyy') AS [Year], " & _
                "IIf(DatePart(m,[DataToday].[TodaysDate])=1,'Januaryj,IIf(DatePart(m,[DataToday].[TodaysDate])=2,jFebruaryj, " & _
                "IIf(DatePart(m,[DataToday].[TodaysDate])=3,'March',IIf(DatePart(m,[DataToday].[TodaysDate])=4,'April', " & _
                "IIf(DatePart(m,[DataToday].[TodaysDate])=5,'May',IIf(DatePart(m,[DataToday].[TodaysDate])=6,'June', " & _
                "IIf(DatePart(m,[DataToday].[TodaysDate])=7,'July',IIf(DatePart(m,[DataToday].[TodaysDate])=8,'August', " & _
                "IIf(DatePart(m,[DataToday].[TodaysDate])=9,'September',IIf(DatePart(m,[DataToday].[TodaysDate])=10,'October', " & _
                "IIf(DatePart(m,[DataToday].[TodaysDate])=11,'November','December'))))))))))) AS [Month], " & _
                "DataToday.ID1, ST1.Sort_Name, ST1.CX_Lead, ST1.[Team Leader], DataToday.CDEScore, " & _
                "IIf([DataToday].[CDEScore]=0,'NC',IIf([DataToday].[CDEScore]=1,'DF',IIf([DataToday].[CDEScore]=2,'DF', " & _
                "IIf([DataToday].[CDEScore]=3,'N',IIf([DataToday].[CDEScore]=4,'S','S'))))) AS [CSAT Outcome], CJDesc.[Customer Journey], CJDesc.[Aligned To], CJDesc.[Subsidary ] " & _
                "FROM (DataToday INNER JOIN ST1 ON DataToday.ID1 = ST1.ID2) INNER JOIN CJDesc ON DataToday.Intent = CJDesc.[Customer Journey]" & _
                "WHERE (((DataToday.ID1)>0));"

thanks in advance.
Gavin

 

[gmazza76]

Apologies, its an append query this one not a select

 

[Micron]

Seems that the code tags removes formatting. You'll have to find another way to pinpoint the problem part I guess.
I have relatives staying until Tuesday, so not sure how much time I'll have to look at this before then.

 

[gmazza76]

Thanks @Micron,

It's from the line

IIf([DataToday].[CDEScore]=0,'NC',IIf([DataToday].[CDEScore]=1,'DF',IIf([DataToday].[CDEScore]=2,'DF

I have realised I think it's how I maybe writing the append as a couple of others aren't running, but the one above runs within the sequence but leaves the re blank

 

[Micron]

I don't know if you realize, but all portions of an IFF statement are evaluated even if any part returns True. Any part that causes an evaluation error can cause the whole thing to fail, but not sure what "leaves the re blank" means. Perhaps that means no record is appended. As long as you're doing this in code, you might as well simplify it. For starters, MonthName(Month(date),False) will return the month name - there's no need for you to figure it out based on 12 logic tests. I would also use a Select Case block to add one piece of sql for the parts that resolve to NC, DF and such. Eliminating the multiple nested IIFs might solve your problem if the problem is that the query appends no records.

 

[gmazza76]

Thanks where could I look to do what you have suggested please as in examples

 

[Micron]

Any chance you could post a copy of your file somewhere? Even if you have to redact data it would probably eliminate a lot of questions. For one thing, the answer depends on where your field data is located. Eliminating all the tests for the month name is probably the easy part. The rest, no idea as it depends.