[AccessD] Incorrect Data...sometimes WAS: Try this one again

John Clark John.Clark at niagaracounty.com
Mon Dec 21 10:19:45 CST 2009


Well, thanks to members of this list...again!...I am on the way to finishing up my current project...one of them anyhow. But, I've got a quick...hopefully...snag to work out first.
 
The help I received pointed me to the following code, which obtains a correct mileage rate, as per the date on the form:
 
Me.curMRate = DLookup("curMRate", "qryMileageRateSrch")
 
The query that this calls, uses the date on the form to find the proper rate
 
SELECT TOP 1 tblMileageRate.datDate, tblMileageRate.curMRate
FROM tblMileageRate
WHERE (((tblMileageRate.datDate)<[Forms]![WitnessVoucherData]![datDate]))
ORDER BY tblMileageRate.datDate DESC;
 
And, this works flawlessly, when running the query. However, when running it from the form, it returns the wrong rate for one of the months. For testing purposes, I entered rate data for three months...it is usually only updated quarterly...and I've used various dates w/in those periods as tests. Her is the actual data:
 
09/01/2009 ... $0.18
10/01/2009 ... $0.20
11/01/2009 ... $0.23
 
If I enter any date w/in October, I get $0.20 returned for a rate. Any date w/in November returns $0.23. But, if I put in a September date, I get $0.23 (i.e. November).
 
As I typed this, I have an idea of what may be happening...it is something I wondered about, but it seemed to work, so I moved on. The field type is "text", so I wonder if textually, this order is different...



More information about the AccessD mailing list