phendrena
03-06-2009, 09:45 AM
Hello again,
I have the following code :-
sSQL = "SELECT [7-Day Support (Total Actioned ~ Current Date)].[Total Of ID], [7-Day Support (Total Actioned ~ Current Date)].[Yes], [7-Day Support (Total Actioned ~ Current Date)].[No] " & _
"FROM [7-Day Support (Total Actioned ~ Current Date)];"
This code is part of an Excel userform that gets data from Access.
The problem that I have is that it is based on a crosstab query that isn't going to contain the values of 'Yes' or 'No' all the time. As such the code will error when the userform code is run.
Would anyone be able to suggest an alternative that won't cause any problems.
If it helps here is the SQL Crosstab query code :-
TRANSFORM Count(Inbound_Call_Log.ID) AS [The Value]
SELECT Inbound_Call_Log.DateOfCall, Inbound_Call_Log.[7DayTeam], Count(Inbound_Call_Log.ID) AS [Total Of ID]
FROM Inbound_Call_Log
WHERE (((Inbound_Call_Log.[7DayTeam])<>"" And (Inbound_Call_Log.[7DayTeam]) Is Not Null) AND ((Inbound_Call_Log.DateOfCall)=Date()))
GROUP BY Inbound_Call_Log.DateOfCall, Inbound_Call_Log.[7DayTeam]
PIVOT [Inbound_Call_Log].[7DayTeamActioned];
I have posted here as I feel this more of an Access query than an Excel query.
Thanks,
I have the following code :-
sSQL = "SELECT [7-Day Support (Total Actioned ~ Current Date)].[Total Of ID], [7-Day Support (Total Actioned ~ Current Date)].[Yes], [7-Day Support (Total Actioned ~ Current Date)].[No] " & _
"FROM [7-Day Support (Total Actioned ~ Current Date)];"
This code is part of an Excel userform that gets data from Access.
The problem that I have is that it is based on a crosstab query that isn't going to contain the values of 'Yes' or 'No' all the time. As such the code will error when the userform code is run.
Would anyone be able to suggest an alternative that won't cause any problems.
If it helps here is the SQL Crosstab query code :-
TRANSFORM Count(Inbound_Call_Log.ID) AS [The Value]
SELECT Inbound_Call_Log.DateOfCall, Inbound_Call_Log.[7DayTeam], Count(Inbound_Call_Log.ID) AS [Total Of ID]
FROM Inbound_Call_Log
WHERE (((Inbound_Call_Log.[7DayTeam])<>"" And (Inbound_Call_Log.[7DayTeam]) Is Not Null) AND ((Inbound_Call_Log.DateOfCall)=Date()))
GROUP BY Inbound_Call_Log.DateOfCall, Inbound_Call_Log.[7DayTeam]
PIVOT [Inbound_Call_Log].[7DayTeamActioned];
I have posted here as I feel this more of an Access query than an Excel query.
Thanks,