Run a macro that tests for the number of fields in a table

[Grd]

Hi there,

I need to create a conditional macro (I found the button) to do something if
it has 10 fields but do something else if it has 11 fields.

The somethings I can figure out it how to ask in the conditinal column if a
table has more than ten or 11 fields.

Any help would be great

Tx
Suzanne

 

[Steve Schapel]

Suzanne,

10 or 11 fields? Are you sure? The mind boggles if that is what you
really want. Did you mean 10 or 11 *records*? It would certainly be
possible to count the fields in a table (you would need to use VBA to
write a user-defined function), but it would be a very unusual requirement.

 

[Grd]

Hi Steve,

Thanks for the reply. The reason I'm after this info (10 or 11 fields) is I
have some raw data that I'm importing into a table in Access. Once its
imported I want to know if its got 10 or 11 fields so I can ran the
appropriate query against it.

The original reason it comes in sometimes as 10 or 11 is that some people
who send me the data have the date column something like "as of, 1/1/99"
causing it to sometimes come in as two columns instead of one when the import
macros are run. Other people send me the data with just the "1/1/99".

So I was trying to automate this by using a macro to test for the number of
fields.

If I can't do it in a Macro then a module might make sense for me althought
I wouldn't know how to phrase the if statement there either unfortunately.

Thanks again
S

 

[Steve Schapel]

Suzanne,

Ok, just such an unusual request I thought you had made a typo. Sorry.

In a standard module, type this:

Public Function CountFields(TableName As String) As Integer
CountFields = CurrentDb.TableDefs(TableName).Fields.Count
End Function

Then, in the Condition of your macro, you can put, for example, like this:

CountFields("NameOfYourTable")=10