Filter difficulties

[BruceM]

I have a table in which a unique value is in the format S-07-01. The number
is generated automatically. "S" is the department code, 07 is the year, and
01 is the first record this year for that department. By the way, the date
the record was started is also stored, so in a sense the 07 is redundant,
but I decided not to worry about that. I stored the literal text value
S-07-01.
I decided not to store the department name, since it could be determined
from the identifying number ("S" stands for "Sales", for instance).
However, this is causing problems when I try to filter the records. Some
department codes are two-letters ("SH" is "Shipping"), so there could be
both S-07-01 and SH-07-01.
I am trying to filter by using a combo box with the department code and
department name as the two columns in the row source:
S Sales
SH Shipping

Literal values produce the desired result in a command button click event:

Me.Filter = "Left([MyNumber], 1) = ""S"""
Me.FilterOn = Not Me.FilterOn

However, I have not found a way to use variables instead of the literal
values (this code in the combo box After Update event):

Private Sub cboFilter_AfterUpdate()

dim lngDept as Long, strFilter as String

lngDept = Len(Me.cboFilter) ' the length of the department code

Me.Filter = Left([MyNumber],lngDept) = Me.cboFilter
' If "Sales" was selected from cboFilter, this is intended to be:
' Me.Filter = "Left([MyNumber],1) = ""S"""
Me.FilterOn = Not Me.FilterOn

End Sub

I have not been able to find an arrangement of quote marks that will get
this to do anything except prompt for parameters. I realize the code will
not make a distinction between "S" and "SH" as department codes, but this is
irrelevant for now since the code doesn't do anything at all.

Of course, if I had stored the department this would be pretty simple. The
database is in the development stages, so there is no problem with going
back to add the field, if that is the cleanest way to solve this problem.

 

[BruceM]

I meant to define strFilter as Me.cboFilter, and use strFilter in the
Me.Filter line. I don't think it matters, but that is why the variable is
defined and not used.

 

[Guest]

BruceM,
Is it possible you are over analyzing this?
Using your example of S = Sales and SH = Shipping so that the code to look
for is variable in length should not really be a problem.

It should be as simple as (assuming the dept code is the bound column):

Me.Filter = "Left([MyNumber], Len(Me.cboFilter)) = " & Me.cboFilter

If there is a chance it would be evaluate to a length of 2 ( "S "), then
trim it:
Me.Filter = "Left([MyNumber], Len(Me.cboFilter)) = " & Trim(Me.cboFilter)

--
Dave Hargis, Microsoft Access MVP

I meant to define strFilter as Me.cboFilter, and use strFilter in the
Me.Filter line. I don't think it matters, but that is why the variable is
defined and not used.

I have a table in which a unique value is in the format S-07-01. The
number is generated automatically. "S" is the department code, 07 is the
year, and 01 is the first record this year for that department. By the
way, the date the record was started is also stored, so in a sense the 07
is redundant, but I decided not to worry about that. I stored the literal
text value S-07-01.
I decided not to store the department name, since it could be determined
from the identifying number ("S" stands for "Sales", for instance).
However, this is causing problems when I try to filter the records. Some
department codes are two-letters ("SH" is "Shipping"), so there could be
both S-07-01 and SH-07-01.
I am trying to filter by using a combo box with the department code and
department name as the two columns in the row source:
S Sales
SH Shipping

Literal values produce the desired result in a command button click event:

Me.Filter = "Left([MyNumber], 1) = ""S"""
Me.FilterOn = Not Me.FilterOn

However, I have not found a way to use variables instead of the literal
values (this code in the combo box After Update event):

Private Sub cboFilter_AfterUpdate()

dim lngDept as Long, strFilter as String

lngDept = Len(Me.cboFilter) ' the length of the department code

Me.Filter = Left([MyNumber],lngDept) = Me.cboFilter
' If "Sales" was selected from cboFilter, this is intended to be:
' Me.Filter = "Left([MyNumber],1) = ""S"""
Me.FilterOn = Not Me.FilterOn

End Sub

I have not been able to find an arrangement of quote marks that will get
this to do anything except prompt for parameters. I realize the code will
not make a distinction between "S" and "SH" as department codes, but this
is irrelevant for now since the code doesn't do anything at all.

Of course, if I had stored the department this would be pretty simple.
The database is in the development stages, so there is no problem with
going back to add the field, if that is the cleanest way to solve this
problem.

 

[BruceM]

Thanks for the reply. I see that I was constructing the string improperly
(I forgot to use an ampersand). The variable-length department code is
still something of an issue, but the filter works now that I have
constructed the string properly:
Me.Filter = "Left([MyNumber], " & Len(Me.cboFilter) & ") = """ &
Me.cboFilter & """"

When I select Sales, the filter string is:
Left([MyNumber],1) = "S"
When I select Shipping, the filter string is:
Left([MyNumber],2) = "SH"

The trouble is that in the first case both SH-07-01 and S-07-01 are
returned, since they both start with "S". I can probably figure out
something now that the obvious mistake is behind me, but I would appreciate
any suggestions you can bring to bear.

BruceM,
Is it possible you are over analyzing this?
Using your example of S = Sales and SH = Shipping so that the code to look
for is variable in length should not really be a problem.

It should be as simple as (assuming the dept code is the bound column):

Me.Filter = "Left([MyNumber], Len(Me.cboFilter)) = " & Me.cboFilter

If there is a chance it would be evaluate to a length of 2 ( "S "), then
trim it:
Me.Filter = "Left([MyNumber], Len(Me.cboFilter)) = " &
Trim(Me.cboFilter)

--
Dave Hargis, Microsoft Access MVP

I meant to define strFilter as Me.cboFilter, and use strFilter in the
Me.Filter line. I don't think it matters, but that is why the variable
is
defined and not used.

I have a table in which a unique value is in the format S-07-01. The
number is generated automatically. "S" is the department code, 07 is
the
year, and 01 is the first record this year for that department. By the
way, the date the record was started is also stored, so in a sense the
07
is redundant, but I decided not to worry about that. I stored the
literal
text value S-07-01.
I decided not to store the department name, since it could be
determined
from the identifying number ("S" stands for "Sales", for instance).
However, this is causing problems when I try to filter the records.
Some
department codes are two-letters ("SH" is "Shipping"), so there could
be
both S-07-01 and SH-07-01.
I am trying to filter by using a combo box with the department code and
department name as the two columns in the row source:
S Sales
SH Shipping

Literal values produce the desired result in a command button click
event:

Me.Filter = "Left([MyNumber], 1) = ""S"""
Me.FilterOn = Not Me.FilterOn

However, I have not found a way to use variables instead of the literal
values (this code in the combo box After Update event):

Private Sub cboFilter_AfterUpdate()

dim lngDept as Long, strFilter as String

lngDept = Len(Me.cboFilter) ' the length of the department code

Me.Filter = Left([MyNumber],lngDept) = Me.cboFilter
' If "Sales" was selected from cboFilter, this is intended to be:
' Me.Filter = "Left([MyNumber],1) = ""S"""
Me.FilterOn = Not Me.FilterOn

End Sub

I have not been able to find an arrangement of quote marks that will
get
this to do anything except prompt for parameters. I realize the code
will
not make a distinction between "S" and "SH" as department codes, but
this
is irrelevant for now since the code doesn't do anything at all.

Of course, if I had stored the department this would be pretty simple.
The database is in the development stages, so there is no problem with
going back to add the field, if that is the cleanest way to solve this
problem.

 

[Guest]

Oh, boy. I should have caught that. That is a puzzlement. It sure would be
better if you could change all the codes to be the same length.
--
Dave Hargis, Microsoft Access MVP

Thanks for the reply. I see that I was constructing the string improperly
(I forgot to use an ampersand). The variable-length department code is
still something of an issue, but the filter works now that I have
constructed the string properly:
Me.Filter = "Left([MyNumber], " & Len(Me.cboFilter) & ") = """ &
Me.cboFilter & """"

When I select Sales, the filter string is:
Left([MyNumber],1) = "S"
When I select Shipping, the filter string is:
Left([MyNumber],2) = "SH"

The trouble is that in the first case both SH-07-01 and S-07-01 are
returned, since they both start with "S". I can probably figure out
something now that the obvious mistake is behind me, but I would appreciate
any suggestions you can bring to bear.

BruceM,
Is it possible you are over analyzing this?
Using your example of S = Sales and SH = Shipping so that the code to look
for is variable in length should not really be a problem.

It should be as simple as (assuming the dept code is the bound column):

Me.Filter = "Left([MyNumber], Len(Me.cboFilter)) = " & Me.cboFilter

If there is a chance it would be evaluate to a length of 2 ( "S "), then
trim it:
Me.Filter = "Left([MyNumber], Len(Me.cboFilter)) = " &
Trim(Me.cboFilter)

--
Dave Hargis, Microsoft Access MVP

I meant to define strFilter as Me.cboFilter, and use strFilter in the
Me.Filter line. I don't think it matters, but that is why the variable
is
defined and not used.

I have a table in which a unique value is in the format S-07-01. The
number is generated automatically. "S" is the department code, 07 is
the
year, and 01 is the first record this year for that department. By the
way, the date the record was started is also stored, so in a sense the
07
is redundant, but I decided not to worry about that. I stored the
literal
text value S-07-01.
I decided not to store the department name, since it could be
determined
from the identifying number ("S" stands for "Sales", for instance).
However, this is causing problems when I try to filter the records.
Some
department codes are two-letters ("SH" is "Shipping"), so there could
be
both S-07-01 and SH-07-01.
I am trying to filter by using a combo box with the department code and
department name as the two columns in the row source:
S Sales
SH Shipping

Literal values produce the desired result in a command button click
event:

Me.Filter = "Left([MyNumber], 1) = ""S"""
Me.FilterOn = Not Me.FilterOn

However, I have not found a way to use variables instead of the literal
values (this code in the combo box After Update event):

Private Sub cboFilter_AfterUpdate()

dim lngDept as Long, strFilter as String

lngDept = Len(Me.cboFilter) ' the length of the department code

Me.Filter = Left([MyNumber],lngDept) = Me.cboFilter
' If "Sales" was selected from cboFilter, this is intended to be:
' Me.Filter = "Left([MyNumber],1) = ""S"""
Me.FilterOn = Not Me.FilterOn

End Sub

I have not been able to find an arrangement of quote marks that will
get
this to do anything except prompt for parameters. I realize the code
will
not make a distinction between "S" and "SH" as department codes, but
this
is irrelevant for now since the code doesn't do anything at all.

Of course, if I had stored the department this would be pretty simple.
The database is in the development stages, so there is no problem with
going back to add the field, if that is the cleanest way to solve this
problem.

 

[BruceM]

Yup, codes the same length would be handy. However, the existing structure
is ingrained into a number of systems, not to mention quite a number of
minds. Changing the systems would be the easy part.

Me.Filter = "Left([MyNumber], " & _
Len(Me.cboFilter) & ") = """ & _
Me.cboFilter & """"

Changing the recordsource SQL would probably be simpler, but adding the
department name field would be simpler still. The thing I don't like about
changing the recordsource is that the query has something like 35 fields
including various calculated and concatenated fields. I could probably make
the SELECT part of the SQL into a constant (SELECT MyNumber, MyField,
MyText, ... MyDate FROM MyQuery ), and add the WHERE according to the combo
box selection. With SQL I could do something like:

dim strSQL as String

If Len(Me.cboFilter) = 1 Then
strSQL = "SELECT * FROM MyQuery WHERE " & _
"InStr([MyNumber],"-") = 2 AND " &
"Left([MyNumber],1) = """ & [cboFilter] & """"

I'm not sure about the syntax, but something like that, maybe. I can't
figure out how to get that InStr condition into a filter.

Storing the department is looking better and better.

Oh, boy. I should have caught that. That is a puzzlement. It sure would
be
better if you could change all the codes to be the same length.
--
Dave Hargis, Microsoft Access MVP

Thanks for the reply. I see that I was constructing the string
improperly
(I forgot to use an ampersand). The variable-length department code is
still something of an issue, but the filter works now that I have
constructed the string properly:
Me.Filter = "Left([MyNumber], " & Len(Me.cboFilter) & ") = """ &
Me.cboFilter & """"

When I select Sales, the filter string is:
Left([MyNumber],1) = "S"
When I select Shipping, the filter string is:
Left([MyNumber],2) = "SH"

The trouble is that in the first case both SH-07-01 and S-07-01 are
returned, since they both start with "S". I can probably figure out
something now that the obvious mistake is behind me, but I would
appreciate
any suggestions you can bring to bear.

BruceM,
Is it possible you are over analyzing this?
Using your example of S = Sales and SH = Shipping so that the code to
look
for is variable in length should not really be a problem.

It should be as simple as (assuming the dept code is the bound column):

Me.Filter = "Left([MyNumber], Len(Me.cboFilter)) = " & Me.cboFilter

If there is a chance it would be evaluate to a length of 2 ( "S "),
then
trim it:
Me.Filter = "Left([MyNumber], Len(Me.cboFilter)) = " &
Trim(Me.cboFilter)

--
Dave Hargis, Microsoft Access MVP


:

I meant to define strFilter as Me.cboFilter, and use strFilter in the
Me.Filter line. I don't think it matters, but that is why the
variable
is
defined and not used.

I have a table in which a unique value is in the format S-07-01. The
number is generated automatically. "S" is the department code, 07 is
the
year, and 01 is the first record this year for that department. By
the
way, the date the record was started is also stored, so in a sense
the
07
is redundant, but I decided not to worry about that. I stored the
literal
text value S-07-01.
I decided not to store the department name, since it could be
determined
from the identifying number ("S" stands for "Sales", for instance).
However, this is causing problems when I try to filter the records.
Some
department codes are two-letters ("SH" is "Shipping"), so there
could
be
both S-07-01 and SH-07-01.
I am trying to filter by using a combo box with the department code
and
department name as the two columns in the row source:
S Sales
SH Shipping

Literal values produce the desired result in a command button click
event:

Me.Filter = "Left([MyNumber], 1) = ""S"""
Me.FilterOn = Not Me.FilterOn

