Sort by decimal fraction

[Oscar_W]

The attached file shows a screen shot of my query. I need now to isolate only those results that are not whole numbers. I think it will be MOD or INT but I can't get it to work in the query.
Also, do I add this "where expression" to the Result column or do I need to create a new Expr: column ?
Thanks.

SQL added in case it helps anyone.

SELECT Qry_All_Ops.StartDate, Qry_All_Ops.Unit, Count(Qry_All_Ops.Unit) AS CountOfUnit, Sum(Qry_All_Ops.JettyNumber) AS SumOfJettyNumber, [sumofJettyNumber]/[countofunit] AS Result
FROM Qry_All_Ops
GROUP BY Qry_All_Ops.StartDate, Qry_All_Ops.Unit, Qry_All_Ops.Status
HAVING (((Count(Qry_All_Ops.Unit))>1) AND ((Qry_All_Ops.Status)="approved"));

 

[Norstar2]

Use the CInt function to see if a number is an Integer(Whole Number)

Example:

Dim dblNum as double

  dblNum = 1
  If dblNum = CInt(dblNum) Then 
    'Is an integer
  Else
    'Not an integer
  End If

 

[Oscar_W]

Thanks. I added

Expr1: [result]=CInt([result])

as a new column and it sorts the results into -1 and 0. It's the 0's I need but when I add the criteria "=0" as a where expression to the new column it tells me the expression is too complex !

New code

SELECT Qry_All_Ops.StartDate, Qry_All_Ops.Unit, Count(Qry_All_Ops.Unit) AS CountOfUnit, Sum(Qry_All_Ops.JettyNumber) AS SumOfJettyNumber, [sumofJettyNumber]/[countofunit] AS Result, [result]=CInt([result]) AS Expr1
FROM Qry_All_Ops
GROUP BY Qry_All_Ops.StartDate, Qry_All_Ops.Unit, Qry_All_Ops.Status
HAVING (((Count(Qry_All_Ops.Unit))>1) AND ((Qry_All_Ops.Status)="approved"));

Help !

 

[Jon K]

Try this:-

SELECT Qry_All_Ops.StartDate, Qry_All_Ops.Unit, Count(Qry_All_Ops.Unit) AS CountOfUnit, Sum(Qry_All_Ops.JettyNumber) AS SumOfJettyNumber, [sumofJettyNumber]/[countofunit] AS Result, [result]=CInt([result]) AS Expr1
FROM Qry_All_Ops
GROUP BY Qry_All_Ops.StartDate, Qry_All_Ops.Unit, Qry_All_Ops.Status
HAVING (((Count(Qry_All_Ops.Unit))>1) AND ((Qry_All_Ops.Status)="approved")) and
Sum(Qry_All_Ops.JettyNumber)/Count(Qry_All_Ops.Unit) =CInt(Sum(Qry_All_Ops.JettyNumber)/Count(Qry_All_Ops.Unit))=0


Hopefully it works.
Normally if you put =0 to Expr1: [result]=CInt([result]), when the query was run, the error would have been "Enter parameter value for result" because result was an alias in the expression.
.

 

[Oscar_W]

Excellent, thanks. I had bodged a fix where I wrote a second query and called one from the other but this is so much neater.

Although it works fine, I have realised that there is an error in my maths and the code below won't find all the "errors"

Result: [sumofJettyNumber]/[countofunit]

Eg. If a ship is listed as being on Jetty 2 & Jetty 3 it will flag it as an error.
But:
If it's on Jetty 2 & Jetty 4 it won't.

I think I will post this bit in the General section.