First value below zero in a column

[JVMartin]

I have a query that (see SQL view below) where I need to return the column
heading ( or related date for that column heading) for the first field in a
list of 15 fields, that is below 1. I wrote a bunch of nested IIF
statements, but there are one too many columns and I'm getting the error
"Query is too complex" until I take out one of them. Is there any way to do
this..hopefully not in SQL or VBA?

SELECT AS_OF_ISSUE.DB, AS_OF_ISSUE.AS_OF_DATE, AS_OF_ISSUE.ISSUE_NAME,
AS_OF_ISSUE.SECURITY_ID, AS_OF_ISSUE.ISSUE_MAJOR_TYPE,
AS_OF_DETAIL.PORTFOLIO,
IIf([10]<#10/31/2008#,1,IIf([11]<1,#11/30/2008#,IIf([12]<1,#12/31/2007#,IIf([13]<1,#1/31/2008#,IIf([14]<1,#2/28/2008#,IIf([15]<1,#3/31/2008#,IIf([16]<1,#4/30/2008#,IIf([17]<1,#5/31/2008#,IIf([18]<1,#6/30/2008#,IIf([19]<1,#7/31/2008#,IIf([20]<1,#8/31/2008#,IIf([21]<1,#9/30/2008#,IIf([22]<1,#10/31/2008#,IIf([23]<1,#11/30/2008#,#12/31/3008#))))))))))))))
AS RunOffDate, CCoff200709_final.PORTFOLIO, CCoff200709_final.SEGMENT,
CCoff200709_final.CUSIP, CCoff200709_final.[10], CCoff200709_final.[11],
CCoff200709_final.[12], CCoff200709_final.[13], CCoff200709_final.[14],
CCoff200709_final.[15], CCoff200709_final.[16], CCoff200709_final.[17],
CCoff200709_final.[18], CCoff200709_final.[19], CCoff200709_final.[20],
CCoff200709_final.[21], CCoff200709_final.[22], CCoff200709_final.[23],
CCoff200709_final.[24]
FROM CCoff200709_final RIGHT JOIN (AS_OF_DETAIL RIGHT JOIN AS_OF_ISSUE ON
(AS_OF_DETAIL.SECURITY_ID_TYPE = AS_OF_ISSUE.SECURITY_ID_TYPE) AND
(AS_OF_DETAIL.SECURITY_ID = AS_OF_ISSUE.SECURITY_ID) AND
(AS_OF_DETAIL.AS_OF_DATE = AS_OF_ISSUE.AS_OF_DATE) AND (AS_OF_DETAIL.DB =
AS_OF_ISSUE.DB)) ON (CCoff200709_final.CUSIP = AS_OF_DETAIL.SECURITY_ID) AND
(CCoff200709_final.PORTFOLIO = AS_OF_DETAIL.PORTFOLIO)
WHERE (((AS_OF_ISSUE.DB)="PAM") AND ((AS_OF_ISSUE.AS_OF_DATE)=#9/30/2007#)
AND ((AS_OF_ISSUE.ISSUE_MAJOR_TYPE)="Asset Backed") AND
((AS_OF_DETAIL.PORTFOLIO)=20320));

 

[John Spencer]

I see no way to avoid using a custom VBA function.
Does the table that contains these misnamed fields 10 to 24(?) have a
primary key so you can identify the specific record.

UNTESTED VBA Function
Public Function fGetMagicDate(PKFromTable)
'Assumes that PK from table is one field and is numeric.
Dim strSQL As String
Dim rst As DAO.Recordset
Dim i As Long

strSQL = "SELECT [10], [11], ...,[24]" & _
" FROM [SomeTableName] " & _
" WHERE PrimaryKeyField = " & PKFromTable

For i = 0 To rst.Fields.Count - 1
If rst.Fields(i) < 1 Then
fGetMagicDate = DateSerial(2008, Val(rst.Fields(i).Name) + 1, 0)
Exit For
End If
Next i

End Function


In your query you could use
fGetMagicDate([TableName].[PrimaryKeyField])
to return the calculated date.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a query that (see SQL view below) where I need to return the column
heading ( or related date for that column heading) for the first field in
a
list of 15 fields, that is below 1. I wrote a bunch of nested IIF
statements, but there are one too many columns and I'm getting the error
"Query is too complex" until I take out one of them. Is there any way to
do
this..hopefully not in SQL or VBA?

SELECT AS_OF_ISSUE.DB, AS_OF_ISSUE.AS_OF_DATE, AS_OF_ISSUE.ISSUE_NAME,
AS_OF_ISSUE.SECURITY_ID, AS_OF_ISSUE.ISSUE_MAJOR_TYPE,
AS_OF_DETAIL.PORTFOLIO,
IIf([10]<#10/31/2008#,1,IIf([11]<1,#11/30/2008#,IIf([12]<1,#12/31/2007#,IIf([13]<1,#1/31/2008#,IIf([14]<1,#2/28/2008#,IIf([15]<1,#3/31/2008#,IIf([16]<1,#4/30/2008#,IIf([17]<1,#5/31/2008#,IIf([18]<1,#6/30/2008#,IIf([19]<1,#7/31/2008#,IIf([20]<1,#8/31/2008#,IIf([21]<1,#9/30/2008#,IIf([22]<1,#10/31/2008#,IIf([23]<1,#11/30/2008#,#12/31/3008#))))))))))))))
AS RunOffDate, CCoff200709_final.PORTFOLIO, CCoff200709_final.SEGMENT,
CCoff200709_final.CUSIP, CCoff200709_final.[10], CCoff200709_final.[11],
CCoff200709_final.[12], CCoff200709_final.[13], CCoff200709_final.[14],
CCoff200709_final.[15], CCoff200709_final.[16], CCoff200709_final.[17],
CCoff200709_final.[18], CCoff200709_final.[19], CCoff200709_final.[20],
CCoff200709_final.[21], CCoff200709_final.[22], CCoff200709_final.[23],
CCoff200709_final.[24]
FROM CCoff200709_final RIGHT JOIN (AS_OF_DETAIL RIGHT JOIN AS_OF_ISSUE ON
(AS_OF_DETAIL.SECURITY_ID_TYPE = AS_OF_ISSUE.SECURITY_ID_TYPE) AND
(AS_OF_DETAIL.SECURITY_ID = AS_OF_ISSUE.SECURITY_ID) AND
(AS_OF_DETAIL.AS_OF_DATE = AS_OF_ISSUE.AS_OF_DATE) AND (AS_OF_DETAIL.DB =
AS_OF_ISSUE.DB)) ON (CCoff200709_final.CUSIP = AS_OF_DETAIL.SECURITY_ID)
AND
(CCoff200709_final.PORTFOLIO = AS_OF_DETAIL.PORTFOLIO)
WHERE (((AS_OF_ISSUE.DB)="PAM") AND ((AS_OF_ISSUE.AS_OF_DATE)=#9/30/2007#)
AND ((AS_OF_ISSUE.ISSUE_MAJOR_TYPE)="Asset Backed") AND
((AS_OF_DETAIL.PORTFOLIO)=20320));