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?????
:
-----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/AwUBQ9VOCYechKqOuFEgEQJIAACgkhDn6u48vT96Kfv5LI6vOLXC07EAnRBM
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.
:
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?
:
-----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+KLyP7okBGeoxdKvgTV0AoKDr
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.
