Field's name property (1 Viewer)

[polina]

Hi,

After I import the Excel spreadsheet into new Access table, keeping the excel columns as the Access fields name, I want to be able to find out the names of the fields.

Say for instance, I know for sure that one field name will have the following name "Pages/MO Feb. 2003"...but I will also have the field "Pages/MO March. 2003".
Is there the way to extract the part of the field's name or use LIKE key word to find out if the certain string is in the field's name

I will appreicate any tips on that.

Thanks.

 

[WayneRyan]

polina,

The last entry on this thread has some code that you could
use:

http://www.access-programmers.co.uk...&threadid=35632&highlight=fields+AND+tabledef

Wayne

 

[Jon K]

The following code (run from a command button on a form) loops through the field names of a table and displays each field name that contains the string "Pages/MO".

----------------------------
Private Sub Command0_Click()

Dim db As DAO.Database
Dim fld As DAO.Field
Dim tDef As DAO.TableDef

Set db = CurrentDb
Set tDef = db.TableDefs("SpecifyTableNameHere")

' loop through each field name.
For Each fld In tDef.Fields

' look for the string "Pages/Mo" in the field name.
If InStr(fld.Name, "Pages/MO") > 0 Then
MsgBox fld.Name
End If

Next

End Sub
----------------------------

The code is written in Access 97. If you use Access 2000 or 2002, you must make a reference to DAO (when the code window is open, choose menu Tools, References... and select the Microsoft DAO 3.6 Object Library)

 

[directormac]

A question for you, polina. How long will this "history" of the publications go on? If you are going to have to add a new field to your table every month to store the number of pages for that month for that publication... you're going to have a very long record structure very quickly:

intPages/MO.April2003
intPages/MO.May2003
<snip>
intPages/MO.January2004
<snip>
intPages/MO.Dec2009

It won't take long until the structure of the table becomes unmanageable. It may be that you need to break the information about a particular issue separate from the information about that publication:


tblPublications
autoPublicationKey = autonumber (PK)
txtPublicationName = text
(add'l fields as appropriate)

tblIssues
autoIssueKey = autonumber (PK)
lngPublicationID = long int (FK from tblPublications)
dtmPublicationDate = short date
intPages = integer


Set a one-to-many from tblPublications to tblIssues. This way, you can add new issues as often as needed without storing all the information in one humongous table.

Just a thought.

--Not-Quite-Normal Mac