[AccessD] Query question ... Finding the proper rate/per date

I think I would do this...

1.  I would write a function that figures your final amount you are after.
In that function it makes sure that the miles are entered and a date has
been entered (which means the proper rate has been  found).  If these two
conditions are not true then exit the function.  If true run the calculation
and place the value in the proper text box on the form.  The function will
be called by steps in 2 and 3 below.  You can either pass the rate and miles
to the new function or have the new function get those numbers from the form
if you write the function within that form.

2.  On the after_update event of the date entry box I would open a recordset
using that SQL to get the rate.  For simplicity for now, I would place that
number into a text box on the form.  Call function above and if conditions
are true you get your amount - if not, you get nothing

3.  On the after_update event of the mileage entry box do call the function
in #1.

TOP should work fine in a recordset.



Reuben Cummings
GFC, LLC
812.523.1017


> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Clark
> Sent: Thursday, November 05, 2009 12:05 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Query question ... Finding the proper 
> rate/per date
> 
> 
> No sir, I did not. Man I am struggling w/this thing. I 
> haven't been using access, for quite a while now, and I'm 
> trying to remember stuff that I've done and it just isn't 
> coming to me. The query, as Stuart gave, is working great...I 
> will be adding the "<=" adjustment too...but I want to grab 
> that result and put it into my form, and the record as well.
>  
> I thought I knew exactly how to do this, but the VBA doesn't 
> seem to like that "TOP" command at all. I started down the 
> path of creating a SQL string and running docmd.runSQL, but 
> that door closed too...I might go back to this, because I 
> think I was making mistakes w/it.
> 
> >>> "Reuben Cummings" <accessd at gfconsultants.com> 11/5/2009 
> 8:58 AM >>>
> John, did you get the following request answered?
> 
> Reuben Cummings
> GFC, LLC
> 812.523.1017
> 
> > 
> > I'm sorry to ask this...but I haven't written anything, aside 
> > from one very miniscule little program, in about two years, 
> > and apparently the magic is gone...I am lost. This code that 
> > Stuart offered up is exactly what I was looking to do, 
> > however, I'd like to do it from a form and what I thought I 
> > needed to do is not working. 
> >  
> > I want to (A) have the rate come up, once a given date is 
> > entered, and be there already when an old record is 
> > accessed...I want to be able to say, "this was entered on 
> > <date> and this was the rate at that time." And, (B) I want 
> > it to multiply the number of miles w/this rate and present a 
> > total. And (C) I want to do pick the going fee out of a 
> > similar table and add this to the equation. I think if I can 
> > do one of these, I can do them all.
> >  
> > How can I grab this, when a new date is entered?
> >  
> > Here is the code I am using, in the query, which works...
> >  
> > SELECT TOP 1 tblMileageRate.datDate, tblMileageRate.curMRate
> > 'FROM tblMileageRate
> > 'WHERE (((tblMileageRate.datDate) < [Date Entered]))
> > 'ORDER BY tblMileageRate.datDate DESC;
> > 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>