Results 1 to 8 of 8

avg/min/max data help needed badly

  1. #1
    donavan01 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    8

    Unhappy avg/min/max data help needed badly


    I am sorry if this has been answered before or if it seems like an overly simple question, but I have been searching for hours for an answer to this. I need to display either a form or report, I don't really care which, that will have a text box that will display the average (or minimum/maximum) for a given field inside of a table. However I do not want all the records just the ones the meet my criteria I have tried the following and nothing seems to work.

    1 text box with DAvg

    Code:
    =DAvg("Field1",[Table],[Table]![Field2]=[Forms]![InputBox1]![Combobox1])
    This gives me an #name? error.

    2 I have tried doing an SQL query and have the control source point to that and I can get all sorts of errors here depending on how I go about it.

    my exact SQL code as of now
    Code:
    SELECT AVG(Archive.Moist_AR) AS Moist_Avg
    FROM Archive
    WHERE (((Archive.Sample_Location)=Forms!InputBox_Stats_Moisture!Location) AND((Archive.Customer_Name)=Forms!InputBox_Stats_Moisture!Customer));
    I do not want to list any of the records just the averages of them.

    I have googled my brains out and have no idea what is going on with it, Please help me!

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    try:
    =DAvg("Field1",[Table],"[Table]![Field2]='" & [Forms]![InputBox1]![Combobox1] & "'")

  3. #3
    donavan01 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    8
    Thanks for the quick reply but there seems to be a little issues with the quotes you are using for your answer you have some " and a ' in there that doesnt seem to be correct can you check it again?

  4. #4
    donavan01 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    8
    SELECT AVG(Archive.Moist_AR) AS Moist_AR_Avg
    FROM Archive
    WHERE (((Archive.Sample_Location)=Forms!InputBox_Stats_M oisture!Location) And ((Archive.Cusomer_Name)=Forms!InputBox_Stats_Moist ure!Customer));


    This code works perfectly when run by itself but the second I try to set it as that control source

    =[Stats_Moist_AR_AVG]![Moist_AR_Avg]

    it totally craps out what am I doing wrong

  5. #5
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    it is correct if field2 is a text field.
    of course you can replace all the ' with "" if you like.

  6. #6
    donavan01 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    8

    Talking

    Weelend00 sorry, my bad on the ' and " issue the font I have made it a little hard to see and my eyes tricked me.



    I have got the code working for anyone that may run into this issue in the future here is the correct syntax and an explanation.


    Code:
    =DAvg("Field1","Table1","[Table1]![Field2]='" & [Forms]![Input_Form1]![ComboBox1] & "'")
    Field1 - This is the field I am trying to average
    Field2 - This is the criteria I am searching by (limiting factor)
    Table1 - This is the table everything gets pulled from
    Input_Form1 - This is a seperate form to retrieve user input
    ComboBox1 - Combo box on Input_Form1


    The same code above will work for min/max by changing the DAvg to DMin/DMax


    This may have been simple but I literally looked for hours for an answer to this question so I figured I would document this well now that I found the answer

  7. #7
    donavan01 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    8
    OK so after getting this to work I realized I need it to be more complex than it is. I need this same code to allow for the selection of a date range from a stored value. However when I try what I think would be correct I get a number back that isn't correct at all.

    Code:
    =DAvg("Moist_AR","Archive","[Archive]![Sample_Location]='" & [Forms]![InputBox_Stats_Location_Customer]![Combo_Location] & "'" & " AND " & "[Archive]![Customer_Name]='" & [Forms]![InputBox_Stats_Location_Customer]![Combo_Customer] & "'" & " AND " & "[Archive]![Date_Sampled] BETWEEN '" & [Forms]![InputBox_Stats_Location_Customer]![txtDate_Start] AND [Forms]![InputBox_Stats_Location_Customer]![txtDate_End] & "'")
    I'm not sure if you can even do a BETWEEN here if so please help if not please tell me how I can do the date select along with the code above that does work.

  8. #8
    donavan01 is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    8

    Thumbs up Solved for now at least

    OK so after some help from HansV MVP over at social.answers.microsoft.com I now have a working piece of code. Im not going to detail this bit since its not my creation totally but you can check the discussion out if you like after the code.

    =DAvg("Moist_AR","Archive","[Archive]![Sample_Location]='" & [Forms]![InputBox_Stats_Location_Customer]![Combo_Location] & "' AND [Archive]![Customer_Name]='" & [Forms]![InputBox_Stats_Location_Customer]![Combo_Customer] & "' AND [Archive]![Date_Sampled] BETWEEN #" & Format([Forms]![InputBox_Stats_Location_Customer]![txtDate_Start],"mm\/dd\/yyyy") & "# AND #" & Format([Forms]![InputBox_Stats_Location_Customer]![txtDate_End],"mm\/dd\/yyyy") & "#")

    http://social.answers.microsoft.com/...b-f55c107bb068


    Thanks for the help everyone one and I hope this helps someone else along the way.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-19-2011, 01:53 PM
  2. Using composite keys but stuck badly in the design
    By hmushtaq in forum Database Design
    Replies: 2
    Last Post: 01-25-2011, 12:25 AM
  3. Best way to import badly formatted data
    By mkallover in forum Import/Export Data
    Replies: 3
    Last Post: 09-16-2010, 01:59 PM
  4. Help needed...
    By Pazz in forum Access
    Replies: 0
    Last Post: 11-02-2009, 06:59 PM
  5. help required badly
    By pradeep_siemens in forum Programming
    Replies: 0
    Last Post: 05-28-2009, 04:41 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums