How to Find a Table in Database Windows Based on Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have: Tables suffixed by the year (e.g., tblCustomers2003,
tblCustomers2004, tblCustomers2005, ...)

I want: to get the name of the table with the latest year.

Application: Access 2003

Any suggestions?
 
Well the most obvious suggestion is to redesign your table structure so that
you don't need multiple tables with similar information.

In the meantime, you can use VBA to find the appropriate table. UNTESTED
AIR CODE follows
Assumption: Using DAO not ADO ( mdb or mde not an Access Project)

'============================
Public Function fLatestTable() As String
Dim dbAny As DAO.Database
Dim tDefAny As DAO.TableDef
Dim strReturn As String

Set dbAny = CurrentDb()

For Each tDefAny In dbAny.TableDefs
If UCase(tDefAny.Name) Like "TBLCUSTOMERS*" Then
If tDefAny.Name > strReturn Then
strReturn = tDefAny.Name
End If
End If
Next tDefAny
fLatestTable = strReturn
End Function



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
DevDaniel said:
I have: Tables suffixed by the year (e.g., tblCustomers2003,
tblCustomers2004, tblCustomers2005, ...)

I want: to get the name of the table with the latest year.

Application: Access 2003

Any suggestions?
Click to expand...

Dim tdf As Dao.TableDef
Dim Yr As Long, Highest As Long
Dim Result As String

For Each tdf In CurrentDb.TableDefs
Yr = Clng(Mid(tdf.Name, 13))
If Yr > Highest Then Highest = Yr
Next

Result = "tblCustomers" & Highest
 
Fastest way:

=DMax("[Name]","MsysObjects","[Type] In (1,4,6) AND [Name] Like
'tblCustomer*'")
 
Worked like a charm! Thanks Dave.


Klatuu said:
Fastest way:

=DMax("[Name]","MsysObjects","[Type] In (1,4,6) AND [Name] Like
'tblCustomer*'")
--
Dave Hargis, Microsoft Access MVP


DevDaniel said:
I have: Tables suffixed by the year (e.g., tblCustomers2003,
tblCustomers2004, tblCustomers2005, ...)

I want: to get the name of the table with the latest year.

Application: Access 2003

Any suggestions?
Click to expand...
Click to expand...
 
Back
Top