Specific data search problem

[trima1]

I have a simple query that uses a field on a form to filter my data. The data in the field looks like this,

3S(June 24-29)
3T(June 24-29)
34P(June 24-July 4)


My criteria expression is simple:
Like [Forms]!SundayPreRegFrm]!SessionNum]&"*"

Everything looks fine if I am looking for a single week number (like 3).

I need to modify the criteria to see the 4 between the 3 and P, but not see the 4 in the date.

Is it possible?

Mark

 

[Remou]

I don't quite get what you mean. Please write the criterion as you wish to see it.

 

[trima1]

Maybe if I explain what the data means, it might make more sense.

The data in the table is shown as
3S(June 24-29)

3 = week number
S = Session Type
(June24-29) = date code (that I want to ignore)

But some data has multiple week numbers
34P(June 24-July 4)

But, when I use the expression
Like [Forms]!SundayPreRegFrm]!SessionNum]&"*",
and the SessionNum (week number) variable on the form = 4, nothing shows up, as to be expected.

Sooooo....

When I specify week 3 on the form, I want to see
3S(June 24-29)
3T(June 24-29)
34P(June 24-July 4)

When I specify week 4 on the form, I want to see
34P(June 24-July 4)
4T(July 1-July 4)


I hope that makes more sense.

Thanks for the help.

Mark

 

[Remou]

Perhaps:

[tt]<...> Where Mid(NameOfField,1,Instr(NameofField,"(")) Like "*" & Forms!SundayPreRegFrm!SessionNum &"*"[/tt]

That is, just compare the field up to the bracket.

Aside: This is quite an odd way to store data.

 

[PHV]

I personally don't use the query grid as it generates buggy messy SQL code, so here a SQL code starting point:
WHERE Val([your strange week session date field]) Like '*' & [Forms]!SundayPreRegFrm]!SessionNum] & '*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[trima1]

Thanks for your time on this.

I am not quite understanding your answer.

Should it look like
Where Mid([SessionNum],1,(Instr([SessionNum],"("))) Like "*" & [Forms]![SundayPreRegFrm]![SessionNum] &"*"


BTW, This is not my database. Just got thrown into the middle of a wierd situation. I am trying to patch it through the summer until I have time to make a new one in the fall.

 

[Remou]

That would be the general idea. However, it is a Where statement that needs, say, a Select statement.

 

[PHV]

You have to go in the SQL view pane of the Query window to better understand the suggestions you've got.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[trima1]

No, I understood what went before it, but thanks for the reminder anyway.

Works fine, thanks for the help.