Sum Query reports invalid procedure call

[kimindoha]

the query is as follow and is based on a big table called WeldData.

There are 2 materials being welded and each material can have different sizes.

so i have to pick the smallest size, unfortunately the diameter field is in text (ie one enters it like 2"), so i have to take running sum of the filtered values to derive at the Total.

Please help me where is the error ??

Many Thanks in Advance.

Posted below is the copy of the query.

SELECT sum(final)*3.14*25.4 AS new
FROM (SELECT [Weld Type], [DWR Date], [Weld Flag], IIf(CSng(left([Material Diameter 1],(instr(1,[Material Diameter 1],"""",1)-1)))<CSng(left([Material Diameter 2],(instr(1,[Material Diameter 2],"""",1)-1))),CSng(left([Material Diameter 1],(instr(1,[Material Diameter 1],"""",1)-1))),CSng(left([Material Diameter 2],(instr(1,[Material Diameter 2],"""",1)-1)))) AS Final FROM WeldData GROUP BY [Weld Type], [DWR Date], [Weld Flag], IIf(CSng(left([Material Diameter 1],(instr(1,[Material Diameter 1],"""",1)-1)))<CSng(left([Material Diameter 2],(instr(1,[Material Diameter 2],"""",1)-1))),CSng(left([Material Diameter 1],(instr(1,[Material Diameter 1],"""",1)-1))),CSng(left([Material Diameter 2],(instr(1,[Material Diameter 2],"""",1)-1)))))  AS a
WHERE [Weld Type] Like '*B*' And (Month([DWR Date])=Month(Now())-1) And ([Weld Flag] like "Production Joint" or [Weld Flag] like "New Joint" or [Weld Flag] like "Tracer Joint");

[DonRayner]

I moved your Conversion from string to number and if statements out to a public Function.

Public Function MaterialSize(Material1 As String, Material2 As String) As Integer
Material1 = Left(Material1, InStr(1, Material1, """", 1) - 1)
Material2 = Left(Material2, InStr(1, Material2, """", 1) - 1)
 
If Material1 < Material2 Then
    MaterialSize = Material1
Else
    MaterialSize = Material2
End If
 
End Function

And Changing your SQL to this

SELECT sum(final)*3.14*25.4 AS new
FROM (SELECT [Weld Type], [DWR Date], [Weld Flag], MaterialSize([Material Diameter 1],[Material Diameter 2]) AS Final FROM WeldData GROUP BY [Weld Type], [DWR Date], [Weld Flag], MaterialSize([Material Diameter 1],[Material Diameter 2])) AS a
WHERE [Weld Type] Like '*B*' And (Month([DWR Date])=Month(Now())-1) And ([Weld Flag] like "Production Joint" or [Weld Flag] like "New Joint" or [Weld Flag] like "Tracer Joint"); 

[kimindoha]

It is a much better solution.

Many Thanks !! DonRayner

[DonRayner]

You're quite welcome. Good luck with your project.

[NeoPa]

FYI: I would change your WHERE clause as follows too :

WHERE ([Weld Type] Like '*B*')
  AND (Month([DWR Date])=Month(Date())-1)
  AND ([Weld Flag] In('Production Joint', 'New Joint', 'Tracer Joint');

The effect will be the same, but it's tidier and more flexible.

[kimindoha]

Many Thanks for the advice. it is cleaner code.

[NeoPa]

My pleasure.

Welcome to Bytes!