[AccessD] Query Help

[Stuart McLachlan]

A VBA function would be a lot easier :)


Function OldFiles(DOS As String) As String
Dim tdf As TableDef
Dim sDOS, sOldFiles, sTemp As String

For Each tdf In CurrentDb.TableDefs
    On Error GoTo NoField 'skip tables that don't have a DOS field
    sDOS = Nz(DMax("DOS", tdf.Name), "") 'include where max DOS is null!
    If sDOS <= DOS Then
        sOldFiles = sOldFiles & tdf.Name & vbTab & sDOS & vbCrLf
    End If
skip:
Next
  OldFiles = sOldFiles
Exit Function

NoField:
  Resume skip
End Function




On 16 Nov 2023 at 21:33, RANDALL R ANTHONY via AccessD wrote:

> Hello group,
> Long time no see, so to speak. Hey, I need to run a query across
> multiple tables to see when last used (I know, bear with me).  Each
> table has a DateofService column, DOS, and it's in a year month
> format, ie, 202301.  So I'd like to use a where clause like where DOS
> < 201012 and the results show that 20 out of 45 tables don't have a
> DOS date greater than that.  Looking for old data tables that can be
> deleted to free up space when the app is run.  Is this a no-brainer or
> am I missing something?
> 
> TIA.
> 
> Randall R. Anthony, MCP
> Database Administrator
> 
> Disclaimer:
> 
> 
> This electronic message and its contents and attachments contain
> information from Sentara Health and is confidential or otherwise
> protected from disclosure. The information is intended to be for the
> addressee only.
> 
> If you are not the addressee, any disclosure, copy, distribution or
> use of the contents of this message is prohibited. If you have
> received this electronic message in error, please notify us
> immediately and destroy the original message and all copies. --
> AccessD mailing list AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd Website:
> http://www.databaseadvisors.com
>