avg/min/max data help needed badly

[donavan01]

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

=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

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!

[weekend00]

try:
=DAvg("Field1",[Table],"[Table]![Field2]='" & [Forms]![InputBox1]![Combobox1] & "'")

[donavan01]

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?

[donavan01]

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

[weekend00]

it is correct if field2 is a text field.
of course you can replace all the ' with "" if you like.

[donavan01]

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.

=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

[donavan01]

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.

=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.

[donavan01]

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.