MS Access: CurrentDb.OpenRecordset return multiple values

Question

I have two tables in Access; Table1 and Table2, Table2 contains an FX Rate used in a calculation in Table1. These tables would be joined on a currency field and a date. However if table2 does not have the relevant FX Rates associated with the date from Table1 the calculation will be incorrect.

I want to display a message box letting the user know this. I have tried the following code:

'Define variables
Dim msgText As String

msgText = CurrentDb.OpenRecordset("SELECT Table1.Date_of_Report, Table2.Date_of_Report FROM Table1 LEFT JOIN Table2 ON Table1.Date_of_Report = Table2.Date_of_Report GROUP BY Table1.Date_of_Report, Table2.Date_of_Report HAVING Table2.Date_of_Report Is Null ")(0).Value
      
MsgBox (msgText)

The above code works fine when there is only one instance with a blank date in the Table2 as it is only returning the first value. I have tried updating it ad in an additional line and change the (0) to a (1) but I get an error.

Is there a way to return a list of where all dates are blank in Table2 and display this as a message to the user?

Thanks in advance,

Answer 1

I use simple helper functions for this.

Open a recordset with your SQL, and do msgText = Rs2String(RS).

' Return all rows (column iColumn) of Recordset RS in a string, separated by sSeparator
Public Function Rs2String(RS As Recordset, Optional iColumn As Long = 0, Optional sSeparator As String = vbCrLf) As String

    Dim S As String
    
    S = ""
    Do While Not RS.EOF
        S = StrAppend(S, CStr(Nz(RS(iColumn), "")), sSeparator)
        RS.MoveNext
    Loop
    
    Rs2String = S
    
End Function


' Append sAppend to sBase, use sSeparator if sBase wasn't empty
Public Function StrAppend(sBase As String, sAppend As Variant, sSeparator As String) As String

    If Len(sAppend) > 0 Then
        If sBase = "" Then
            StrAppend = Nz(sAppend, "")
        Else
            StrAppend = sBase & sSeparator & Nz(sAppend, "")
        End If
    Else
        StrAppend = sBase
    End If
    
End Function

Answer 2

I managed to do it a roundabout way and I've included my code below incase anyone else has a similar issue. My code will probably only work for smaller datasets due to concatenating the strings together. However it works for me as I know there will only be a few dates in each table.

Dim RS As Recordset
Dim msgString As String
Dim vMsg As String

msgString = ""


Set RS = CurrentDb.OpenRecordset("SELECT Table1.Date AS [Date], Table2.Date AS [Date_2] FROM Table1 LEFT JOIN Table2 ON Table1.Date = Table2.Date GROUP BY Table1.Date, Table2.Date HAVING Table2.Date Is Null ")

If RS.RecordCount > 0 Then

RS.MoveFirst 
While Not (RS.EOF)
msgString = "- " & RS!Date & vbCrLf & msgString
RS.MoveNext
Wend

RS.Close 


vMsg = MsgBox("The Rates have not been uploaded for the for the following dates: " & vbCrLf & _
    vbCrLf & msgString & vbCrLf & _
    "This will cause issues when converting calculating fields. " & _
    "Upload the Rates for these dates and re-run the process.", vbCritical, "Rates")

End If