Using LEFT function in WHERE clause

  • Thread starter Thread starter Kayda
  • Start date Start date
K

Kayda

I keep getting prompted for a parameter when using a left function in a
WHERE clause. This only happens when the LEFT clause below is used. Take it
out, and it runs.

This is a UNION query to a linked table. My query looks good, what is up?



SELECT
DowntowniSkillsetStat.Timestamp, DowntowniSkillsetStat.Skillset,
DowntowniSkillsetStat.ActiveTime, DowntowniSkillsetStat.CallsAnswered,
DowntowniSkillsetStat.CallsAnsweredDelay,
DowntowniSkillsetStat.CallsOffered, DowntowniSkillsetStat.SkillsetAbandoned,
DowntowniSkillsetStat.TotalStaffedTime, DowntowniSkillsetStat.Time
FROM
DowntowniSkillsetStat DowntowniSkillsetStat
WHERE
( LEFT([DowntownSkillsetStat].[Skillset], 3) = 'Ass') AND
DowntowniSkillsetStat.Time >= '07:00' AND
DowntowniSkillsetStat.Time <= '19:45'

UNION

SELECT
FrankliniSkillsetStat.Timestamp, FrankliniSkillsetStat.Skillset,
FrankliniSkillsetStat.ActiveTime, FrankliniSkillsetStat.CallsAnswered,
FrankliniSkillsetStat.CallsAnsweredDelay,
FrankliniSkillsetStat.CallsOffered, FrankliniSkillsetStat.SkillsetAbandoned,
FrankliniSkillsetStat.TotalStaffedTime, FrankliniSkillsetStat.Time
FROM
FrankliniSkillsetStat FrankliniSkillsetStat
WHERE
( FrankliniSkillsetStat.Skillset = 'POS_Expert' OR
FrankliniSkillsetStat.Skillset = 'POS_Express' OR
FrankliniSkillsetStat.Skillset = 'POS_Unique' OR
FrankliniSkillsetStat.Skillset = 'POS_Client') AND

FrankliniSkillsetStat.Time >= '07:00' AND
FrankliniSkillsetStat.Time <= '19:45';
 
As far as I can tell, with the Left() function, you have created a new column in the first query, thus, causing the two queries not to have the same amount of columns

HTH, and hope I am right:)
 
ÄúºÃ£ºÎÒÓÐÒ»¸öÎÊÌâ
Wayne Morgan said:
Right off-hand, this sounds as if it is a References error. Check this link.
http://members.iinet.net.au/~allenbrowne/ser-38.html

--
Wayne Morgan
Microsoft Access MVP


Kayda said:
I keep getting prompted for a parameter when using a left function in a
WHERE clause. This only happens when the LEFT clause below is used. Take it
out, and it runs.

This is a UNION query to a linked table. My query looks good, what is up?



SELECT
DowntowniSkillsetStat.Timestamp, DowntowniSkillsetStat.Skillset,
DowntowniSkillsetStat.ActiveTime, DowntowniSkillsetStat.CallsAnswered,
DowntowniSkillsetStat.CallsAnsweredDelay,
DowntowniSkillsetStat.CallsOffered, DowntowniSkillsetStat.SkillsetAbandoned,
DowntowniSkillsetStat.TotalStaffedTime, DowntowniSkillsetStat.Time
FROM
DowntowniSkillsetStat DowntowniSkillsetStat
WHERE
( LEFT([DowntownSkillsetStat].[Skillset], 3) = 'Ass') AND
DowntowniSkillsetStat.Time >= '07:00' AND
DowntowniSkillsetStat.Time <= '19:45'

UNION

SELECT
FrankliniSkillsetStat.Timestamp, FrankliniSkillsetStat.Skillset,
FrankliniSkillsetStat.ActiveTime, FrankliniSkillsetStat.CallsAnswered,
FrankliniSkillsetStat.CallsAnsweredDelay,
FrankliniSkillsetStat.CallsOffered, FrankliniSkillsetStat.SkillsetAbandoned,
FrankliniSkillsetStat.TotalStaffedTime, FrankliniSkillsetStat.Time
FROM
FrankliniSkillsetStat FrankliniSkillsetStat
WHERE
( FrankliniSkillsetStat.Skillset = 'POS_Expert' OR
FrankliniSkillsetStat.Skillset = 'POS_Express' OR
FrankliniSkillsetStat.Skillset = 'POS_Unique' OR
FrankliniSkillsetStat.Skillset = 'POS_Client') AND

FrankliniSkillsetStat.Time >= '07:00' AND
FrankliniSkillsetStat.Time <= '19:45';
Click to expand...
Click to expand...
 
Back
Top