Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2009

    Unanswered: Need help with Access Query

    I am trying to write a query that returns records within a range based off a calculation. Let me explain.

    Name Record # Cumulative %
    Bob 1 100
    Ralph 2 98.48
    Fred 3 82.81
    George 4 78.54
    Jim 5 74.53
    John 12 57.47

    Ok, I base my calculation off of Jim. The calculation is Cumulative % * 1.3 to get the upper end of the range, and Cumulative % *.77 to get the lower end of the range. I want the query to return all records that fall within those calculations. Basically the records returned would be Record # 3 to record # 12.

    Is there a way to write the query to do this, and would it be possible to be able to select the record and have the calculation be done upon selection?


  2. #2
    Join Date
    Aug 2009
    Up Nort' Wi
    I'm assuming by selection you mean a combobox or something along those lines and you are coding this behind a form in vba. This is nothing but pseudo code, I can't even remember at this point of the day whether SQL will even play nicely with fractions or if it'll round 'em up...that being said, I think the easiest way would be to do something like:

    Private Sub cmd_Get_Range_OnClick()
    	dim cu_Perc as single 
    	dim u_lim as single
    	dim l_lim as single 
    	dim name as string
    	dim rawSQL as string
      	dim SQL as string
      	dim db as database
    	dim rs as recordset
    	name = cbo_name.**
    	set db = currentdb
    	set rs = db.openrecordset ("TableName")
    	if not rs.bof and not rs.eof then
    		do while not rs.eof
    			if rs!name = name then
    				cu_perc = rs!Cumulative
    				exit do
    	u_lim = cu_perc * 1.3
    	l_lim = cu_perc * .77
      	rawSQL = "SELECT * FROM TableName WHERE Cumulative BETWEEN '" &l_lim &"' AND '" &u_lim &"';"
    	SQL = rawSQL
    	docmd.openreport "Report_Name", acViewPreview, SQL
    End Sub
    ** Can use either .value if you want to search off the ID, or .text if you want to search off text shown in the combobox...if doing .text then don't forget to set the focus on the combobox before filling the variable

    Hopefully someone out there has a more elegant method than I've offered, there's probably a shortcut out there for SQL to dish out percentages without having to manually set variables.


    Good, fast, cheap...Pick 2.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts