Replacing #Error with a number

[johnwiseman]

Hello.

Running the following code delivers the required results unless [qry Staff Task Volumes.[sumofvolumes] is "0".

This is due to [ActualMinsPersTask] divides [sumofvolume]/[sumoftime] and if [sumofvolumes] = "0" you cant divide the [sumofvolumes] figure by "0", you get an #error in [ActualMinsPerTask].

SELECT [qry Staff Actual Rate Child].[Staff Number], [qry Staff Actual Rate Child].[Task Code], [qry Staff Actual Rate Child].SumOfTime, [qry Staff Task Volumes].SumOfVolumes, [sumoftime]/[SumOfVolumes] AS ActualMinsPerTask
FROM [qry Staff Actual Rate Child] INNER JOIN [qry Staff Task Volumes] ON [qry Staff Actual Rate Child].[Task Code] = [qry Staff Task Volumes].[Task Code];


Thanks in advance for your help.

 

[bborissov]

[COLOR=blue]SELECT[/color] [qry Staff Actual Rate Child].[Staff [COLOR=blue]Number[/color]],
       [qry Staff Actual Rate Child].[[COLOR=#FF00FF]Task[/color] Code],
       [qry Staff Actual Rate Child].SumOfTime,
       [qry Staff [COLOR=#FF00FF]Task[/color] Volumes].SumOfVolumes,
       [COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] SumOfVolumes = 0
            [COLOR=blue]THEN[/color] 0
            [COLOR=blue]ELSE[/color][sumoftime]/[SumOfVolumes] [COLOR=blue]END[/color]
            [COLOR=blue]AS[/color] ActualMinsPerTask
[COLOR=blue]FROM[/color] [qry Staff Actual Rate Child]
     [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] [qry Staff [COLOR=#FF00FF]Task[/color] Volumes] [COLOR=blue]ON[/color]
           [qry Staff Actual Rate Child].[[COLOR=#FF00FF]Task[/color] Code] =
           [qry Staff [COLOR=#FF00FF]Task[/color] Volumes].[[COLOR=#FF00FF]Task[/color] Code]

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP