#error in text box fix

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • JackieSmith
    New Member
    • Jan 2011
    • 18

    #error in text box fix

    Hi,

    I have a db that is giving me a problem. I have a form which contains a sub-form that displays data that is filtered from combo boxes and option buttons. This all works fine.

    I now text boxes at the bottom of my main form which display certain data (count records in sub form, Average attendance, Average mark scored, and highest expense paid). These are all linked to text boxes in the footer of the sub form.

    I have got working the count records and average attendance however the remaining two boxes are stumping me. The average mark scored works however when there is nothing for it to avg it displays #error which I want to change to display 0. I have tried Nz and all versions on Null values to no avail.

    The current code in the text box is as follows:

    Code:
    =DLookUp("Grade","Mark Scheme","Start<=" & [front screen data subform].[Form]![Text41] & " And Finish<" & [front screen data subform].[Form]![Text41])
    Thanks
    Last edited by NeoPa; Apr 2 '11, 12:41 PM. Reason: Please use the CODE tags. They are mandatory on this site.
  • Stewart Ross
    Recognized Expert Moderator Specialist
    • Feb 2008
    • 2545

    #2
    #Error being returned when using domain aggregate functions like DLookup arise if any of the arguments supplied to the function are null. As the names of the table being looked up and the field to return are string constants in the statement you show, this leaves the third argument (the Where clause) as the potential problem.


    You are supplying the value of a textbox on your subform to the where clause ([Text41]). When you say 'when there is nothing for it to avg' do you mean that the subform has no records to show (i.e. [Text41] is null)?

    If so, I'd suggest using an IIF statement to test for this condition explicitly, replacing the control source property of the textbox with:

    Code:
    IIF(IsNull([front screen data subform].[Form]![Text41), Null, DLookUp("Grade","Mark Scheme","Start<=" & [front screen data subform].[Form]![Text41] & " And Finish<" & [front screen data subform].[Form]![Text41]))
    This just sets the lookup textbox to null if the subform textbox is null - i.e. there is no value to display. You can of course substitute any value you like in place of the null I've shown.

    -Stewart
    Last edited by Stewart Ross; Apr 2 '11, 09:39 AM.

    Comment

    • JackieSmith
      New Member
      • Jan 2011
      • 18

      #3
      Hi, Thanks for this but still needs tweaking. I had a look at the code and firstly used it as t was but this now no longer applies the DLookup.

      I have tried a few variations of the IIf code including changing null and also putting an IIf code in a new box and separating the DLookup and IIf code but this isnt working. Im not sure why. Any thoughts?

      Comment

      • NeoPa
        Recognized Expert Moderator MVP
        • Oct 2006
        • 32633

        #4
        General indications that something non-specific isn't working leave us next to nothing to work with. Please post what you tried and exactly what result ensued. Not working could mean any number of results including, but not limited to, No values returned; Incorrect values returned; Null results; Error results. Please take the time to communicate clearly what your situation(s) is/are as otherwise we are trying to cover every possible scenario, which wastes a lot of time.

        Comment

        • JackieSmith
          New Member
          • Jan 2011
          • 18

          #5
          Ok basically I am trying to combine a isnumeric code with a DLookup code in one go. This is what I have and is not working.

          Code:
           IIf(isnumericDLookUp("Grade","Mark Scheme","Start<=" & [front screen data subform].[Form]![Text41] & " And Finish<" & [front screen data subform].[Form]![Text41]),0)
          Basically the box should show a grade of A14 or such like however it is currently just showing a blank box. I am using expression builder here.

          I am using access 2007. I am using a DLookup to look up a number grade in a table and return the alpha-numeric grade it corresponds with.
          Last edited by JackieSmith; Apr 9 '11, 08:20 AM. Reason: Missing info

          Comment

          • Stewart Ross
            Recognized Expert Moderator Specialist
            • Feb 2008
            • 2545

            #6
            Oh dear. Did you compare what you actually put into the record source with what was suggested? Where did the IsNumeric come from? It is not correct syntax in any event, so this alone should be leaving you with another #Error in the text box:

            IIf(isnumericDLookup..

            The whole IIF is nothing like what I posted, which was

            Code:
            IIF(IsNull([front screen data subform].[Form]![Text41), Null, DLookUp("Grade","Mark Scheme","Start<=" & [front screen data subform].[Form]![Text41] & " And Finish<" & [front screen data subform].[Form]![Text41]))
            As I mentioned in post #2, if any of the arguments provided to a domain aggregate function are null the domain function will return an error value. The only argument I could see in what you posted which could potentially be null was the Text41 value from your subform control.

            In the example I posted, the IIf tests the value returned by the IsNull function contained as its first term. The IsNull tests the Text41 control value to see if it is a null. If Text41 is null then the whole IIF returns a null. Otherwise, the IIF returns the value of the DLookup, which you told us in post #1 works fine when there is something to average.

            What you used as the control source does not do that at all. Ignoring the IsNumeric, which is used incorrectly in any event, your IIF is not formed in such a way that any value can be returned from it. It seems to be amalgamating parts of an IsNull, incorrectly used, without realising that the IsNull cannot be applied to the DLookup as a whole - you would still be feeding a null argument to your DLookup, which will still return #Error regardless of being in an IsNull (incorrectly listed as IsNumeric and without its opening arguments bracket).

            -Stewart
            Last edited by Stewart Ross; Apr 9 '11, 10:11 AM.

            Comment

            • JackieSmith
              New Member
              • Jan 2011
              • 18

              #7
              Hi stewart,

              when I looked at the isNull I found that this is to replace blank data with a figure or letter. However this is not what I want. Basically all I want is when the text box isnt counting anything for it to be blank or display a 0.

              It will not be counting anything when the sub form is empty as no filters have been selected to allow data to be displayed.

              Everything I have read on Isnull says this is not the correct option for me. However as the text box I am referring to is a number and I am then trying to say if not in use then display 0, if in use then lookup the grade.

              The code you have provided does certainly give what I require in the box which is show blank however when I filter the data and expect the Dlookup to work this doesnt. No alpha numeric field displays as it should. However without the IIf statement and just the Dlookup it works fine except for the error.

              Hope this makes sense

              Comment

              • Stewart Ross
                Recognized Expert Moderator Specialist
                • Feb 2008
                • 2545

                #8
                There is a typo in my example, unfortunately (a missing closing bracket for the [Text41] control name within the IsNull). It should be:

                Code:
                IIF(IsNull([front screen data subform].[Form]![Text41]), Null, DLookUp("Grade","Mark Scheme","Start<=" & [front screen data subform].[Form]![Text41] & " And Finish<" & [front screen data subform].[Form]![Text41]))
                I come back to a question I asked back in post #2; what did you mean when you said there was 'nothing to average'? If the value of subform control [Text41] is null under those circumstances, then IsNull (or its SQL equivalent test, [your field] IS NULL) is the right test to apply within the IIF.

                I'd try it once again with the missing bracket that I accidentally left out restored to the correct position, and see what happens then.

                -Stewart

                Comment

                • JackieSmith
                  New Member
                  • Jan 2011
                  • 18

                  #9
                  Hi, Tried code but still shows blank when there should be a reading. I have uploaded a small version of the db so you can see. The box that is white is the problem box.

                  Basically there is a field in the footer of the sub form called average mark. This is the number grade. The Dlookup then converts this to alpha numeric like displayed in the actual sub form.

                  When I say there is nothing to count I mean when the sub form is empty as no data has been selected to view.

                  I hope this helps to clarify what I mean.

                  Thanks
                  Attached Files

                  Comment

                  • Stewart Ross
                    Recognized Expert Moderator Specialist
                    • Feb 2008
                    • 2545

                    #10
                    There was an '=' operator missing at the start of the control source expression, Jackie. THe full property that works for me on test is:

                    Code:
                    =IIf(IsNull([front screen data subform].Form!Text41),Null,DLookUp("Grade","Mark Scheme","Start<=" & [front screen data subform].Form!Text41 & " And Finish<" & [front screen data subform].Form!Text41))
                    This returns blank if the subform is empty, or the identifier of the average otherwise.

                    -Stewart

                    Comment

                    • JackieSmith
                      New Member
                      • Jan 2011
                      • 18

                      #11
                      Thanks. Finally ghot it working. Trust me to miss a small thing like that. Bit blonde at times. I am very grateful to you.

                      Comment

                      Working...