I have three textbox and two of them are text and the third add up what was entered based on the 2 other text. I'm trying to get the third text to sum the values entered for site name and station name
I have three textbox and two of them are text and the third add up what was entered based on the 2 other text. I'm trying to get the third text to sum the values entered for site name and station name
Welcome to the site. Your image didn't get attached properly.
Here is the code in expression builder =IIf(Val([Site Name].[Text]) And Val([Station Name].[Text])LIKE([Data Collection] Sum([Data Collection].[Liquid Amount Gallons]))
LIKE without wildcard should probably just be = sign.
You probably want to use & for concatenation, not AND. Don't use Text property. Applying VAL function to alpha data makes no sense. The expression as a whole makes no sense. Probably want syntax like:
=Sum(IIf(some expression here, [Liquid Amount Gallons], Null))
Show some sample data and desired result.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Try without the .Text property. You can only use that if the control has focus. Plus you seem to be missing commas. The structure of an IIf() is:
IIf(Test resulting in true or false, what to do if true, what to do if false)
You don't appear to have a valid test, nor the true and false arguments. In words, how would you complete the formula using the structure above?
Do yourself a favor and DO NOT use spaces in object names.
Object names include Field names, Table names, Query names, Form names and Report names.
Examples
Current
Better (uses underscore)
Best (IMHO)
[Site Name] [Site_Name] [SiteName] [Station Name] [Station_Name] [StationName] [Data Collection] [Data_Collection] [DataCollection] [Liquid Amount Gallons] [Liquid Amount_Gallons] [LiquidAmountGallons]
Let's try to analyze your formula.
=IIF(Condition, ExpressionWhenTrue, ExpressionWhenFalse)
1. Obviously, you missed a delimiter before SUM(), which logicallly must be ExpressionWhenTrue;
2. ExpressionWhenFalse is missing, so in case Condition is FALSE, FALSE is returned;
3. ExpressionWhenTrue returns SUM of field [Liquid Amount Gallons] of all rows in table [Data Collection]. I.e. there must be such table!;
4. Condition is a logical expression like (Control1 AND Control2 Like Control3), where Control1 = [Site Name], Control2 = [Station Name], and Control3 = [Data Collection];
5. The expression Control1 AND Control 2 works only, when both controls are linked to TRUE/FALSE fields (in this case it is a regular logical expression), or numeric fields (in this case the expression returns TRUE when both values are equal, and FALSE otherwise). When those fields are text, an error is returned;
6. The expression (Control1 AND Control2) LIKE Control3 assumes, that the control [Data Collection] is also linked to either TRUE/FALSE or numeric field;
7. When 5. and 6. aren't case, and controls [Site Name], [Station Name], and [Data Collection] are text values, the current formula can't work. And another problem is at hand:
a) This indicates, that you don't have really a single table [Data Collection] present - probably you don't have such table at all. Instead you have a lot of tables with similar structure - a table for every value in field linked to control [Data Collection]. When this is the case, this is a FAT NO!
Last edited by ArviLaanemets; 05-18-2022 at 05:21 AM.
That got me 90 percent there, but I want the liquid value based on the site name and station name. If I type in test1 and storage it should be 45, but the liquid amount is adding all data see the attachedLIKE without wildcard should probably just be = sign.
You probably want to use & for concatenation, not AND. Don't use Text property. Applying VAL function to alpha data makes no sense. The expression as a whole makes no sense. Probably want syntax like:
=Sum(IIf(some expression here, [Liquid Amount Gallons], Null))
Show some sample data and desired result.
Can you see it now
Please use # icon on toolbar when posting code snippets.
Cross Posting: https://www.excelguru.ca/content.php?184
Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
Look at the form that says Report center. Every site is suppose to have a different name and different kinds of Station names. Instead the Liquid Amount is giving me the total of everything and also, when I try to clear the filter the liquid amount values stays. I get an error in VBA saying I can't attach an object to that field.
Did you have the chance to look at it
Why isn't control for Site Name also a combobox?
Why is RecordSource a GROUP BY query when there is no aggregate calc?
Since purpose of form is to select filter criteria why even bother with showing gallons?
Since this form is not displaying data, why even bind it to table/query?
This Sum expression is useless mostly because the IIf depends on fields of form RecordSource and the record that has focus never changes.
I cannot get Sum to work with IIf condition at all, however, a DSum will work. Rename controls to tbxSite and cbxStation.
=DSum("[Liquid Amount Gallons]","[Data Collection]","[Site Name]='" & [tbxSite] & "' And [Station Name]='" & [cbxStation] & "'")
There is no need to bind form to dataset.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Worked like a charm. Thank you so much!Why isn't control for Site Name also a combobox?
Why is RecordSource a GROUP BY query when there is no aggregate calc?
Since purpose of form is to select filter criteria why even bother with showing gallons?
Since this form is not displaying data, why even bind it to table/query?
This Sum expression is useless mostly because the IIf depends on fields of form RecordSource and the record that has focus never changes.
I cannot get Sum to work with IIf condition at all, however, a DSum will work. Rename controls to tbxSite and cbxStation.
=DSum("[Liquid Amount Gallons]","[Data Collection]","[Site Name]='" & [tbxSite] & "' And [Station Name]='" & [cbxStation] & "'")
There is no need to bind form to dataset.