Text box with nz() very slow

[darlingm]

At the moment, I have a very simple Access 2007 database with 3 small tables, and 4 small forms.

In two of the forms' headers, I have a text box with contents '=Nz([FirstName] & " " & [MiddleName] & " " & [LastName],"Untitled")' which I took from Microsoft's Marketing Projects sample database.

When I open the two forms with this text box, the forms displays instantaneously except for this text box -- this text box doesn't show its contents for almost a full second. The part of the form that displays right away includes the fields FirstName, MiddleName, and LastName, so it's not that Access is taking forever to pull the row of data...

The database isn't too large. It's about 800K.

It isn't the computer, it's an Intel Core2 Duo E6600 with 4GB memory.

Any ideas on how to speed this up? Is there a totally different way I should be doing this?


As you can problaby tell, I'm relatively new to Access. I've read a few books, but am at a loss why this function is running so slowly. Before, I've focused on web development however this client requres Access for this project. Any help would be much appreciated!

 

[Uncle Gizmo]

You could try having the form "hidden" and then "unhiding" it when you want to see it.

 

[Moniker]

Also, unless Nz works different in 2007, this will never return the word "Untitled":

=Nz([FirstName] & " " & [MiddleName] & " " & [LastName],"Untitled")

You have two spaces in there, so the value will never be NULL, and the word "Untitled" will never be returned. In order for it to work, you'd have to change it to this:

=Nz(Trim([FirstName] & " " & [MiddleName] & " " & [LastName]),"Untitled")'

 

[ChrisO]

Are you sure that is what Microsoft wrote?

I think it should have been: -

=Nz([FirstName]+" " & [MiddleName]+" " & [LastName],"Untitled")

in order to propagate the Nulls so that when all three are Null it would return > Untitled <

Regards,
Chris.

 

[Moniker]

Actually, Chris, you don't want to use a plus sign to concatenate strings like that. There's still the problem of the spaces as well.

 

[ChrisO]

Actually, Moniker I do want to use the plus sign like that because if the proceeding field is Null then adding the Space to the Null produces Null.

So if [FirstName] is Null then [FirstName] + " " is also Null.

Therefore if all three are Null then: -

[FirstName] + " " & [MiddleName] + " " & [LastName]

returns Null and the Nz function returns Untitled

Test it, I did before posting

 

[Moniker]

Tested. And denied.

In the immediate window, a non-declared variable (essentially a NULL) + " " produces a space. In code, a NULL + " " produces nothing.

It was tested, but with different results. You learn something new everyday.

 

[ChrisO]

Testing demo attached.

 

[pbaldy]

I was busy doing my own test so I haven't looked at Chris', but in general I agree with Chris. The + propagates nulls, & does not. Thus a Null field + " " would yield a Null.

 

[Moniker]

Good to see we all have extremely exciting Monday nights.

 

[ChrisO]

Tuesday midday’s.

 

[darlingm]

Yikes, that's embarrising. Thanks for all the responses.

Microsoft wrote "=Nz([Employee Name],"Untitled")" at the top of one of their forms. I need the name to be split into first, middle, and last, so I changed the function and neglected the spaces causing "Untitled" to never be returned.

Besides that issue, does it make sense to everyone that such a simple embedded function would take so long to execute and display? Microsoft's sample database has a decent delay to it as well, but I can't tell if the delay in my database is worse than theirs.

I might be wrong, but I don't think the hide/unhide form trick will work, because there are so many records, that I think once the user switched to a different record, the delay would happen then -- which would be just as bad -- just not on the initial form load.

 

[Moniker]

How many records are we talking? Thousands and thousands? If that's the case, then yes, the NZ will take a little bit of time. Have you tried basing the form on a query and running the NZ in the query?

 

[darlingm]

How many records are we talking? Thousands and thousands? If that's the case, then yes, the NZ will take a little bit of time. Have you tried basing the form on a query and running the NZ in the query?

Thanks! Splitting it into a seperate query makes it act instantly, and updates the combined names header at the top as soon as a name is edited. I don't have to create on update events now, either.

The size of my database is what was worrying me so much about NZ. There's a whopping 1-5 rows in the table, right now for testing purposes, and only about 10 fields, none of which are holding anything large.

The database was even compacted.

Any ideas why NZ would run so slow embedded in the form, but instantly in a query? Does this mean I should never embed much in forms, and instead always base on queries -- if there's more than just the raw fields?

I appreciate all of your help. I have a lot of programming/database experience, but it's all been on the Unix side. I have a client who's requiring it to be done in access, so I'm scrambling a bit.

 

[Moniker]

Almost all forms should be based on queries of some sort, even if it's just a select * on a table. That's not 100% all of the time, but when using calculated controls on a field, the query is always faster than the formula in the form. This is because the query prepopulates the data for you.

It's pretty much what Gizmo was getting at: Hide everything until the calculations are done, and then show it once the calculations are done. Much like most DBs (at least Access, DB2, and SQL Server), Access will do the first few hundred records instantly and then continues to do the other records in the background while you peruse the first few hundred records. For example, if you have a table with 150,000 records, and you open it and instantly demand to go to the very last record, it's going to take the DB engine a bit to catch up to that request.

In your case, with so few records, it's just doing them all at once. The difference is that the form is acting like a middleman of sorts. The form says, "Hey, if I combine all these fields, is it null?" Then, the code says, "Where are these fields?" and it goes and gets the one record you specified, and then performs the Nz on it. The function (Nz) isn't the slow part here; it's the retrieval of the fields to perform Nz on. Putting it in the query cuts out the middleman, as it were, as the function is acting on controls (fields in this case) in the same object (the query), whereas the form has to get those values.