MS Access 2003 CrossTab Query Question using VBA--Item not found i

When running a crosstab query, one cannot tell ahead of time what columns are
going to appear based on parameter criteria fed to an underlying query that a
crosstab query is created from. That, of course, is the dynamic nature of
crosstab result sets. However, if I know all the possible values for the
columns because the field the column is based on has a finite number of
possible values, how can I test if a particular column is present using VBA?
With the usual construct of rs![X], where X is one of the possible column
values, if that value does not appear when the crosstab is run, a debug of
that if statement will show the error message that "item not found in this
collection". MY QUESTION IS:

how can I error trap for this? That is, if I want to test for rs![X].value
and it does not exist, then the code just skips by if on error resume is
used; otherwise the cdoe breaks. I would ideally like to have another IF
statement outside of the one above that checks for the existance of that
column, and I do not know how to do this. Is there an error code associated
with "item not found in this collection" that I can look for? If so, what
function would I use to test for the error condition and what value would it
need to equal for ""item not found in this collection"???? And if this is
not a possible or best approach, how should I detect/look for a particular
column in a crosstab query when the query is run in code and the recordset is
processed in code looking for a specific resulting column and its value???

Thanks to anyone who can help me!!!!

Mike S.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What you're really looking for is the name of the Field (it is the
object in the Fields collection that is not being found); that is easily
found:

If rs.Field = "name of field" Then
' do something w/ the field
End If

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9F1ioechKqOuFEgEQLyZQCg43Zl6AO+KLyP7okBGeoxdK vgTV0AoKDr
vAzScW9oG0Ia1fBAJaUi4xyz
=6nhz
-----END PGP SIGNATURE-----


Mike S. S. wrote:
When running a crosstab query, one cannot tell ahead of time what columns are
going to appear based on parameter criteria fed to an underlying query that a
crosstab query is created from. That, of course, is the dynamic nature of
crosstab result sets. However, if I know all the possible values for the
columns because the field the column is based on has a finite number of
possible values, how can I test if a particular column is present using VBA?
With the usual construct of rs![X], where X is one of the possible column
values, if that value does not appear when the crosstab is run, a debug of
that if statement will show the error message that "item not found in this
collection". MY QUESTION IS:

how can I error trap for this? That is, if I want to test for rs![X].value
and it does not exist, then the code just skips by if on error resume is
used; otherwise the cdoe breaks. I would ideally like to have another IF
statement outside of the one above that checks for the existance of that
column, and I do not know how to do this. Is there an error code associated
with "item not found in this collection" that I can look for? If so, what
function would I use to test for the error condition and what value would it
need to equal for ""item not found in this collection"???? And if this is
not a possible or best approach, how should I detect/look for a particular
column in a crosstab query when the query is run in code and the recordset is
processed in code looking for a specific resulting column and its value???

Thanks to anyone who can help me!!!!

Mike S.

Use the ColumnHeading property of the Crosstab Query to enter all of the
choices.

eg. "Jan", "Feb", "Mar"

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

"Mike S. S." wrote in message
...
When running a crosstab query, one cannot tell ahead of time what columns
are
going to appear based on parameter criteria fed to an underlying query
that a
crosstab query is created from. That, of course, is the dynamic nature of
crosstab result sets. However, if I know all the possible values for the
columns because the field the column is based on has a finite number of
possible values, how can I test if a particular column is present using
VBA?
With the usual construct of rs![X], where X is one of the possible column
values, if that value does not appear when the crosstab is run, a debug of
that if statement will show the error message that "item not found in this
collection". MY QUESTION IS:

how can I error trap for this? That is, if I want to test for
rs![X].value
and it does not exist, then the code just skips by if on error resume is
used; otherwise the cdoe breaks. I would ideally like to have another IF
statement outside of the one above that checks for the existance of that
column, and I do not know how to do this. Is there an error code
associated
with "item not found in this collection" that I can look for? If so, what
function would I use to test for the error condition and what value would
it
need to equal for ""item not found in this collection"???? And if this
is
not a possible or best approach, how should I detect/look for a particular
column in a crosstab query when the query is run in code and the recordset
is
processed in code looking for a specific resulting column and its value???