However, I have not found a way to use variables instead of the
literal
values (this code in the combo box After Update event):

Private Sub cboFilter_AfterUpdate()

dim lngDept as Long, strFilter as String

lngDept = Len(Me.cboFilter) ' the length of the department
code

Me.Filter = Left([MyNumber],lngDept) = Me.cboFilter
' If "Sales" was selected from cboFilter, this is intended to be:
' Me.Filter = "Left([MyNumber],1) = ""S"""
Me.FilterOn = Not Me.FilterOn

End Sub

I have not been able to find an arrangement of quote marks that will
get
this to do anything except prompt for parameters. I realize the
code
will
not make a distinction between "S" and "SH" as department codes, but
this
is irrelevant for now since the code doesn't do anything at all.

Of course, if I had stored the department this would be pretty
simple.
The database is in the development stages, so there is no problem
with
going back to add the field, if that is the cleanest way to solve
this
problem.

 

[Guest]

How about:
Me.Filter = "Left([MyNumber], " & _
Len(Me.cboFilter) + 1 & ") = """ & _
Me.cboFilter & "-"""

Not sure I have the quotes correct, but do you get the idea?
That is if it is S-07 or SH-07, then if you include the - it should filter
correct.ly. (I think)

--
Dave Hargis, Microsoft Access MVP

Yup, codes the same length would be handy. However, the existing structure
is ingrained into a number of systems, not to mention quite a number of
minds. Changing the systems would be the easy part.

Me.Filter = "Left([MyNumber], " & _
Len(Me.cboFilter) & ") = """ & _
Me.cboFilter & """"

Changing the recordsource SQL would probably be simpler, but adding the
department name field would be simpler still. The thing I don't like about
changing the recordsource is that the query has something like 35 fields
including various calculated and concatenated fields. I could probably make
the SELECT part of the SQL into a constant (SELECT MyNumber, MyField,
MyText, ... MyDate FROM MyQuery ), and add the WHERE according to the combo
box selection. With SQL I could do something like:

dim strSQL as String

If Len(Me.cboFilter) = 1 Then
strSQL = "SELECT * FROM MyQuery WHERE " & _
"InStr([MyNumber],"-") = 2 AND " &
"Left([MyNumber],1) = """ & [cboFilter] & """"

I'm not sure about the syntax, but something like that, maybe. I can't
figure out how to get that InStr condition into a filter.

Storing the department is looking better and better.

Oh, boy. I should have caught that. That is a puzzlement. It sure would
be
better if you could change all the codes to be the same length.
--
Dave Hargis, Microsoft Access MVP

Thanks for the reply. I see that I was constructing the string
improperly
(I forgot to use an ampersand). The variable-length department code is
still something of an issue, but the filter works now that I have
constructed the string properly:
Me.Filter = "Left([MyNumber], " & Len(Me.cboFilter) & ") = """ &
Me.cboFilter & """"

When I select Sales, the filter string is:
Left([MyNumber],1) = "S"
When I select Shipping, the filter string is:
Left([MyNumber],2) = "SH"

The trouble is that in the first case both SH-07-01 and S-07-01 are
returned, since they both start with "S". I can probably figure out
something now that the obvious mistake is behind me, but I would
appreciate
any suggestions you can bring to bear.

BruceM,
Is it possible you are over analyzing this?
Using your example of S = Sales and SH = Shipping so that the code to
look
for is variable in length should not really be a problem.

It should be as simple as (assuming the dept code is the bound column):

Me.Filter = "Left([MyNumber], Len(Me.cboFilter)) = " & Me.cboFilter

If there is a chance it would be evaluate to a length of 2 ( "S "),
then
trim it:
Me.Filter = "Left([MyNumber], Len(Me.cboFilter)) = " &
Trim(Me.cboFilter)

--
Dave Hargis, Microsoft Access MVP


:

I meant to define strFilter as Me.cboFilter, and use strFilter in the
Me.Filter line. I don't think it matters, but that is why the
variable
is
defined and not used.

I have a table in which a unique value is in the format S-07-01. The
number is generated automatically. "S" is the department code, 07 is
the
year, and 01 is the first record this year for that department. By
the
way, the date the record was started is also stored, so in a sense
the
07
is redundant, but I decided not to worry about that. I stored the
literal
text value S-07-01.
I decided not to store the department name, since it could be
determined
from the identifying number ("S" stands for "Sales", for instance).
However, this is causing problems when I try to filter the records.
Some
department codes are two-letters ("SH" is "Shipping"), so there
could
be
both S-07-01 and SH-07-01.
I am trying to filter by using a combo box with the department code
and
department name as the two columns in the row source:
S Sales
SH Shipping

Literal values produce the desired result in a command button click
event:

Me.Filter = "Left([MyNumber], 1) = ""S"""
Me.FilterOn = Not Me.FilterOn

However, I have not found a way to use variables instead of the
literal
values (this code in the combo box After Update event):

Private Sub cboFilter_AfterUpdate()

dim lngDept as Long, strFilter as String

lngDept = Len(Me.cboFilter) ' the length of the department
code

Me.Filter = Left([MyNumber],lngDept) = Me.cboFilter
' If "Sales" was selected from cboFilter, this is intended to be:
' Me.Filter = "Left([MyNumber],1) = ""S"""
Me.FilterOn = Not Me.FilterOn

End Sub

I have not been able to find an arrangement of quote marks that will
get
this to do anything except prompt for parameters. I realize the
code
will
not make a distinction between "S" and "SH" as department codes, but
this
is irrelevant for now since the code doesn't do anything at all.

Of course, if I had stored the department this would be pretty
simple.
The database is in the development stages, so there is no problem
with
going back to add the field, if that is the cleanest way to solve
this
problem.

 

[BruceM]

Perfect! Simple, and it gets the job done. I recall now that I was trying
to get ahold of something like that yesterday, but I couldn't sort it out in
my brain. I got stuck on trying to limit MyNumber first, then apply the
filter (sort of filtering a filtered recordset), but that line of thought
was leading me nowhere. Thanks for your help. Much appreciated.

How about:
Me.Filter = "Left([MyNumber], " & _
Len(Me.cboFilter) + 1 & ") = """ & _
Me.cboFilter & "-"""

Not sure I have the quotes correct, but do you get the idea?
That is if it is S-07 or SH-07, then if you include the - it should filter
correct.ly. (I think)

--
Dave Hargis, Microsoft Access MVP

Yup, codes the same length would be handy. However, the existing
structure
is ingrained into a number of systems, not to mention quite a number of
minds. Changing the systems would be the easy part.

Me.Filter = "Left([MyNumber], " & _
Len(Me.cboFilter) & ") = """ & _
Me.cboFilter & """"

Changing the recordsource SQL would probably be simpler, but adding the
department name field would be simpler still. The thing I don't like
about
changing the recordsource is that the query has something like 35 fields
including various calculated and concatenated fields. I could probably
make
the SELECT part of the SQL into a constant (SELECT MyNumber, MyField,
MyText, ... MyDate FROM MyQuery ), and add the WHERE according to the
combo
box selection. With SQL I could do something like:

dim strSQL as String

If Len(Me.cboFilter) = 1 Then
strSQL = "SELECT * FROM MyQuery WHERE " & _
"InStr([MyNumber],"-") = 2 AND " &
"Left([MyNumber],1) = """ & [cboFilter] & """"

I'm not sure about the syntax, but something like that, maybe. I can't
figure out how to get that InStr condition into a filter.

Storing the department is looking better and better.

Oh, boy. I should have caught that. That is a puzzlement. It sure
would
be
better if you could change all the codes to be the same length.
--
Dave Hargis, Microsoft Access MVP


:

Thanks for the reply. I see that I was constructing the string
improperly
(I forgot to use an ampersand). The variable-length department code
is
still something of an issue, but the filter works now that I have
constructed the string properly:
Me.Filter = "Left([MyNumber], " & Len(Me.cboFilter) & ") = """ &
Me.cboFilter & """"

When I select Sales, the filter string is:
Left([MyNumber],1) = "S"
When I select Shipping, the filter string is:
Left([MyNumber],2) = "SH"

The trouble is that in the first case both SH-07-01 and S-07-01 are
returned, since they both start with "S". I can probably figure out
something now that the obvious mistake is behind me, but I would
appreciate
any suggestions you can bring to bear.

BruceM,
Is it possible you are over analyzing this?
Using your example of S = Sales and SH = Shipping so that the code
to
look
for is variable in length should not really be a problem.

It should be as simple as (assuming the dept code is the bound
column):

Me.Filter = "Left([MyNumber], Len(Me.cboFilter)) = " &
Me.cboFilter

If there is a chance it would be evaluate to a length of 2 ( "S "),
then
trim it:
Me.Filter = "Left([MyNumber], Len(Me.cboFilter)) = " &
Trim(Me.cboFilter)

--
Dave Hargis, Microsoft Access MVP


:

I meant to define strFilter as Me.cboFilter, and use strFilter in
the
Me.Filter line. I don't think it matters, but that is why the
variable
is
defined and not used.

I have a table in which a unique value is in the format S-07-01.
The
number is generated automatically. "S" is the department code, 07
is
the
year, and 01 is the first record this year for that department.
By
the
way, the date the record was started is also stored, so in a sense
the
07
is redundant, but I decided not to worry about that. I stored the
literal
text value S-07-01.
I decided not to store the department name, since it could be
determined
from the identifying number ("S" stands for "Sales", for
instance).
However, this is causing problems when I try to filter the
records.
Some
department codes are two-letters ("SH" is "Shipping"), so there
could
be
both S-07-01 and SH-07-01.
I am trying to filter by using a combo box with the department
code
and
department name as the two columns in the row source:
S Sales
SH Shipping

Literal values produce the desired result in a command button
click
event:

Me.Filter = "Left([MyNumber], 1) = ""S"""
Me.FilterOn = Not Me.FilterOn

However, I have not found a way to use variables instead of the
literal
values (this code in the combo box After Update event):

Private Sub cboFilter_AfterUpdate()

dim lngDept as Long, strFilter as String

lngDept = Len(Me.cboFilter) ' the length of the department
code

Me.Filter = Left([MyNumber],lngDept) = Me.cboFilter
' If "Sales" was selected from cboFilter, this is intended to
be:
' Me.Filter = "Left([MyNumber],1) = ""S"""
Me.FilterOn = Not Me.FilterOn

End Sub

I have not been able to find an arrangement of quote marks that
will
get
this to do anything except prompt for parameters. I realize the
code
will
not make a distinction between "S" and "SH" as department codes,
but
this
is irrelevant for now since the code doesn't do anything at all.

Of course, if I had stored the department this would be pretty
simple.
The database is in the development stages, so there is no problem
with
going back to add the field, if that is the cleanest way to solve
this
problem.

 

[Guest]

Glad I could help.
My dad always said I was simple
--
Dave Hargis, Microsoft Access MVP

Perfect! Simple, and it gets the job done. I recall now that I was trying
to get ahold of something like that yesterday, but I couldn't sort it out in
my brain. I got stuck on trying to limit MyNumber first, then apply the
filter (sort of filtering a filtered recordset), but that line of thought
was leading me nowhere. Thanks for your help. Much appreciated.

How about:
Me.Filter = "Left([MyNumber], " & _
Len(Me.cboFilter) + 1 & ") = """ & _
Me.cboFilter & "-"""

Not sure I have the quotes correct, but do you get the idea?
That is if it is S-07 or SH-07, then if you include the - it should filter
correct.ly. (I think)

--
Dave Hargis, Microsoft Access MVP

Yup, codes the same length would be handy. However, the existing
structure
is ingrained into a number of systems, not to mention quite a number of
minds. Changing the systems would be the easy part.

