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

John,

    My sample db named Report_PseudoGraphs might be of interest to you. It is in Access 2000 file format and is available at Rogers Access Library. Link - 
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=45

    On the main switchboard, command button captioned  "Monthly Salary As Per Scales Notified Periodically" takes you to form F_Salary that displays monthly salary for the given employee, as per scales notified from time to time. 

    For a given month and year, the most recent scale notification for the employee in question upto that month year becomes applicable. The notified salary scales are shown in the first subform while the actual applicable salary is shown in the adjacent subform.

    A report covering above details gets generated by clicking the appropriate command button. The report includes graphical representation of month-wise salary for the given employee. Whenever there is a change in salary level, it is accompanied by a change in back color of value column (in graphical version) so as to facilitate visual appreciation.

Best wishes,
A.D. Tejpal
------------

  ----- Original Message ----- 
  From: John Clark 
  To: 'Access Developers discussion and problem solving' 
  Sent: Thursday, November 05, 2009 22:35
  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;