Thanks to anyone who can help me!!!!

Mike S.

There is no rs.Field in the collection. There is an rs.Fields with Append,
Count, Delete, and Refresh methods associated with it. What am I missing
here?

"MGFoster" wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What you're really looking for is the name of the Field (it is the
object in the Fields collection that is not being found); that is easily
found:

If rs.Field = "name of field" Then
' do something w/ the field
End If

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9F1ioechKqOuFEgEQLyZQCg43Zl6AO+KLyP7okBGeoxdK vgTV0AoKDr
vAzScW9oG0Ia1fBAJaUi4xyz
=6nhz
-----END PGP SIGNATURE-----


Mike S. S. wrote:
When running a crosstab query, one cannot tell ahead of time what columns are
going to appear based on parameter criteria fed to an underlying query that a
crosstab query is created from. That, of course, is the dynamic nature of
crosstab result sets. However, if I know all the possible values for the
columns because the field the column is based on has a finite number of
possible values, how can I test if a particular column is present using VBA?
With the usual construct of rs![X], where X is one of the possible column
values, if that value does not appear when the crosstab is run, a debug of
that if statement will show the error message that "item not found in this
collection". MY QUESTION IS:

how can I error trap for this? That is, if I want to test for rs![X].value
and it does not exist, then the code just skips by if on error resume is
used; otherwise the cdoe breaks. I would ideally like to have another IF
statement outside of the one above that checks for the existance of that
column, and I do not know how to do this. Is there an error code associated
with "item not found in this collection" that I can look for? If so, what
function would I use to test for the error condition and what value would it
need to equal for ""item not found in this collection"???? And if this is
not a possible or best approach, how should I detect/look for a particular
column in a crosstab query when the query is run in code and the recordset is
processed in code looking for a specific resulting column and its value???

Thanks to anyone who can help me!!!!

Mike S.

Right. My mistake. I meant rs.Name. It will return the field name.
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

Mike S. S. wrote:
There is no rs.Field in the collection. There is an rs.Fields with Append,
Count, Delete, and Refresh methods associated with it. What am I missing
here?

"MGFoster" wrote:


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What you're really looking for is the name of the Field (it is the
object in the Fields collection that is not being found); that is easily
found:

If rs.Field = "name of field" Then
' do something w/ the field
End If

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9F1ioechKqOuFEgEQLyZQCg43Zl6AO+KLyP7okBGeoxdK vgTV0AoKDr
vAzScW9oG0Ia1fBAJaUi4xyz
=6nhz
-----END PGP SIGNATURE-----


Mike S. S. wrote:

When running a crosstab query, one cannot tell ahead of time what columns are
going to appear based on parameter criteria fed to an underlying query that a
crosstab query is created from. That, of course, is the dynamic nature of
crosstab result sets. However, if I know all the possible values for the
columns because the field the column is based on has a finite number of
possible values, how can I test if a particular column is present using VBA?
With the usual construct of rs![X], where X is one of the possible column
values, if that value does not appear when the crosstab is run, a debug of
that if statement will show the error message that "item not found in this
collection". MY QUESTION IS:

how can I error trap for this? That is, if I want to test for rs![X].value
and it does not exist, then the code just skips by if on error resume is
used; otherwise the cdoe breaks. I would ideally like to have another IF
statement outside of the one above that checks for the existance of that
column, and I do not know how to do this. Is there an error code associated
with "item not found in this collection" that I can look for? If so, what
function would I use to test for the error condition and what value would it
need to equal for ""item not found in this collection"???? And if this is
not a possible or best approach, how should I detect/look for a particular
column in a crosstab query when the query is run in code and the recordset is
processed in code looking for a specific resulting column and its value???

Thanks to anyone who can help me!!!!

Mike S.

I don't think you have explained at all why you want to do this. However,
you can take a crosstab like the one in Northwind.mdb

TRANSFORM Sum(CCur([Order
Details].UnitPrice*[Quantity]*(1-[Discount])/100)*100) AS ProductAmount
SELECT Products.ProductName, Orders.CustomerID, Year([OrderDate]) AS
OrderYear
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
WHERE (((Orders.OrderDate) Between #1/1/1997# And #12/31/1997#))
GROUP BY Products.ProductName, Orders.CustomerID, Year([OrderDate])
PIVOT "Qtr " & DatePart("q",[OrderDate],1,0);

Get rid of all the TRANSFORM, SELECTed, and GROUP BY columns. Delete the
word "PIVOT" and copy the expression from after the PIVOT and place it into
the SELECT clause:

SELECT "Qtr " & DatePart("q",[OrderDate],1,0) AS Expr1
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
WHERE (((Orders.OrderDate) Between #1/1/1997# And #12/31/1997#))
GROUP BY "Qtr " & DatePart("q",[OrderDate],1,0) ;

This will show all possible column values.
--
Duane Hookom
MS Access MVP
--

"Mike S. S." wrote in message
...
When running a crosstab query, one cannot tell ahead of time what columns
are
going to appear based on parameter criteria fed to an underlying query
that a
crosstab query is created from. That, of course, is the dynamic nature of
crosstab result sets. However, if I know all the possible values for the
columns because the field the column is based on has a finite number of
possible values, how can I test if a particular column is present using
VBA?
With the usual construct of rs![X], where X is one of the possible column
values, if that value does not appear when the crosstab is run, a debug of
that if statement will show the error message that "item not found in this
collection". MY QUESTION IS:

how can I error trap for this? That is, if I want to test for
rs![X].value
and it does not exist, then the code just skips by if on error resume is
used; otherwise the cdoe breaks. I would ideally like to have another IF
statement outside of the one above that checks for the existance of that
column, and I do not know how to do this. Is there an error code
associated
with "item not found in this collection" that I can look for? If so, what
function would I use to test for the error condition and what value would
it
need to equal for ""item not found in this collection"???? And if this
is
not a possible or best approach, how should I detect/look for a particular
column in a crosstab query when the query is run in code and the recordset
is
processed in code looking for a specific resulting column and its value???

Thanks to anyone who can help me!!!!

Mike S.

MG:

rs.name returns the name of the query that created the recordset not the
column name I am looking at. Below is the code I am using:

intcol = rs.Fields.Count - 1
x=2

Do While x = intcol
If rs(x).Name = "A" And rs(x).Value = rs![Total] Then
intA = intA + 1
ElseIf rs(x).Name = "F" And rs(x).Value = rs![Total] Then
intF = intF + 1
ElseIf rs(x).Name = "L" And rs(x).Value = rs![Total] Then
intL = intL + 1
ElseIf rs(x).Name = "RV" And rs(x).Value = rs![Total] Then
intSO = intSO + 1
ElseIf rs(x).Name = "SP" And rs(x).Value = rs![Total] Then
intSO = intSO + 1
ElseIf rs(x).Name = "RV" And rs(x).Value 0 Then
intRVV = -1
xRV = x
ElseIf rs(x).Name = "SP" And rs(x).Value 0 Then
intSPV = -1
xSP = x
ElseIf rs(x).Name = "A" Or rs(x).Name = "F" Or rs(x).Name = "L"
Or rs(x).Name = "SP" Or rs(x).Name = "RV" Then
ElseIf rs(x).Value 0 Then
intOther = -1
End If
x = x + 1
Loop

The reasons for the above code has to do with the business requirements of
the report; however rs(x).Name = "A" does give me the column name. The
problem is how do I find out if rs(x).name does NOT equal a certain value
(there are too many values to test for all of them). When the code hits the
statement rs(x).Name = "A" and the currrent column name (.index # x) does
not ="A" then the error "Item not found in this collection" occurs. If I
could trap this error by an error number, etc., then I would know that as I
loop though the columns of the resulting corsstab, a certain column does not
exist (I need to know for any particular row if a column exists AND if there
is a value or not for that column).

I am sorry if I am not making clear my problem, but I hope this helps.
Again, before running a crosstab ones does not know how many columns will
appear and what their name will be..that is the natureof a crosstab. My
crosstab columns are based on a field that is equivalent to product type run
for a particular salesperson. Based on a particular period of sales, the
salesperson may have made sales in one product category, two, ten, up to the
max. of product categories possible in the table.




"MGFoster" wrote:

Right. My mistake. I meant rs.Name. It will return the field name.
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

Mike S. S. wrote:
There is no rs.Field in the collection. There is an rs.Fields with Append,
Count, Delete, and Refresh methods associated with it. What am I missing
here?

"MGFoster" wrote:


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What you're really looking for is the name of the Field (it is the
object in the Fields collection that is not being found); that is easily
found:

If rs.Field = "name of field" Then
' do something w/ the field
End If

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9F1ioechKqOuFEgEQLyZQCg43Zl6AO+KLyP7okBGeoxdK vgTV0AoKDr
vAzScW9oG0Ia1fBAJaUi4xyz
=6nhz
-----END PGP SIGNATURE-----


Mike S. S. wrote:

When running a crosstab query, one cannot tell ahead of time what columns are
going to appear based on parameter criteria fed to an underlying query that a
crosstab query is created from. That, of course, is the dynamic nature of
crosstab result sets. However, if I know all the possible values for the
columns because the field the column is based on has a finite number of
possible values, how can I test if a particular column is present using VBA?
With the usual construct of rs![X], where X is one of the possible column
values, if that value does not appear when the crosstab is run, a debug of
that if statement will show the error message that "item not found in this
collection". MY QUESTION IS:

how can I error trap for this? That is, if I want to test for rs![X].value
and it does not exist, then the code just skips by if on error resume is
used; otherwise the cdoe breaks. I would ideally like to have another IF
statement outside of the one above that checks for the existance of that
column, and I do not know how to do this. Is there an error code associated
with "item not found in this collection" that I can look for? If so, what
function would I use to test for the error condition and what value would it
need to equal for ""item not found in this collection"???? And if this is
not a possible or best approach, how should I detect/look for a particular
column in a crosstab query when the query is run in code and the recordset is
processed in code looking for a specific resulting column and its value???

Thanks to anyone who can help me!!!!

Mike S.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your code only reads the first row of the recordset. Is that what you
want?

It might be easier to use a SELECT CASE expression instead of the If &
IfElse structure. (not sure this will solve the problem) E.g.:

Do While x = intcol
Select Case rs(x).Name
Case "A"
If rs(x).Value = rs!Total Then intA = intA + 1
Case "F"
If rs(x).Value = rs!Total Then intF = intF + 1
' ... etc. ...
Case Else
If rs(x).Value 0 Then intOther = -1
End Select
x = x + 1
Loop

Unfortunately, VBA doesn't "short-cut" when the 1st criteria of an
IF...THEN statement is False. This means all the comparisons in the If
line are evaluated. If the column doesn't exist in the recordset the
"Item not found in this collection" error occurs.

You might want to put a Break point on the Do While statement & step
thru the code. When the break point is hit: in the Debug window use
the following to see what column names are available:

for each fld in rs.Fields : Debug.Print fld.Name : Next fld

then step thru the WHILE loop to see which If statement is causing the
error.
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9VOCYechKqOuFEgEQJIAACgkhDn6u48vT96Kfv5LI6vOL XC07EAnRBM
tXeA7JbYM8fFwr60/Ps16f0d
=j++R
-----END PGP SIGNATURE-----

Mike S. S. wrote:
MG:

rs.name returns the name of the query that created the recordset not the
column name I am looking at. Below is the code I am using:

intcol = rs.Fields.Count - 1
x=2

Do While x = intcol
If rs(x).Name = "A" And rs(x).Value = rs![Total] Then
intA = intA + 1
ElseIf rs(x).Name = "F" And rs(x).Value = rs![Total] Then
intF = intF + 1
ElseIf rs(x).Name = "L" And rs(x).Value = rs![Total] Then
intL = intL + 1
ElseIf rs(x).Name = "RV" And rs(x).Value = rs![Total] Then
intSO = intSO + 1
ElseIf rs(x).Name = "SP" And rs(x).Value = rs![Total] Then
intSO = intSO + 1
ElseIf rs(x).Name = "RV" And rs(x).Value 0 Then
intRVV = -1
xRV = x
ElseIf rs(x).Name = "SP" And rs(x).Value 0 Then
intSPV = -1
xSP = x
ElseIf rs(x).Name = "A" Or rs(x).Name = "F" Or rs(x).Name = "L"
Or rs(x).Name = "SP" Or rs(x).Name = "RV" Then
ElseIf rs(x).Value 0 Then
intOther = -1
End If
x = x + 1
Loop

The reasons for the above code has to do with the business requirements of
the report; however rs(x).Name = "A" does give me the column name. The
problem is how do I find out if rs(x).name does NOT equal a certain value
(there are too many values to test for all of them). When the code hits the
statement rs(x).Name = "A" and the currrent column name (.index # x) does
not ="A" then the error "Item not found in this collection" occurs. If I
could trap this error by an error number, etc., then I would know that as I
loop though the columns of the resulting corsstab, a certain column does not
exist (I need to know for any particular row if a column exists AND if there
is a value or not for that column).

I am sorry if I am not making clear my problem, but I hope this helps.
Again, before running a crosstab ones does not know how many columns will
appear and what their name will be..that is the natureof a crosstab. My
crosstab columns are based on a field that is equivalent to product type run
for a particular salesperson. Based on a particular period of sales, the
salesperson may have made sales in one product category, two, ten, up to the
max. of product categories possible in the table.




"MGFoster" wrote:


Right. My mistake. I meant rs.Name. It will return the field name.
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

Mike S. S. wrote:

There is no rs.Field in the collection. There is an rs.Fields with Append,
Count, Delete, and Refresh methods associated with it. What am I missing
here?

"MGFoster" wrote:



-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What you're really looking for is the name of the Field (it is the
object in the Fields collection that is not being found); that is easily
found:

If rs.Field = "name of field" Then
' do something w/ the field
End If

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9F1ioechKqOuFEgEQLyZQCg43Zl6AO+KLyP7okBGeoxdK vgTV0AoKDr
vAzScW9oG0Ia1fBAJaUi4xyz
=6nhz
-----END PGP SIGNATURE-----


Mike S. S. wrote:


When running a crosstab query, one cannot tell ahead of time what columns are
going to appear based on parameter criteria fed to an underlying query that a
crosstab query is created from. That, of course, is the dynamic nature of
crosstab result sets. However, if I know all the possible values for the
columns because the field the column is based on has a finite number of
possible values, how can I test if a particular column is present using VBA?
With the usual construct of rs![X], where X is one of the possible column
values, if that value does not appear when the crosstab is run, a debug of
that if statement will show the error message that "item not found in this
collection". MY QUESTION IS:

how can I error trap for this? That is, if I want to test for rs![X].value
and it does not exist, then the code just skips by if on error resume is
used; otherwise the cdoe breaks. I would ideally like to have another IF
statement outside of the one above that checks for the existance of that
column, and I do not know how to do this. Is there an error code associated
with "item not found in this collection" that I can look for? If so, what
function would I use to test for the error condition and what value would it
need to equal for ""item not found in this collection"???? And if this is
not a possible or best approach, how should I detect/look for a particular
column in a crosstab query when the query is run in code and the recordset is
processed in code looking for a specific resulting column and its value???

Thanks to anyone who can help me!!!!

Mike S.

MG:

I didn't show all my code since it was not necessary to show the
problem....there is an outer DO WHILE NOT rs.EOF that skips throuogh all the
records (and this does not solve my problem). I do not hit any breaks in the
program because I start the code off with ON ERROR RESUME NEXT so that the
"item not found...." error won't cause a break. This still leaves me with
the same problem though. I actually thought of building a variable string of
all of the column names and then testing to see if the column I am looking
for is in the string or not using INSTR. Unfortunately, the column names I
need to look for are codes that are defined by a single alpha character, but
some of the other possible codes are 2 char alphas, some of which contain the
single alpha I am looking for, so I could get a false string search.

Any other ideas?????

"MGFoster" wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your code only reads the first row of the recordset. Is that what you
want?

It might be easier to use a SELECT CASE expression instead of the If &
IfElse structure. (not sure this will solve the problem) E.g.:

Do While x = intcol
Select Case rs(x).Name
Case "A"
If rs(x).Value = rs!Total Then intA = intA + 1
Case "F"
If rs(x).Value = rs!Total Then intF = intF + 1
' ... etc. ...
Case Else
If rs(x).Value 0 Then intOther = -1
End Select
x = x + 1
Loop

Unfortunately, VBA doesn't "short-cut" when the 1st criteria of an
IF...THEN statement is False. This means all the comparisons in the If
line are evaluated. If the column doesn't exist in the recordset the
"Item not found in this collection" error occurs.

You might want to put a Break point on the Do While statement & step
thru the code. When the break point is hit: in the Debug window use
the following to see what column names are available:

for each fld in rs.Fields : Debug.Print fld.Name : Next fld

then step thru the WHILE loop to see which If statement is causing the
error.
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9VOCYechKqOuFEgEQJIAACgkhDn6u48vT96Kfv5LI6vOL XC07EAnRBM
tXeA7JbYM8fFwr60/Ps16f0d
=j++R
-----END PGP SIGNATURE-----

Mike S. S. wrote:
MG:

rs.name returns the name of the query that created the recordset not the
column name I am looking at. Below is the code I am using:

intcol = rs.Fields.Count - 1
x=2

Do While x = intcol
If rs(x).Name = "A" And rs(x).Value = rs![Total] Then
intA = intA + 1
ElseIf rs(x).Name = "F" And rs(x).Value = rs![Total] Then
intF = intF + 1
ElseIf rs(x).Name = "L" And rs(x).Value = rs![Total] Then
intL = intL + 1
ElseIf rs(x).Name = "RV" And rs(x).Value = rs![Total] Then
intSO = intSO + 1
ElseIf rs(x).Name = "SP" And rs(x).Value = rs![Total] Then
intSO = intSO + 1
ElseIf rs(x).Name = "RV" And rs(x).Value 0 Then
intRVV = -1
xRV = x
ElseIf rs(x).Name = "SP" And rs(x).Value 0 Then
intSPV = -1
xSP = x
ElseIf rs(x).Name = "A" Or rs(x).Name = "F" Or rs(x).Name = "L"
Or rs(x).Name = "SP" Or rs(x).Name = "RV" Then
ElseIf rs(x).Value 0 Then
intOther = -1
End If
x = x + 1
Loop

The reasons for the above code has to do with the business requirements of
the report; however rs(x).Name = "A" does give me the column name. The
problem is how do I find out if rs(x).name does NOT equal a certain value
(there are too many values to test for all of them). When the code hits the
statement rs(x).Name = "A" and the currrent column name (.index # x) does
not ="A" then the error "Item not found in this collection" occurs. If I
could trap this error by an error number, etc., then I would know that as I
loop though the columns of the resulting corsstab, a certain column does not
exist (I need to know for any particular row if a column exists AND if there
is a value or not for that column).

I am sorry if I am not making clear my problem, but I hope this helps.
Again, before running a crosstab ones does not know how many columns will
appear and what their name will be..that is the natureof a crosstab. My
crosstab columns are based on a field that is equivalent to product type run
for a particular salesperson. Based on a particular period of sales, the
salesperson may have made sales in one product category, two, ten, up to the
max. of product categories possible in the table.




"MGFoster" wrote:


Right. My mistake. I meant rs.Name. It will return the field name.
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

Mike S. S. wrote:

There is no rs.Field in the collection. There is an rs.Fields with Append,
Count, Delete, and Refresh methods associated with it. What am I missing
here?

"MGFoster" wrote:



-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What you're really looking for is the name of the Field (it is the
object in the Fields collection that is not being found); that is easily
found:

If rs.Field = "name of field" Then
' do something w/ the field
End If

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9F1ioechKqOuFEgEQLyZQCg43Zl6AO+KLyP7okBGeoxdK vgTV0AoKDr
vAzScW9oG0Ia1fBAJaUi4xyz
=6nhz
-----END PGP SIGNATURE-----


Mike S. S. wrote:


When running a crosstab query, one cannot tell ahead of time what columns are
going to appear based on parameter criteria fed to an underlying query that a
crosstab query is created from. That, of course, is the dynamic nature of
crosstab result sets. However, if I know all the possible values for the
columns because the field the column is based on has a finite number of
possible values, how can I test if a particular column is present using VBA?
With the usual construct of rs![X], where X is one of the possible column
values, if that value does not appear when the crosstab is run, a debug of
that if statement will show the error message that "item not found in this
collection". MY QUESTION IS:

how can I error trap for this? That is, if I want to test for rs![X].value
and it does not exist, then the code just skips by if on error resume is
used; otherwise the cdoe breaks. I would ideally like to have another IF
statement outside of the one above that checks for the existance of that
column, and I do not know how to do this. Is there an error code associated
with "item not found in this collection" that I can look for? If so, what
function would I use to test for the error condition and what value would it
need to equal for ""item not found in this collection"???? And if this is
not a possible or best approach, how should I detect/look for a particular
column in a crosstab query when the query is run in code and the recordset is
processed in code looking for a specific resulting column and its value???

Thanks to anyone who can help me!!!!

Mike S.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A break point is a brown dot on the left margin of the VBA module. If
you haven't set the application to ignore "special keys" then when the
VBA code runs & hits the break point the code will stop and the VBA
module will be visible to you. You then use the Debug toolbar to step
thru the code (or whatever else you want to do in Debug mode).

How to set the break point:

Open the VBA module. Move to the location where you want the code to
stop running. Click the left margin at that line. You can't set the
break point on Dim or Const or blank lines.

For debug purposes Dim out the ON ERROR RESUME NEXT line until you fix
the problem.
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9V1wIechKqOuFEgEQKNCQCgjDatznrKonTW4F5YkS8BWe o25TYAoPoH
nLELR/YoGNmo4bOJBJicpWsJ
=65i3
-----END PGP SIGNATURE-----

Mike S. S. wrote:
MG:

I didn't show all my code since it was not necessary to show the
problem....there is an outer DO WHILE NOT rs.EOF that skips throuogh all the
records (and this does not solve my problem). I do not hit any breaks in the
program because I start the code off with ON ERROR RESUME NEXT so that the
"item not found...." error won't cause a break. This still leaves me with
the same problem though. I actually thought of building a variable string of
all of the column names and then testing to see if the column I am looking
for is in the string or not using INSTR. Unfortunately, the column names I
need to look for are codes that are defined by a single alpha character, but
some of the other possible codes are 2 char alphas, some of which contain the
single alpha I am looking for, so I could get a false string search.

Any other ideas?????

"MGFoster" wrote:


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your code only reads the first row of the recordset. Is that what you
want?

It might be easier to use a SELECT CASE expression instead of the If &
IfElse structure. (not sure this will solve the problem) E.g.:

Do While x = intcol
Select Case rs(x).Name
Case "A"
If rs(x).Value = rs!Total Then intA = intA + 1
Case "F"
If rs(x).Value = rs!Total Then intF = intF + 1
' ... etc. ...
Case Else
If rs(x).Value 0 Then intOther = -1
End Select
x = x + 1
Loop

Unfortunately, VBA doesn't "short-cut" when the 1st criteria of an
IF...THEN statement is False. This means all the comparisons in the If
line are evaluated. If the column doesn't exist in the recordset the
"Item not found in this collection" error occurs.

You might want to put a Break point on the Do While statement & step
thru the code. When the break point is hit: in the Debug window use
the following to see what column names are available:

for each fld in rs.Fields : Debug.Print fld.Name : Next fld

then step thru the WHILE loop to see which If statement is causing the
error.
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9VOCYechKqOuFEgEQJIAACgkhDn6u48vT96Kfv5LI6vOL XC07EAnRBM
tXeA7JbYM8fFwr60/Ps16f0d
=j++R
-----END PGP SIGNATURE-----

Mike S. S. wrote:

MG:

rs.name returns the name of the query that created the recordset not the
column name I am looking at. Below is the code I am using:

intcol = rs.Fields.Count - 1
x=2

Do While x = intcol
If rs(x).Name = "A" And rs(x).Value = rs![Total] Then
intA = intA + 1
ElseIf rs(x).Name = "F" And rs(x).Value = rs![Total] Then
intF = intF + 1
ElseIf rs(x).Name = "L" And rs(x).Value = rs![Total] Then
intL = intL + 1
ElseIf rs(x).Name = "RV" And rs(x).Value = rs![Total] Then
intSO = intSO + 1
ElseIf rs(x).Name = "SP" And rs(x).Value = rs![Total] Then
intSO = intSO + 1
ElseIf rs(x).Name = "RV" And rs(x).Value 0 Then
intRVV = -1
xRV = x
ElseIf rs(x).Name = "SP" And rs(x).Value 0 Then
intSPV = -1
xSP = x
ElseIf rs(x).Name = "A" Or rs(x).Name = "F" Or rs(x).Name = "L"
Or rs(x).Name = "SP" Or rs(x).Name = "RV" Then
ElseIf rs(x).Value 0 Then
intOther = -1
End If
x = x + 1
Loop

The reasons for the above code has to do with the business requirements of
the report; however rs(x).Name = "A" does give me the column name. The
problem is how do I find out if rs(x).name does NOT equal a certain value
(there are too many values to test for all of them). When the code hits the
statement rs(x).Name = "A" and the currrent column name (.index # x) does
not ="A" then the error "Item not found in this collection" occurs. If I
could trap this error by an error number, etc., then I would know that as I
loop though the columns of the resulting corsstab, a certain column does not
exist (I need to know for any particular row if a column exists AND if there
is a value or not for that column).

I am sorry if I am not making clear my problem, but I hope this helps.
Again, before running a crosstab ones does not know how many columns will
appear and what their name will be..that is the natureof a crosstab. My
crosstab columns are based on a field that is equivalent to product type run
for a particular salesperson. Based on a particular period of sales, the
salesperson may have made sales in one product category, two, ten, up to the
max. of product categories possible in the table.




"MGFoster" wrote:



Right. My mistake. I meant rs.Name. It will return the field name.
--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

Mike S. S. wrote:


There is no rs.Field in the collection. There is an rs.Fields with Append,
Count, Delete, and Refresh methods associated with it. What am I missing
here?

"MGFoster" wrote:




-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

What you're really looking for is the name of the Field (it is the
object in the Fields collection that is not being found); that is easily
found:

If rs.Field = "name of field" Then
' do something w/ the field
End If

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ9F1ioechKqOuFEgEQLyZQCg43Zl6AO+KLyP7okBGeoxdK vgTV0AoKDr
vAzScW9oG0Ia1fBAJaUi4xyz
=6nhz
-----END PGP SIGNATURE-----


Mike S. S. wrote:



When running a crosstab query, one cannot tell ahead of time what columns are
going to appear based on parameter criteria fed to an underlying query that a
crosstab query is created from. That, of course, is the dynamic nature of
crosstab result sets. However, if I know all the possible values for the
columns because the field the column is based on has a finite number of
possible values, how can I test if a particular column is present using VBA?
With the usual construct of rs![X], where X is one of the possible column
values, if that value does not appear when the crosstab is run, a debug of
that if statement will show the error message that "item not found in this
collection". MY QUESTION IS:

how can I error trap for this? That is, if I want to test for rs![X].value
and it does not exist, then the code just skips by if on error resume is
used; otherwise the cdoe breaks. I would ideally like to have another IF
statement outside of the one above that checks for the existance of that
column, and I do not know how to do this. Is there an error code associated
with "item not found in this collection" that I can look for? If so, what
function would I use to test for the error condition and what value would it
need to equal for ""item not found in this collection"???? And if this is
not a possible or best approach, how should I detect/look for a particular
column in a crosstab query when the query is run in code and the recordset is
processed in code looking for a specific resulting column and its value???

Thanks to anyone who can help me!!!!

Mike S.