Access: Return closest Date problem

E_DPSG

Board Regular
Joined
Jul 17, 2008
Messages
82
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?
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,465
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jul 17, 2008
Messages
82
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,465
Office Version
365
Platform
Windows
So what do your "A" and "B" tables represent?
What is the structure of each?
 

E_DPSG

Board Regular
Joined
Jul 17, 2008
Messages
82
A table represents customer information. B table holds the Rates issued on a specific day.
 

E_DPSG

Board Regular
Joined
Jul 17, 2008
Messages
82
I solved my issue. I created a function to pull in the available date.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,465
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jul 17, 2008
Messages
82
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.

Code:
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
:cool:
 

E_DPSG

Board Regular
Joined
Jul 17, 2008
Messages
82
Cleaned up for performance reasons:

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,099,681
Messages
5,470,101
Members
406,681
Latest member
sachinmasurkar

This Week's Hot Topics

Top