Strange form field bevavior

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.
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveL13Author 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, Developing Solutions LLCCommented:
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]
SteveL13Author 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.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Dale FyeOwner, Developing Solutions LLCCommented:
Have you checked to make sure that fields foreground and background color are not the same?
SteveL13Author Commented:
They are not the same.  Backcolor is white.  Forecolor is black.
Dale FyeOwner, Developing Solutions LLCCommented:
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.
mbizupCommented:
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)
mbizupCommented:
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...
SteveL13Author 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?
mbizupCommented:
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.
SteveL13Author Commented:
It does work by itself.  Is a mystery.
mbizupCommented:
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.
SteveL13Author 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?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
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

SteveL13Author Commented:
Thank you Dale.  I will hang on to that code.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.