KLATUU--Code to Go thru DB and search table field names for particular name

[Andre Laplume via AccessMonster.com]

Your code worked great for searching the queries for a referenced table. I'd
like to do the same with table field names. Would I need another loop to
traverse thru each field in the table?

--
OOPs a typo...the statement that DOES NOT work is:

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],IIf([TYPE]=1,

[Amt]*[Percent],0)))

 

[Klatuu]

Sub ShowTableFields(strTableName)
Dim objTdefs As TableDefs
Dim objTdef As TableDef
Dim lngFldCount As Long

Set objTdefs = CurrentDb.TableDefs
Set objTdef = objTdefs(strTableName)
For lngFldCount = 0 To objTdef.Fields.Count - 1
Debug.Print objTdef.Fields(lngFldCount).Name
Next lngFldCount
End Sub

As to the statement that does not work. I did not write that. I think it
does not work because of the way it is looking at the date. the only way it
might work is if you are using the Long Date format on your computer. Since
there are different date formats, I would suggeset you try this modification:

AdjAmt: Sum(IIf([TYPE]=2 And
Month(Date)=7,[Amt]*[Percent],IIf([TYPE]=1,[Amt]*[Percent],0)))

Your code worked great for searching the queries for a referenced table. I'd
like to do the same with table field names. Would I need another loop to
traverse thru each field in the table?

--
OOPs a typo...the statement that DOES NOT work is:

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],IIf([TYPE]=1,

[Amt]*[Percent],0)))

 

[Andre Laplume via AccessMonster.com]

I got the date one working. I also figured out how to do the table/field one.
...however your code looks more streamlined than what I came up with.

One interesting thing on the 'query' code...likely an Access quirk...the
INSTR function that opens the each query and searches the text for the table..
....well the whole thing blows up if it can not open a query do to "ambiguous
outter joins'. That is ok but the weird thing is when I run the affected
query it is fine--no ambiguous joins error in design or SQL view. I think
the problem arises because the person who clicked the links for the two keys
in each table did it in a weird way. He/She linked t1.key1 to t2.key1
starting with t1.key1. He/She then linked t2.key2 to t1.key2 starting with
t2.key2. He/She then double clicked the two links and set them to get the
appropriate join but need to select a #2 join on one and a #3 join on the
other. The net result is two keys join from tb1 to tbl2 and works fine but
the INSTR command did not like it. I deleted the links and re-did them
properly and the INSTR worked fine.

I hope I have not completely confused you!

Sub ShowTableFields(strTableName)
Dim objTdefs As TableDefs
Dim objTdef As TableDef
Dim lngFldCount As Long

Set objTdefs = CurrentDb.TableDefs
Set objTdef = objTdefs(strTableName)
For lngFldCount = 0 To objTdef.Fields.Count - 1
Debug.Print objTdef.Fields(lngFldCount).Name
Next lngFldCount
End Sub

As to the statement that does not work. I did not write that. I think it
does not work because of the way it is looking at the date. the only way it
might work is if you are using the Long Date format on your computer. Since
there are different date formats, I would suggeset you try this modification:

AdjAmt: Sum(IIf([TYPE]=2 And
Month(Date)=7,[Amt]*[Percent],IIf([TYPE]=1,[Amt]*[Percent],0)))

Your code worked great for searching the queries for a referenced table. I'd
like to do the same with table field names. Would I need another loop to
traverse thru each field in the table?

[Amt]*[Percent],0)))

--
OOPs a typo...the statement that DOES NOT work is:

AdjAmt: Sum(IIf([TYPE]=2 And Left(Date(),2)="07",[Amt]*[Percent],IIf([TYPE]=1,

[Amt]*[Percent],0)))