positional data in WHERE claus

[maceslin]

I am writing code for a series of cascading combo boxes but do not how
to write the where claus for my desired activity.

The data for all combo boxes comes from tblJCA which contains JCA_PK
as autonumber, JCA_Number as <i>text</i> and JCA_Desc.

JCA_Number is formated as follows: 1.2.3 or 2.4.11

cboJCA1 shoulld list the single digit values where there is only
something in the first position like 1 of example 1.2.3 or 2 of
example 2.4.11

cboJCA2 cascades from there- cboJCA2 should list the data from the
third position in the text string that has the same value as selected
in cboJCA1 for example of 1 is chosen in cboJCA1 then cboJCA 2 should
list values 1.1, 1.2, 1.3, 1.4 etc..

if cboJCA2 value is 1.3 then cboJCA3 should list all values such as
1.3.1 or 1.3.2 or 1.3.3 etc.

I hope this is clear and some one can help me with the "WHERE" claus

Dave

 

[Klatuu]

There Where Clause should be different for the row source query for each
combo box. If you have, for example, 4 combo boxes, the query for box 4
should reference the value in box 4:
SELECT SomeField FROM SomeTable Where AnotherField = Me.Combo3

Then in the After Update event of combo3, you requery combo 4

Me.Combo4.Requery

And you just move backwards using the same technique.

 

[Mike Painter]

I am writing code for a series of cascading combo boxes but do not how
to write the where claus for my desired activity.

The data for all combo boxes comes from tblJCA which contains JCA_PK
as autonumber, JCA_Number as <i>text</i> and JCA_Desc.

JCA_Number is formated as follows: 1.2.3 or 2.4.11

cboJCA1 shoulld list the single digit values where there is only
something in the first position like 1 of example 1.2.3 or 2 of
example 2.4.11

cboJCA2 cascades from there- cboJCA2 should list the data from the
third position in the text string that has the same value as selected
in cboJCA1 for example of 1 is chosen in cboJCA1 then cboJCA 2 should
list values 1.1, 1.2, 1.3, 1.4 etc..

if cboJCA2 value is 1.3 then cboJCA3 should list all values such as
1.3.1 or 1.3.2 or 1.3.3 etc.

I hope this is clear and some one can help me with the "WHERE" claus

Dave

You say "where there is only something in the first position " Does this
mean there can be a
".2.3"?

You can use Mid and the Instr function to pick those values or you could use
the split function.
If something is mandated in the first position, even a zero, it's a bit
easier.

 

[maceslin]

You say "where there is only  something in the first position " Does this
mean there can be a
".2.3"?

You can use Mid and the Instr function to pick those values or you could use
the split function.
If something is mandated in  the first position, even a zero, it's a bit
easier.- Hide quoted text -

- Show quoted text -

Mike- there has to be a value in hte first position, it will never
start with a decimal, not sure how to use mid and instr as you
recommend

 

[Mike Painter]

Mike- there has to be a value in hte first position, it will never
start with a decimal, not sure how to use mid and instr as you
recommend

Split is probably a better way to go.
Pos = instr(yourstring,".")
Mid (Yourstring, 1, Pos - 1) gives the first value
Nextpos = instr(Pos + 1,yourstring,"." )
Mid (Yourstring, Pos + 1, NextPos 1) gives the second value etc.

Split would give
A(0) = first value
A(1) = second
A(2) = third and would regire a lot less work if they decide to add.