Strange form field bevavior

SteveL13
SteveL13 used Ask the Experts™
on
I have a continuous form that has a field that is acting strange.  There is one field in the form that only displays values when the scroll bar is clicked.  When it is not clicked the values disappear.

What would be causing this?  I have no formatting going on anywhere.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Incidentally, the field that is acting strange has this code:

Difference: (Nz([field1],0)+Nz([field2],0)+Nz([field3],0)+Nz([field4],0)+Nz([field5],0)+Nz([field6],0))-[field7]
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Try changing it to:

Difference: (Nz([field1],0)+Nz([field2],0)+Nz([field3],0)+Nz([field4],0)+Nz([field5],0)+Nz([field6],0))-NZ([field7], 0)

added an NZ( ) function around [Field7]

Author

Commented:
Same problem.   One field in the form that only displays values when the scroll bar is clicked.  When it is not clicked the values disappear.
Expert Spotlight: Joe Anderson (DatabaseMX)

We’ve posted a new Expert Spotlight!  Joe Anderson (DatabaseMX) has been on Experts Exchange since 2006. Learn more about this database architect, guitar aficionado, and Microsoft MVP.

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Have you checked to make sure that fields foreground and background color are not the same?

Author

Commented:
They are not the same.  Backcolor is white.  Forecolor is black.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Do you have a program like "SnagIt" or "Camtasia" which will allow you to record screen video?

If so, use that to record the situation you are encountering, then post it back here.
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
You could also post a sample database (just the relevant forms and tables, and any sensitive data removed).

Do the field1, field2, etc names refer to Textboxes/Other controls on your form, or do they refer to the underlying fields in your query or table?  (If textboxes/other controls, change it so that they reference the actual fields in the recordsource table or query)
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Also - a general debugging tip... Break the problem down.

Start with a simpler expression to see if any part of it works as expected, and then add to it until you find the breaking point.

ie: Start with this:
Nz([field1],0)

Open in new window

If that works, try:
Nz([field1],0)+Nz([field2],0)

Open in new window

Then:
Nz([field1],0)+Nz([field2],0)+Nz([field3],0)

Open in new window

etc...

Author

Commented:
MBizup:  I tried your suggestion and everything worked until I added the last field, -NZ([field7], 0).  Strange.  So then I tried something different.  Instead of using the linked Excel file, I created a make table query to use the Excel file and then used the table for the form instead of the linked Excel file.  IT WORKED!  What in the world?
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
See if the field7 part works on its own without the other fields.
It’s possible that reading from the Excel spreadsheet might be bogging things down to the extent that you’re seeing the strange behavior you’re describing.  Using a table would be a good bit more efficient.

Author

Commented:
It does work by itself.  Is a mystery.
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013

Commented:
Try removing the NZ functions in your expression, and just use the field names.  Do you actually have blanks in these fields?  I believe blanks in Excel are empty strings, not nulls (which is what the NZ function affects).

And as a side note, I generally do not use spreadsheets directly in queries, reports or forms.  Instead, I import the raw data into a staging table temporarily, and then format it as needed, moving it into into a permanent table, which is what I then use for queries, forms and reports.
Commented:
I created a make table query to use the Excel file and then used the table for the form instead of the linked Excel file.  IT WORKED!  What in the world?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
As Miriam mentioned, if you are dealing with linked Excel spreadsheets, you cannot be certain that a cell value is NULL when it appears to be.  It could be a zero length string, or even a string of spaces.

I have a function which I use for purposes like this which allows me to sum across rows (used when I receive non-normalized data)

Public Function fnSum(ParamArray ArrayOfValues() As Variant) As Variant

    Dim varSum As Variant
    Dim intLoop As Integer
          
    varSum = 0
    For intLoop = LBound(ArrayOfValues) To UBound(ArrayOfValues)
        If IsNull(ArrayOfValues(intLoop)) Then
            'do nothing
        ElseIf Trim(ArrayOfValues(intLoop) & "") = "" Then
            'do nothing
        ElseIf IsNumeric(ArrayOfValues(intLoop)) = False Then
            'do nothing
        Else
            varSum = varSum + Val(ArrayOfValues(intLoop))
        End If
    Next
    fnSum = varSum
          
End Function

Open in new window

Author

Commented:
Thank you Dale.  I will hang on to that code.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial