Access: Return closest Date problem

[E_DPSG]

Hi all -

I have a query that I am trying to return the closest matching date (but in the past) where I do not get a hit between the joins.

Something like this:

iif(A.DateVal is null, (Select Max(B.DateVal) From B Where (B.DateVal<=A.DateVal)),A.DateVal)

This returns a -1

What am I missing?

 

[Joe4]

Closest mathing date to what?
What if there are multiple records with the same date?
What if there are no earlier records?

It might be helpful if you post a small sample of your data and your expected results.

 

[E_DPSG]

Closest mathing date to what?
What if there are multiple records with the same date?
What if there are no earlier records?

It might be helpful if you post a small sample of your data and your expected results.

Okay. I have a scenario where Monday of this week was a bank holiday. I am dealing with rates issued by a bank. For example, If someone did business that involved a quote performed on Monday, the values quoted were generated on Friday, last week. Keeping up with this, The quote performed on 1.16.2012 pulls its values from Friday 1.13.2012. Because I do not want to maintain a lookup calendar, simply return the most current date applicable seemed like the best option.

to the multiple records, The table that contains the rate value I am seeking only contains a single record by day.

I believe I typed the wrong code in. maybe this would help:

iif(A.Rate is null, (Select (B.Rate) From B Where (max(B.DateVal)<=A.DateVal)),A.DateVal)

For no earlier records: The data sets that I am looking at are for the past 12 months. The source file containing the rates value date back to 2009, with a catch-all value of 1.1.1900 - So I think I have managed to prevent that from happening.

I am sure this is a simple little issue with my ordering but my mind is nearing burn-out. Joe - Any ideas?

 

[Joe4]

So what do your "A" and "B" tables represent?
What is the structure of each?

 

[E_DPSG]

A table represents customer information. B table holds the Rates issued on a specific day.

 

[E_DPSG]

I solved my issue. I created a function to pull in the available date.

 

[Joe4]

I'm sorry, I was away for a while and just started looking at this a few minutes ago. I was looking at some SQL code solutions, though it was getting a little tricky.

Out of curiosity, would you mind posting your function? I would love to see how you did it.

 

[E_DPSG]

Sure thing - Not the most efficient method as it still searches the entire file and assigns a value each time, but it works.

the ID was used for debugging puposes.

Option Compare Database

Function GetAvailDate(ByVal FileDate) As Date
    Dim ReturnDate As Date
    Dim BreakVal As Boolean
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim id As Integer
    
    Set db = CurrentDb()

                With db
                            .QueryDefs.Delete ("TestQry")
                            
                            Set qdfNew = .CreateQueryDef("TestQry", "Select KeyRates.Date " _
                                                    & "FROM KeyRates " _
                                                    & "Where keyrates.date is not null " _
                                                    & "ORDER BY KeyRates.Date;")
                End With
                
                Set rs1 = db.OpenRecordset("TestQry")
            
                If Nz(rs1.RecordCount, 0) > 0 Then
                    BreakVal = False
                    rs1.MoveFirst
                    id = 1
                    'rs1.MoveNext
                    Do While Not rs1.EOF
                        If BreakVal = False Then
                            If rs1![Date] <= FileDate Then
                                id = id + 1
                                ReturnDate = rs1![Date]
                            Else
                                id = id - 1
                                
                                BreakVal = True
                            End If
                        End If
                        rs1.MoveNext
                    Loop
                    End If
            Set rs1 = Nothing
    GetAvailDate = ReturnDate
End Function

 

[E_DPSG]

Cleaned up for performance reasons:

Option Compare Database

Function GetAvailDate(ByVal FileDate) As Date
    Dim ReturnDate As Date
    Dim BreakVal As Boolean
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    
    Set db = CurrentDb()
                With db
                            .QueryDefs.Delete ("TestQry")
                            Set qdfNew = .CreateQueryDef("TestQry", "Select KeyRates.Date " _
                                                    & "FROM KeyRates " _
                                                    & "Where keyrates.date is not null " _
                                                    & "ORDER BY KeyRates.Date;")
                End With
                Set rs1 = db.OpenRecordset("TestQry")
                If Nz(rs1.RecordCount, 0) > 0 Then
                    BreakVal = False
                    rs1.MoveFirst
                    Do While Not rs1.EOF And BreakVal = False
                        If BreakVal = False Then
                            If rs1![Date] <= FileDate Then
                                ReturnDate = rs1![Date]
                            Else
                                BreakVal = True
                            End If
                        End If
                        rs1.MoveNext
                    Loop
                    End If
            Set rs1 = Nothing
    GetAvailDate = ReturnDate
End Function

Hope this helps someone.