[AccessD] Query Help

[RANDALL R ANTHONY]

Thanks everyone again.  Man, I had to dial up the way back machine to use the immediate window to run the function, but it works as advertised.  Major headache resolved.

-----Original Message-----
From: AccessD <accessd-bounces+rranthon=sentara.com at databaseadvisors.com> On Behalf Of Stuart McLachlan
Sent: Thursday, November 16, 2023 8:32 PM
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Subject: Re: [AccessD] Query Help

Notice: This email originated outside the Sentara Health network. Beware of links and attachments. Report suspicious emails with the Report Phishing button.

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://secure-web.cisco.com/1d4McszW4l4yInXw5iqojwdBpIf6fm6k2AQXIn8BVcGCVUPHXxXCxFKM1SWCPHia-4CmShktJBIOHdrTH-mEQj0doGWcu-sI7qN2suLkl4OtKAIa9u6dege-MglR9ozrdRSY5yazf9ZolGs0FO3ame2uKain_avjI6-YfIYDcuuCL4arQP1CL2i1dI5B_oJRASdFVNOcaPi8rn9P8-su-OlDUBvsM1gPwqhlg0lX8Wwj0i2HApqsDzSZw70pzFAWRWQZHgLHqtpL8kcoI0iszoP5ty43JfvXAQSs7daw1n85kPRsX2qyycIRg9MFwozt_/https%3A%2F%2Fdatabaseadvisors.com%2Fmailman%2Flistinfo%2Faccessd Website:
> > http://secure-web.cisco.com/1hrpTvOieGD13IO3MsTYEH-_yN9wMsnreioGFGG6
> > EOvL-muerKf8ot8lEWYZMxN6waxUynIrMRbPoHhPu-na_kl7spsXNR7wGFoO2YQlN41Y
> > cWvti5Zf0objYmTpeV66mbsBbdJOjrShVkwlUsQakTX6CW-0aOStVg5ftmd3UWR4GMzd
> > PQtvflJHtn8aCYiIqg6tHsbSpbJHhrOTy341oGObC9nLnnJw-mvMpXOLcrsAPX-P36wB
> > DIecmFJ4hysaCOvsCLSNH5xkWnORSJZHtHKvqKGwuZmBBqHjJ2exwkSZ-41QRhQyboO-
> > wPNIsStqw/http%3A%2F%2Fwww.databaseadvisors.com
> > 
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://secure-web.cisco.com/1d4McszW4l4yInXw5iqojwdBpIf6fm6k2AQXIn8BV
> cGCVUPHXxXCxFKM1SWCPHia-4CmShktJBIOHdrTH-mEQj0doGWcu-sI7qN2suLkl4OtKAI
> a9u6dege-MglR9ozrdRSY5yazf9ZolGs0FO3ame2uKain_avjI6-YfIYDcuuCL4arQP1CL
> 2i1dI5B_oJRASdFVNOcaPi8rn9P8-su-OlDUBvsM1gPwqhlg0lX8Wwj0i2HApqsDzSZw70
> pzFAWRWQZHgLHqtpL8kcoI0iszoP5ty43JfvXAQSs7daw1n85kPRsX2qyycIRg9MFwozt_
> /https%3A%2F%2Fdatabaseadvisors.com%2Fmailman%2Flistinfo%2Faccessd
> Website: 
> http://secure-web.cisco.com/1hrpTvOieGD13IO3MsTYEH-_yN9wMsnreioGFGG6EO
> vL-muerKf8ot8lEWYZMxN6waxUynIrMRbPoHhPu-na_kl7spsXNR7wGFoO2YQlN41YcWvt
> i5Zf0objYmTpeV66mbsBbdJOjrShVkwlUsQakTX6CW-0aOStVg5ftmd3UWR4GMzdPQtvfl
> JHtn8aCYiIqg6tHsbSpbJHhrOTy341oGObC9nLnnJw-mvMpXOLcrsAPX-P36wBDIecmFJ4
> hysaCOvsCLSNH5xkWnORSJZHtHKvqKGwuZmBBqHjJ2exwkSZ-41QRhQyboO-wPNIsStqw/
> http%3A%2F%2Fwww.databaseadvisors.com
> 


--
AccessD mailing list
AccessD at databaseadvisors.com
https://secure-web.cisco.com/1d4McszW4l4yInXw5iqojwdBpIf6fm6k2AQXIn8BVcGCVUPHXxXCxFKM1SWCPHia-4CmShktJBIOHdrTH-mEQj0doGWcu-sI7qN2suLkl4OtKAIa9u6dege-MglR9ozrdRSY5yazf9ZolGs0FO3ame2uKain_avjI6-YfIYDcuuCL4arQP1CL2i1dI5B_oJRASdFVNOcaPi8rn9P8-su-OlDUBvsM1gPwqhlg0lX8Wwj0i2HApqsDzSZw70pzFAWRWQZHgLHqtpL8kcoI0iszoP5ty43JfvXAQSs7daw1n85kPRsX2qyycIRg9MFwozt_/https%3A%2F%2Fdatabaseadvisors.com%2Fmailman%2Flistinfo%2Faccessd
Website: http://secure-web.cisco.com/1hrpTvOieGD13IO3MsTYEH-_yN9wMsnreioGFGG6EOvL-muerKf8ot8lEWYZMxN6waxUynIrMRbPoHhPu-na_kl7spsXNR7wGFoO2YQlN41YcWvti5Zf0objYmTpeV66mbsBbdJOjrShVkwlUsQakTX6CW-0aOStVg5ftmd3UWR4GMzdPQtvflJHtn8aCYiIqg6tHsbSpbJHhrOTy341oGObC9nLnnJw-mvMpXOLcrsAPX-P36wBDIecmFJ4hysaCOvsCLSNH5xkWnORSJZHtHKvqKGwuZmBBqHjJ2exwkSZ-41QRhQyboO-wPNIsStqw/http%3A%2F%2Fwww.databaseadvisors.com

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.