Variables in SQL Access

Question

Variables in SQL Access

Navigation

#1 by (0 votes)

2

I have the following problem,

I have tried to change the following statement sql in access to include variables, but I get an error.

If I do not use variables, the query works without problems.

SQL statement

parameters @start date;
parameters @finish date;
set @start = #4/1/2017 0:0:1#;
set @finish = #4/30/2017 23:59:0#;
SELECT dbo_t_Agent.PeripheralNumber, dbo_t_Agent.EnterpriseName,
        Sum(dbo_t_Agent_Skill_Group_Half_Hour.CallsHandledToHalf) AS SumaDeCallsHandledToHalf,
        Sum(dbo_t_Agent_Skill_Group_Half_Hour.AgentOutCallsToHalf) AS SumaDeAgentOutCallsToHalf,
        Sum(dbo_t_Agent_Skill_Group_Half_Hour.HandledCallsTimeToHalf) AS SumaDeHandledCallsTimeToHalf,
        Sum(dbo_t_Agent_Skill_Group_Half_Hour.AgentOutCallsTimeToHalf) AS SumaDeAgentOutCallsTimeToHalf,
        Sum(dbo_t_Agent_Skill_Group_Half_Hour.InternalCallsRcvdToHalf) AS SumaDeInternalCallsRcvdToHalf,
        Sum(dbo_t_Agent_Skill_Group_Half_Hour.TransferredInCallsToHalf) AS SumaDeTransferredInCallsToHalf
FROM dbo_t_Agent INNER JOIN dbo_t_Agent_Skill_Group_Half_Hour ON dbo_t_Agent.SkillTargetID = dbo_t_Agent_Skill_Group_Half_Hour.SkillTargetID
WHERE (((dbo_t_Agent_Skill_Group_Half_Hour.DateTime) Between @start And @finish))
GROUP BY dbo_t_Agent.PeripheralNumber, dbo_t_Agent.EnterpriseName
HAVING (((Sum(dbo_t_Agent_Skill_Group_Half_Hour.CallsHandledToHalf))>0));

What have I tried?

I have changed the sets, since they are declared as date, to remove the # . But the problem is not there, because I keep getting the same error.

set @start = 4/1/2017 0:0:1;
set @finish = 4/30/2017 23:59:0;

What error comes out when executing the query?

Invalid SQL statement, expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or 'UPDATE'

What am I doing wrong? Is it possible that you can not really use variables in Access statements?

sql ms-access ms-acces-2007

asked by David 09.06.2017 в 11:39

source

1 answer

Fragments are created with a Gray background and not white as default Error: invalid instruction suffix for 'push'

score 0 · Answer 1

You can do it by typing the name of the variable between square brackets, so that access will request the parameters.

SELECT dbo_t_Agent.PeripheralNumber, dbo_t_Agent.EnterpriseName,
        Sum(dbo_t_Agent_Skill_Group_Half_Hour.CallsHandledToHalf) AS SumaDeCallsHandledToHalf,
        Sum(dbo_t_Agent_Skill_Group_Half_Hour.AgentOutCallsToHalf) AS SumaDeAgentOutCallsToHalf,
        Sum(dbo_t_Agent_Skill_Group_Half_Hour.HandledCallsTimeToHalf) AS SumaDeHandledCallsTimeToHalf,
        Sum(dbo_t_Agent_Skill_Group_Half_Hour.AgentOutCallsTimeToHalf) AS SumaDeAgentOutCallsTimeToHalf,
        Sum(dbo_t_Agent_Skill_Group_Half_Hour.InternalCallsRcvdToHalf) AS SumaDeInternalCallsRcvdToHalf,
        Sum(dbo_t_Agent_Skill_Group_Half_Hour.TransferredInCallsToHalf) AS SumaDeTransferredInCallsToHalf
FROM dbo_t_Agent INNER JOIN dbo_t_Agent_Skill_Group_Half_Hour ON dbo_t_Agent.SkillTargetID = dbo_t_Agent_Skill_Group_Half_Hour.SkillTargetID
WHERE (((dbo_t_Agent_Skill_Group_Half_Hour.DateTime) Between [fecha inicial] And [fecha final]))
GROUP BY dbo_t_Agent.PeripheralNumber, dbo_t_Agent.EnterpriseName
HAVING (((Sum(dbo_t_Agent_Skill_Group_Half_Hour.CallsHandledToHalf))>0));

With this you solve the problem of the query and the request of the parameters once.