[AccessD] Try this one again

John Clark John.Clark at niagaracounty.com
Fri Dec 18 10:17:56 CST 2009


Well A.D., I think you are officially my hero today! ;o)
 
First of all, as soon as I saw your code...specifically the change to the SQL of the query...I regained my memory. This is exactly what I had done in the past, and I had totally forgotten about it. So, because I had done it that way before, and I am pressed for time, I went this way again...and it worked...for the most part it worked.
 
Now I am on to something I totally did not foresee...something I've never run into before. I am having simple arithmetic problems now
 
(A) The biggest problem...decimals aren't showing up properly. I've got the number type setup as double w/2 decimals, and I am still seeing "0.2" and "15" instead of "15.00"
 
and
 
(B) It doesn't seem to want to perform the addition of the standard fee. The witness gets mileage, based on the going rate, multiplied by their distance, and this is working. But, they also get a stipend of $15.00, and this isn't working right now. 
 
I'm not real worried about this last one...yet...because I'm guessing it is something dumb that I am missing or doing, and I've just got to go back and proof my work for a mistake.

>>> "A.D. Tejpal" <adtp at airtelmail.in> 12/18/2009 4:25 AM >>>
John,

    Apparently, you wish to fetch the latest applicable value of curMRate, depending upon the date entered in a form control. 

    Let TxtDate be the name of a text box located on form F_Main. Sample code in AfterUpdate event of TxtDate as given below, would get the required value directly from the table and place it in target text box say TxtMRate.

' Code in form's module
'=============================
Private Sub TxtDate_AfterUpdate()
    Me.TxtMRate = DMax("CurMRate", _
            "tblMileageRate", "datDate <= #" & _
            Format([TxtDate], "mm/dd/yyyy") & "#")
End Sub
'=============================

    Note: 
    Formatting of TxtDate as "mm/dd/yyyy" before concatenation is meant to ensure consistent results even if the local settings for short date happen to be different from U.S. date format.

    If the same value were to be collected from a query similar to qryMileageByDate as included in your post, it would be necessary to incorporate form based parameters in such a query, as shown in sample SQL given below. In such a case, the statement in AfterUpdate event of TxtDate would be:

    Me.TxtMRate = DLookup("curMRate", "qryMileageByDate")

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

qryMileageByDate (Sample Select query)
==================================
SELECT TOP 1 curMRate  
FROM tblMileageRate  
WHERE datDate <= [Forms]![F_Main]![TxtDate]  
ORDER BY datDate DESC;
==================================

  ----- Original Message ----- 
  From: John Clark 
  To: Access Developers discussion and problem solving 
  Sent: Friday, December 18, 2009 00:16
  Subject: [AccessD] Try this one again


  OK...about a month ago, I asked for help on this, and I did get some...one that seemed really promising...but nothing worked. It has to be easy...something common. I was pulled away from this project, but now, w/the end of the year coming, they want it quick, so I've got a couple of days to finish it. I've already got an idea of how to avoid the problem all together, but it won't be the "proper" way to do it, IMHO. So, I'd like to complete it, how I want to do it.
   
  How do I get information from a query, into my form, and thus, if necessary, into my table?
   
  I've got a query, which I got help on, from you guys, that works great...as a query. Enter a date and you will get the single value for that date. The SQL for the query is:
   
  SELECT TOP 1 tblMileageRate.datDate, tblMileageRate.curMRate
  FROM tblMileageRate
  WHERE (((tblMileageRate.datDate)<[Date Entered]))
  ORDER BY tblMileageRate.datDate DESC;
   
  But, I've tried so many ways to do this right from the form, and I cannot get it to work. I looked into A.D.'s example, and I think it has gotten me close...I tried a statement like Me.curMRate.Value = "qryMileageByDate" but this isn't quite right either...says I can't assign a value to this object. 
   
  I've already started whipping up a substitute program that avoids any of this stuff, but it is not what I wanted. I want the user to be able to lookup a record and the rates for the date of that record will always be in there.
   
  Here is the really dumb thing about it...I've done this before. I wrote a program that did pretty much exactly what I am trying to do. But, that was 4-5 years ago, and I really haven't done much coding in that time.
   
  To put the question simply, how do I send a date to a query, from a form, and get back a rate based on that date...this would be the latest entry as of the entered date...basically what the code above does, but via the form?
   
  Thank you very much for all the help so far, by the way!
   
  John Clark
-- 
AccessD mailing list
AccessD at databaseadvisors.com 
http://databaseadvisors.com/mailman/listinfo/accessd 
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list