Access Crashes on DLOOKUP calculation

[neo651]

I have a form that contains some controls who's Control Sources are DLOOKUP statements. It seems that if the value being looked up is 0 Access crashes when it calculates that statement. I came across a couple other forums where someone described the same issue but they always resolved it by not allowing users to enter 0. I need my users to be able to enter that value so I can't use a workaround, I need to resolve the issue. Has anyone else encountered this and if so, have you been able to fix it?



Thank you.

[June7]

What do you mean 'crashes' - the database stops responding, you have to use TaskManager to kill the app?

Show the DLookup expression. Is the DLookup result used in a calcuation involving division by that result? Can't divide by 0, but shouldn't 'crash', just get error in the textbox.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Missinglinq]

And what kind of Access file is this; mdb/accdb or mde/accde?

Actually, I understand that inin mde/accde files, unhandled errors will, in fact, crash the app. This may be true in files executing under Access Runtime, as well.

Linq ;0)>

[neo651]

When it crashes I receive the error window saying "Microsoft Access has stopped working" and gives me the option to check online for a solution or close the program.

The DLOOKUP statements are very simply, they're just grabbing a piece of data from a query based on 2 criteria: <>=DLookUp("Stats_To_Date_2012","qryData","[Office]=* AND [Panel]=*")<>

And it's an .accdb file.

[June7]

Why the <> characters?

The asterisk (*) is a wildcard used with LIKE operator and needs text delimiters. Office and Panel must be text datatype fields.

=DLookUp("Stats_To_Date_2012","qryData","[Office] Like '*' AND [Panel] LIKE '*'")

This basically includes every record in the search except those where Office or Panel is null.

[neo651]

Sorry, the wildcards aren't actually used, I put them there because those values vary depending on where in the form they're used, in production there are numbers there.

[June7]

We need to know the exact syntax of the DLookup you are using, no dummy substitutions.

If you want to provide db for analysis, follow instructions at bottom of my post.

[neo651]

Well below is an example of one of the DLOOKUP statements. But the value for Office can range from 1-10 and the value for Panel can range from 1-16. But it doesn't seem to matter what the numbers are, if the value it points to is a 0 Access throws that error and closes.

=DLookUp("Stats_To_Date_2012","qryData","[Office]=1 AND [Panel]=1")

[June7]

If 0 is not a possible value, how can the expression encounter 0? If DLookup does not find record matching the criteria, the return is Null.

Viewed out of context of the full db, there is nothing wrong with that expression.
Only recourse left is to provide the db for analysis.

[neo651]

The problem isn't when the expression contains a 0, it's when the expression returns a 0 that the problem arises.

In any case, I've provided the front and back ends of the database here, please excuse the poor table structure.

In the front end file, under the category "Back End" in frmStats is where you'll find the DLOOKUP expressions. It's a form with a spreadsheet layout, almost all the controls in there have DLOOKUPs for their Control Source.Marketers' Database.zip

[June7]

Are you saying Stats_To_Date_2012 field could have 0 value? What I see in table is either a number or null. This DLookup cannot result in a 0 value. It is either a number or null. CStr() cannot handle Null, it will error. So handle possible null with Nz():

CStr(Nz(DLookUp("Best_Month_Stats","tbl_Best_Month s","[OfficeID]=1"),""))

Use & instead of + for concatenation operator, then won't matter what the datatype is and CStr and Nz won't be necessary. The + character is concatenation operator left over from old Basic language and it is also addition operator.

[neo651]

Yup, you nailed it. I implemented that fix and it worked. Thanks so much, really appreciate it. Out of curiosity, do you know why DLOOKUP can't return a 0? Is that a bug?

[June7]

I was saying this particular use of DLookup could not return 0 because Stats_To_Date_2012 field did not have 0 in any record. I didn't look at data in other fields.

If no record matches the criteria, DLookup will return null. This is the case with many of the domain aggregate functions.

[neo651]

Well there is a record for every criteria called in all those DLOOKUP statements, even if the record is blank, the issue only arises when a value of 0 is entered into the record and if I add a "CStr(NZ(" to the DLOOKUP statement it starts accepting 0s as a value without crashing.

[June7]

Zero is a totally acceptable result for domain aggregate functions, including DLookup. However, if the result is used in subsequent calculations there may be an issue. For one, can't divide by zero, however, shouldn't cause crash, except maybe in an accde as suggested by Linq.

Does the db still crash with 0 return after modifying expressions to use & concatenator without CStr and Nz?

I will look at the db some more tonight.