Query not sorting by >Days gone 1

[Triacona]

Dear All,
Thanks for a great forum!
Within the query I have below there is a field DaysGone that is a calculation field.
DaysGone = Todays date - date application was made valid.
Then in the criteria I have: >=[forms]![MainScreen].[SrchCrit]
I have to use the VAL() function as otherwise it gives me a ODBC call failure.
It also doesn't provide the correct output.
E.G. if I put 56 in the field of SrchCrit it gives me output from 6 all the way to 8379???

SELECT UNI7LIVE_DCAPPL.OFFCODE, Val(Date()-(UNI7LIVE_DCAPPL.DATEAPVAL)) AS DaysGone, UNI7LIVE_DCAPPL.DATEAPVAL, 
UNI7LIVE_DCAPPL.DATEDECISN, UNI7LIVE_DCAPPL.REFVAL, UNI7LIVE_DCAPPL.PROPOSAL, UNI7LIVE_DCAPPL.ADDRESS, UNI7LIVE_DCAPPL.DATE8WEEK, 
UNI7LIVE_DCAPPL.DATEAPRECV, UNI7LIVE_DCAPPL.DCSTAT, UNI7LIVE_DCAPPL.DCAPPTYP, UNI7LIVE_DCAPPL.DECTYPE, UNI7LIVE_DCAPPL.DATEDECISS, (Date()-UNi7LIVE_DCAPPL.DATEAPVAL) AS DaysGoneT
FROM UNI7LIVE_DCAPPL
GROUP BY UNI7LIVE_DCAPPL.OFFCODE, Val(Date()-(UNI7LIVE_DCAPPL.DATEAPVAL)), UNI7LIVE_DCAPPL.DATEAPVAL, UNI7LIVE_DCAPPL.DATEDECISN, 
UNI7LIVE_DCAPPL.REFVAL, UNI7LIVE_DCAPPL.PROPOSAL, UNI7LIVE_DCAPPL.ADDRESS, UNI7LIVE_DCAPPL.DATE8WEEK, UNI7LIVE_DCAPPL.DATEAPRECV, 
UNI7LIVE_DCAPPL.DCSTAT, UNI7LIVE_DCAPPL.DCAPPTYP, UNI7LIVE_DCAPPL.DECTYPE, UNI7LIVE_DCAPPL.DATEDECISS
HAVING (((UNI7LIVE_DCAPPL.OFFCODE) Like "*" & [Forms]![MainScreen].[CmbOff] & "*") AND ((Val(Date()-(UNI7LIVE_DCAPPL.DATEAPVAL)))>=[forms]![MainScreen].[SrchCrit]) AND 
((UNI7LIVE_DCAPPL.DATEAPVAL) Is Not Null) AND ((UNI7LIVE_DCAPPL.DCSTAT) Not In ("NFA","PD","WDN","PPREQ","PDREM","PREAPP","COMP","PDE","CLO")) 
AND ((UNI7LIVE_DCAPPL.DATEDECISS) Is Null))
ORDER BY Val(Date()-(UNI7LIVE_DCAPPL.DATEAPVAL));

Any help/advice would be greatly appreciated!
Thanks

Thank you,

Kind regards

Triacona

 

[SkipVought]

hi,

It also doesn't provide the correct output.

that statement does not carry any helpful information that would aid someone on the outside in trying to evaluate what might be happening.

What do you believe should be the correct output?

if I put 56 in the field of SrchCrit it gives me output from 6 all the way to 8379???

"Output from 6 all the way to 8379" in what field?
You do realize that your query species that and DaysGone value >= 56, along with the other criteria, can return many rows in your resultset.

Also, I would put ALL the having statments into a WHERE clause...

FROM UNI7LIVE_DCAPPL ud

where ud.OFFCODE               Like "*" & [Forms]![MainScreen].[CmbOff] & "*"
  AND Val(Date()-ud.DATEAPVAL) >=[forms]![MainScreen].[SrchCrit]
  AND ud.DATEAPVAL             Is Not Null
  AND ud.DCSTAT                Not In ("NFA","PD","WDN","PPREQ","PDREM","PREAPP","COMP","PDE","CLO")
  AND ud.DATEDECISS            Is Null

GROUP BY ud.OFFCODE, Val(Date()-(ud.DATEAPVAL)), ud.DATEAPVAL, ud.DATEDECISN, 
ud.REFVAL, ud.PROPOSAL, ud.ADDRESS, ud.DATE8WEEK, ud.DATEAPRECV, 
ud.DCSTAT, ud.DCAPPTYP, ud.DECTYPE, ud.DATEDECISS

ORDER BY Val(Date()-ud.DATEAPVAL);

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Triacona]

Thank you Skip

The correct output would be where DaysGoneBy >=[forms]![MainScreen].[SrchCrit], in the [forms]![MainScreen].[SrchCrit] field the user can input any number.

I want the query to then use the number within DaysGoneBy and anything within DaysGoneBy to be either greater or equal to that number.

So if I put in 56 I don't want any number below that...

Thanks for all your help


Thank you,

Kind regards

Triacona

 

[PHV]

Try this:
>=Val([forms]![MainScreen].[SrchCrit])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Triacona]

Dear PHV,

Brilliant! Thank you!

Thank you,

Kind regards

Triacona