Unknown Error Popup

[jessabridge]

I have a simple query. I made a IIf statement that includes a Dlookup. Before I added the Dlookup, all was fine. I added it in & it works! Wonderful! But when I hit Run, I get the attached error message. I click OK and it goes away. It even gives me the correct data. I have Access 2010 since last week, from 2003, so i am adjusting. When I run a query that is linked to MMaxValue, I get that Unknown popup 3 times, same when I try to open the report. Numbers are correct but the Popup.

Here is the SQL:
SELECT [t Current Year PP Exception Hours].FY, Max([t Current Year PP Exception Hours].FYPP) AS MaxOfFYPP, Max([t Current Year PP Exception Hours].[PAY PER]) AS MMax, Nz(IIf([fy]=2015,[mmax]+26,DLookUp(" [SMax] ","MMaxValue","[FY] + 1 "))) AS SMax
FROM [t Current Year PP Exception Hours]
GROUP BY [t Current Year PP Exception Hours].FY
ORDER BY Max([t Current Year PP Exception Hours].[PAY PER]);


Any Ideas,
Jessa

 

[madefemere]

Hello,

I think the criteria used in the dlookUp is not correct :

DLookUp(" [SMax] ","MMaxValue","[FY] + 1 ")

What do you mean with "[FY] + 1 "

 

[plog]

Nz(IIf([fy]=2015,[mmax]+26,DLookUp(" [SMax] ","MMaxValue","[FY] + 1 "))) AS SMax

That's a whole lot of wrong in one expression:

1. I assume [mmax] isn't a field in [t Current Year PP Exception Hours], but is you're attempt to reference the [mmax] value in the query you are currently trying to execute. You can't do that. That value doesn't exist until you run the query, at which time it's too late to use it in that query.

2. There's really no reason to use a Dlookup in a query--instead you simply link the datasource you would Dlookup into and just use the value in that manner, not via lookup.

3. You haven't used criteria in the criteria section of your Dlookup. "[FY] +1" isn't an evaluation, its an operation. So using it in the criteria argument of Dlookup does nothing, or worse will always cause your Dlookup to fail.

4. In an aggregate query (one where you use a GROUP BY clause), every field in the SELECT clause that doesn't have an aggregate function applied to it (MAX, MIN, etc.) must appear in the GROUP BY clause. That means the value portion of the SMax field must appear in the GROUP BY clause, or be used in an aggregate function.

Instead of referencing your code and what it does/doesn't do versus your expectations, provide me with sample data. Give me what you are starting with and what you expect your query to produce based on that starting data. Be sure to include table and field names. Use this format for posting:

TableNameHere
Field1NameHere, Field2NameHere, Field3NameHere, ...
David, 12/2/2009, 46
Sally, 4/18/2010, 33
Larry, 3/3/2002, 68

 

[jessabridge]

Hello,

I think the criteria used in the dlookUp is not correct :

DLookUp(" [SMax] ","MMaxValue","[FY] + 1 ")

What do you mean with "[FY] + 1 "

I have two FY in my table. 2014 & 2015. 2014 has 26 PP's. 2015 has 5 as of right now but it changes. I want the SMax column to equal 31 so I can use that number to average the LWOP hours in the report. Next PP it will have to be 32 and so on. I was trying to use the Dlookup to say when it is FY = 2014, look it up like it is FY = 2014 +1...ie "2015".

 

[jessabridge]

1. I assume [mmax] isn't a field in [t Current Year PP Exception Hours], but is you're attempt to reference the [mmax] value in the query you are currently trying to execute. You can't do that. That value doesn't exist until you run the query, at which time it's too late to use it in that query.

Thanks!
I put the forumla in a new query:
SMax: (IIf([fy]=2015,[mmax]+26,DLookUp(" [MMax] + 26 ","MMaxValue","[FY] + 1 ")))

It works perfectly!
When I reread the expression this morning, I noticed I referenced the heading in the formula creating a circular reference. Silly! I put my new SMaxValue query where I needed it in my LWOP hours by PP query and it all works out w/o an error message. Thanks for your idea.
Jessa