469,271 Members | 1,755 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,271 developers. It's quick & easy.

#error in text box fix


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:

Expand|Select|Wrap|Line Numbers
  1. =DLookUp("Grade","Mark Scheme","Start<=" & [front screen data subform].[Form]![Text41] & " And Finish<" & [front screen data subform].[Form]![Text41])
Apr 1 '11 #1

✓ answered by Stewart Ross

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

Expand|Select|Wrap|Line Numbers
  1. =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.


10 10772
Stewart Ross
2,545 Expert Mod 2GB
#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:

Expand|Select|Wrap|Line Numbers
  1. 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.

Apr 2 '11 #2
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?
Apr 2 '11 #3
32,171 Expert Mod 16PB
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.
Apr 2 '11 #4
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.

Expand|Select|Wrap|Line Numbers
  1.  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.
Apr 9 '11 #5
Stewart Ross
2,545 Expert Mod 2GB
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:


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

Expand|Select|Wrap|Line Numbers
  1. 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).

Apr 9 '11 #6
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
Apr 9 '11 #7
Stewart Ross
2,545 Expert Mod 2GB
There is a typo in my example, unfortunately (a missing closing bracket for the [Text41] control name within the IsNull). It should be:

Expand|Select|Wrap|Line Numbers
  1. 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.

Apr 9 '11 #8
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.

Attached Files
File Type: zip test.zip (93.5 KB, 202 views)
Apr 10 '11 #9
Stewart Ross
2,545 Expert Mod 2GB
There was an '=' operator missing at the start of the control source expression, Jackie. THe full property that works for me on test is:

Expand|Select|Wrap|Line Numbers
  1. =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.

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

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Tosch | last post: by
3 posts views Thread by Peter A. Schott | last post: by
13 posts views Thread by albert_reade | last post: by
2 posts views Thread by Mike N. | last post: by
5 posts views Thread by Johannes Bauer | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.