[AccessD] Query Help

[Stuart McLachlan]

If you don't like to rely on forcing errors ( I don't), then a better solution would be:

Function OldFiles(DOS As String) As String
Dim tdf As TableDef
Dim fld As Field
Dim DosExists As Boolean
Dim sDOS, sOldFiles, sTemp As String
For Each tdf In CurrentDb.TableDefs
    DosExists = False
    For Each fld In tdf.Fields
        If fld.Name = "DOS" Then DosExists = True: Exit For
    Next
    If DosExists Then
        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
    End If
Next
  OldFiles = sOldFiles
End Function



On 17 Nov 2023 at 11:17, Stuart McLachlan wrote:

> 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
> > 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>