I'm trying to build a string in Access 2007 VBA that I would pass a
value "Quarters" to, and a value "Consecutive" to. I'm trying to
identify patterns in quarterly data/

If the number of "Quarters"is 2, and "Consecutive" is 2, then I'm
looking for any two quarters in a row where they are equal. (1 and 2).

If the number of "Quarters"is 3, and "Consecutive" is 2, then I'm
looking for any two quarters in a row where they
are equal (1 and 2, or 2 and 3).

Is there a way to do this with a For/Next loop so that I can do this
with one piece of code?

If you'd give us a description of the data you want to analyze, and how you
obtain it (from an array, from records in a table, ???), then I'd wager
someone could be of help.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"Icarus" <tjmoses1 (AT) gmail (DOT) com> wrote

I have a "baseline" field, and three Quarters worth of data (actually
8, but I figure if I can get this to work for three, it will work for
8) to compare

07/01/2010
10/10/2010
01/01/2011

I'm trying to write a For/Next loop that will result in the text
string:

"Where ([Baseline Field Value] > [07/01/2010] and [Baseline Field
Value] > [10/01/2010]) or ([Baseline Field Value] > [10/01/2010] or
[Baseline Field Value] > [01/01/2011])

On Jul 22, 5:57*am, Icarus <tjmos... (AT) gmail (DOT) com> wrote:
I agree with Larry. Even after your second post it's still
confusing. In the second post you have 10/10/2011 yet your list has
10/10/2011. Is the data ordered to see if there are consecutive
values?

You wrote "If the number of "Quarters"is 3, and "Consecutive" is 2,
then I'm looking for any two quarters in a row where they are equal (1
and 2, or 2 and 3)." Kind of a "Huh?"

It wouldn't hurt to put down a couple of example sets. Heading of
Qtrs: n Consecutive: n and then a dataset (5 or 6 rows) and a comment
to the side of which ones fit the bill. Then another dataset to
drive your point home.

Here is the process:

1. the user selects a date range from a list of quarters. In the
example for this post, I'm using the range that will include the three
quarter values of #07/01/2010# , #10/01/2010#, and #01/01/2011#.
These quarters represent field names in the table being queried.

2. the user selects a number of consecutive quarters to compare the
baseline data value. This could be the same number of quarters as what
was selected in step #1 (so 3 quarters), or they could also select 2
quarters.

3. The VBA code then needs to build a SQL string based on the date
range, and the number of consecutive quarters. The purpose is to show
the records where the value in the quarter is not equal to the
baseline value.
a. Right now, I have a for/next loop that creates a SQL
string if they want the same number of consecutive quarters as there
are in the list of quarters:
"where ([07/01/2010] < [BASELINE]) and ([10/01/2010]
< [BASELINE]) and ([01/01/2011] < [BASELINE])"
b. What I'm trying to do now is create a SQL string if they
want a different number of consecutive quarters as there are in the
list of quarters. So in this case, any two consecutive quarters
during the three quarter range:
"where (([07/01/2010] < [BASELINE]) and
([10/01/2010] < [BASELINE])) or (([10/01/2010] < [BASELINE]) and
([01/01/2011] < [BASELINE]) ).

* The intent of this is to streamline my code because the
user can select up to 8 quarter values, and select 2-8 consecutive
quarters. Manually buliding the SQL for each possible instance (2
consecutive in 8, 3 consecutive in
8, 4 consecutive in 8, etc) is difficult to maintain.

4. Once the SQL string is bulit in VBA, I use this to create a new
query with the SQL string, and open the query for the user.

My current For/next loop only works for step 3a.:

(intQuarters is the number of consecutive quarters selected by the
user / rsWhereQuarters is the recordset of Quarter Values /
strBaselineField is the field that the quarter value fields are being
compared against.)

If intQuarters = rsWhereQuarters.RecordCount Then
For intCounter = 1 To intQuarters

strQuarterField =
CStr(rsWhereQuarters("QuarterBeginning")) & strCompareField

'Less Than
If intCounter = rsWhereQuarters.RecordCount Then
strWhereLessThan = strWhereLessThan & "([" &
strQuarterField & "] < [" & strBaselineField & "])"
Else
strWhereLessThan = strWhereLessThan & "([" &
strQuarterField & "] < [" & strBaselineField & "]) and "
End If

rsWhereQuarters.MoveNext

Next
End If

How can I update my code to work in step 3b?

Is BASELINE a date/time field? That's the only field type that would make
any sense in the Where clause you show, but BASELINE (to me) seems an
unusual name for a date/time field. (Note: I didn't say it would be _wrong_
to use BASELINE for date/time value, but is worth clarifying.)

And, if it is a date/time field, then the SQL string you show in step 3a is
redundant, because if the BASELINE date is greater than Jan 1 2011, it will
certainly be greater than the two lesser dates of July 1 2010 and Oct 1
2010.

I'm sorry, but this is just not yet clear to me.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access


"Icarus" <tjmoses1 (AT) gmail (DOT) com> wrote

On Jul 22, 4:57*pm, "Access Developer" <accde... (AT) gmail (DOT) com> wrote:
Thank you for your time. I was on a time crunch, so I found a manual
workaround that meets the customer requirements.

I'm glad you found something that works for you. Best of luck with using
Access in the future.

We're here to help, but we've got to have enough specifics... remote
debugging is difficult enough; without sufficient information it's
impossible.

--
Larry Linson, Microsoft Office Access MVP
Co-author: "Microsoft Access Small Business Solutions", published by Wiley
Access newsgroup support is alive and well in USENET
comp.databases.ms-access

So at the very basic level, I was trying to create a for/next loop.

I have three numbers: 1,2,3. This is part 1.

I have another variable, "InARow". the value for this is "2".

So the for/next loop would need to give me the possible 2 (set by the
"InARow" variable) consecutive number combinations of part 1. The
result I'm looking for is a text string of "(1 and 2) or (2 and 3)".

On Jul 26, 9:08*am, Icarus <tjmos... (AT) gmail (DOT) com> wrote:
It appears you have field names that are "quarters". Inside the
quarters field is some data. I suppose your data is coming from some
pivot table. I think you could have presented a row column example or
two to help illustrate your issue. I'll even show you what I mean
FieldName Value Baseline...
01/2011 10 11 Consecutive
10/2010 5 11 Consecutive
07/2010 30 11 Not

I'm thinking maybe you can write a function. Pass the key, number of
months, etc to it that is needed to figure it our. Then the function
does the comparison. It could even write out those that passed to a
new/other table. use the New/Other table for displaying results.