Me.Filter = "Left([MyNumber], " & _
Len(Me.cboFilter) & ") = """ & _
Me.cboFilter & """"

Changing the recordsource SQL would probably be simpler, but adding the
department name field would be simpler still. The thing I don't like
about
changing the recordsource is that the query has something like 35 fields
including various calculated and concatenated fields. I could probably
make
the SELECT part of the SQL into a constant (SELECT MyNumber, MyField,
MyText, ... MyDate FROM MyQuery ), and add the WHERE according to the
combo
box selection. With SQL I could do something like:

dim strSQL as String

If Len(Me.cboFilter) = 1 Then
strSQL = "SELECT * FROM MyQuery WHERE " & _
"InStr([MyNumber],"-") = 2 AND " &
"Left([MyNumber],1) = """ & [cboFilter] & """"

I'm not sure about the syntax, but something like that, maybe. I can't
figure out how to get that InStr condition into a filter.

Storing the department is looking better and better.

Oh, boy. I should have caught that. That is a puzzlement. It sure
would
be
better if you could change all the codes to be the same length.
--
Dave Hargis, Microsoft Access MVP


:

Thanks for the reply. I see that I was constructing the string
improperly
(I forgot to use an ampersand). The variable-length department code
is
still something of an issue, but the filter works now that I have
constructed the string properly:
Me.Filter = "Left([MyNumber], " & Len(Me.cboFilter) & ") = """ &
Me.cboFilter & """"

When I select Sales, the filter string is:
Left([MyNumber],1) = "S"
When I select Shipping, the filter string is:
Left([MyNumber],2) = "SH"

The trouble is that in the first case both SH-07-01 and S-07-01 are
returned, since they both start with "S". I can probably figure out
something now that the obvious mistake is behind me, but I would
appreciate
any suggestions you can bring to bear.

BruceM,
Is it possible you are over analyzing this?
Using your example of S = Sales and SH = Shipping so that the code
to
look
for is variable in length should not really be a problem.

It should be as simple as (assuming the dept code is the bound
column):

Me.Filter = "Left([MyNumber], Len(Me.cboFilter)) = " &
Me.cboFilter

If there is a chance it would be evaluate to a length of 2 ( "S "),
then
trim it:
Me.Filter = "Left([MyNumber], Len(Me.cboFilter)) = " &
Trim(Me.cboFilter)

--
Dave Hargis, Microsoft Access MVP


:

I meant to define strFilter as Me.cboFilter, and use strFilter in
the
Me.Filter line. I don't think it matters, but that is why the
variable
is
defined and not used.

I have a table in which a unique value is in the format S-07-01.
The
number is generated automatically. "S" is the department code, 07
is
the
year, and 01 is the first record this year for that department.
By
the
way, the date the record was started is also stored, so in a sense
the
07
is redundant, but I decided not to worry about that. I stored the
literal
text value S-07-01.
I decided not to store the department name, since it could be
determined
from the identifying number ("S" stands for "Sales", for
instance).
However, this is causing problems when I try to filter the
records.
Some
department codes are two-letters ("SH" is "Shipping"), so there
could
be
both S-07-01 and SH-07-01.
I am trying to filter by using a combo box with the department
code
and
department name as the two columns in the row source:
S Sales
SH Shipping

Literal values produce the desired result in a command button
click
event:

Me.Filter = "Left([MyNumber], 1) = ""S"""
Me.FilterOn = Not Me.FilterOn

However, I have not found a way to use variables instead of the
literal
values (this code in the combo box After Update event):

Private Sub cboFilter_AfterUpdate()

dim lngDept as Long, strFilter as String

lngDept = Len(Me.cboFilter) ' the length of the department
code

Me.Filter = Left([MyNumber],lngDept) = Me.cboFilter
' If "Sales" was selected from cboFilter, this is intended to
be:
' Me.Filter = "Left([MyNumber],1) = ""S"""
Me.FilterOn = Not Me.FilterOn

End Sub

I have not been able to find an arrangement of quote marks that
will
get
this to do anything except prompt for parameters. I realize the
code
will
not make a distinction between "S" and "SH" as department codes,
but
this
is irrelevant for now since the code doesn't do anything at all.

Of course, if I had stored the department this would be pretty
simple.
The database is in the development stages, so there is no problem
with
going back to add the field, if that is the cleanest way to solve
this
problem.