IIF issue.. I fear I may know the answer..

[boostbyslinky]

First Receipt : IIf(Min([dbo_ReportData].[Received Date])="",[Liveval].[Received Date],Min([dbo_ReportData].[Received Date]))

I'm guessing that IIF doesn't play that way? Well, I know it doesn't, I'm getting aggregate errors when attempting to run the query..

I need to pull a value from one table (dbo_reportdata), but if that value doesn't exist, or returns a null, then pull the equivalent value from liveval...

Any thoughts?

 

[boblarson]

It isn't that, it is that you are using the wrong criteria for a date field. If it is a date field that is and not text.

You can't use
IIf(Min([dbo_ReportData].[Received Date])=""

On a Date Field, and especially with MIN. You would use

IIf(Min(Nz([dbo_ReportData].[Received Date],0))= 0,

But if you were not using MIN and just to check a date field it would be

IIf([FieldNameHere] Is Null, ...etc.

 

[boostbyslinky]

Thanks for the reply Bob..

I think I must be doing something wrong at a more fundamental level.

When attempting to use my code (with your suggested amendments) within a query, if that query element is set as "group by" I get:

Cannot have aggregate function in Group By clause:

Switching it to an expression leaves me with:

You tried to execute a query that does not include the specified expression "......" as part of an aggregate function..

The (very simply) test query is as follows at present..

SELECT Liveval.Docket, Liveval.[Job No], IIf(Min(Nz([dbo_ReportData].[Received Date],0))=0,[Liveval].[Received Date],Min([dbo_ReportData].[Received Date])) AS [First Receipt]
FROM Liveval LEFT JOIN dbo_ReportData ON Liveval.[Job No] = dbo_ReportData.[Job No]
GROUP BY Liveval.Docket, Liveval.[Job No];

Whilst it isn't a deal breaker to have this function, it brings a substantial aesthetic benefit to the intended query output, so it'd be really nice to get it to work..

TIA..

 

[boostbyslinky]

Sorry to bounce this, but I'm still trying to work out how to do it!
Any thoughts gratefully received..

 

[gemma-the-husky]

maybe you need to look at the overall design of this query and indeed the database.

you have a row in a query, and now you want to find some data relative to that row.

so - how are the relevant tables structured, and why are there two potential fields. generally a query that for each row needs to examine two related aggregate queries is going to be very slow, unless you are only running it for a single record at a time.

can you clarify what is going on?

 

[boostbyslinky]

believe me, a database re-design/rebuild would be lovely, but, as per most solutions, this solution doesn't exist in ideal circumstances..

My DB has an ODBC hook to a SQL database holding the table dbo_reportdata, this data is historical records from our call logging/CRM system.
There is then a local table (liveval) which holds records from the live side of the CRM.. (populated via a text file export/import.. )

A call has a lifecycle "docket" reference, my query is attempting to pull together information from Liveval alongside historical data from dbo_reportdata, using that docket (or ID if you like) as the primary relationship driver..

If a call is brand new, it has no historical data, so I would want to display the received date field from Liveval, if a call has some history I would want to display the minimum date from the received date field in dbo_reportdata..

I hope that makes things a little clearer? If not, please feel free to ask more questions..
If the end result isn't achievable, it's not the end of the world, but I'd rather like to get it working if possible..

Cheers..

 

[tehNellie]

Perhaps if you shuffle your base query around a little bit?

SELECT Liveval.Docket,
Liveval.[Job No], 
NZ(mindate.[ReceivedDate], [Liveval].[ReceivedDate]) AS [FirstReceipt]
FROM Liveval 
LEFT JOIN 
(
	SELECT jobNo, MIN(ReceivedDate) AS RecievedDate
	FROM dbo_reportData
	GROUP BY JobNo
)as MinDate ON 
  Liveval.[JobNo] = MinDate.[JobNo]

So just take the Min Recieved date for each job no from the ReportData table to begin with. And do a NULL replacement on the mindate.Receiveddate to substitute the liveval.receivedDate instead?

 

[boostbyslinky]

Morning...

After a bit of digging around on Friday I managed to resolve the issue.. It would seem that I just needed NZ, but was approaching it in the wrong way.. I spend a lot of time in Excel, so was expecting an "expression, result if true, result if false" style syntax, needless to say, that doesn't work with NZ!

So, after getting my head around that, the answer is actually really simple..

First Receipt: Nz(Min([dbo_ReportData].[Received Date]),[liveval].[Received Date])

Taaa Daaa!

Thanks for all of your help and steering in the right direction..