Blank date field = zero results 1

[Trudye]

I noticed a problem in my query results when a null/blank field exits. For instance I am selecting MAX date and if there are 5 results and one of the 5 recs has a blank date NONE are selected. How can I get SQL to ingore the blank date field and return the appropriate value?

Thanks much,
Trudye


strSQL = "SELECT MAX(Deals.From) as MaxDate, [Deal Detail].Item, [Deal Detail].COST " _
& "FROM (Clients " _
& "INNER JOIN Deals on Clients.ID = Deals.ClientId) " _
& "INNER JOIN [Deal Detail] on Deals.Dealid = [Deal Detail].Dealid " _
& "WHERE Clientid = " & sID & " AND Item = '16x20 Photo'" _
& " GROUP BY [Deal Detail].Item, Clients.Id, [Deal Detail].COST"
Set rst = db1.OpenRecordset(strSQL)
If rst.RecordCount > 0 Then
sItem = rst!Item
If IsDate(rst!MaxDate) Then
sMaxFrom = rst!MaxDate
End If
sMaxRate = rst!COST
GoSub Write_Recs
End If

 

[PHV]

Have you tried this ?
SELECT MAX(Nz(Deals.From,0))
Hopefully Deals.From is a true DateTime field ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Trudye]

Thanks PHV, I had it backwards. I had the NZ func outside the MAX func.


Be well,
TEN

 

[Trudye]

PHV, it seems SQL does not like your version either.
Erro msg Syntax err in string in query expression.

TEN

 

[PHV]

What is the data type of Deals.From ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Trudye]

Date Type = date