creating a filter with a form

[timglass via AccessMonster.com]

I have created a database for tracking hours worked. I now need to create a
way for users to sort through the data. I have four fields that I need to
sort through using a form with combo boxes. The only problem that I have is
how do I use a wilcard character, *, in the combo boxes so that if the user
wants to select all the information in one or more of the fields how do I do
that? I posted this question about a week ago and was given a select
statement to use but have not been able to get it to work.

You can add the * to your combos by using a technique like this:
SELECT EmployeeNum FROM tbllkEmployee UNION Select "*" as Bogus From
tbllkEmployee;

Then in the criteria, wrap LIKE around all your control references
LIKE([Forms]![MyFormName]![MyCombo])
If the user selects the *, then it evaluates to LIKE(*) which will return
everything.


Any help is greatly appreciated.

 

[Barry Gilbert]

I have created a database for tracking hours worked. I now need to create a
way for users to sort through the data. I have four fields that I need to
sort through using a form with combo boxes. The only problem that I have is
how do I use a wilcard character, *, in the combo boxes so that if the user
wants to select all the information in one or more of the fields how do I do
that? I posted this question about a week ago and was given a select
statement to use but have not been able to get it to work.

You can add the * to your combos by using a technique like this:
SELECT EmployeeNum FROM tbllkEmployee UNION Select "*" as Bogus From
tbllkEmployee;

There's a small error here. Change the word Bogus to EmployeeNum. The fields
in each portion of a Union query must have the same names.

Barry

 

[Barry Gilbert]

Oops. Please ignore my last post.

Do you see the "*" in the combobox? This will tell you if the union query is
working. Could you post the code you're using to buld the filter?

Barry

 

[TonyT]

The SELECT statement is your combobox rowsource - either set in properties or
in code, on_enter or form on_load etc, and the second lot of code is how you
evaluate the selection in the aforementioned combobox, most likely in a
command buttons on_click event or in the comboboxes after_update event.

the code will be similar for each of your 4 comboboxes just with different
names and fields.

TonyT

 

[timglass via AccessMonster.com]

Thanks for the quick reply. when I enter the select statement i get an error:


Compile error:
Expected: Case

Any ideas?

The SELECT statement is your combobox rowsource - either set in properties or
in code, on_enter or form on_load etc, and the second lot of code is how you
evaluate the selection in the aforementioned combobox, most likely in a
command buttons on_click event or in the comboboxes after_update event.
the code will be similar for each of your 4 comboboxes just with different
names and fields.

TonyT

I have created a database for tracking hours worked. I now need to create a
way for users to sort through the data. I have four fields that I need to

[quoted text clipped - 14 lines]

Any help is greatly appreciated.

 

[TonyT]

Ahh I see, I think

you need something like;

me.mycombobox1.Rowsource = "SELECT .........etc etc etc"
where mycombobox1 is the name of your combobox.

just starting a line Select in vba is confusing it inot expecting a select
case statement which is another ballgame altogether.

Thanks for the quick reply. when I enter the select statement i get an error:


Compile error:
Expected: Case

Any ideas?

The SELECT statement is your combobox rowsource - either set in properties or
in code, on_enter or form on_load etc, and the second lot of code is how you
evaluate the selection in the aforementioned combobox, most likely in a
command buttons on_click event or in the comboboxes after_update event.
the code will be similar for each of your 4 comboboxes just with different
names and fields.

TonyT

I have created a database for tracking hours worked. I now need to create a
way for users to sort through the data. I have four fields that I need to

[quoted text clipped - 14 lines]

Any help is greatly appreciated.

 

[timglass via AccessMonster.com]

I did as you suggested and now am getting a different error

Run-time error '13':

Type mismatch




Sorry for being such a pain

Ahh I see, I think

you need something like;

me.mycombobox1.Rowsource = "SELECT .........etc etc etc"
where mycombobox1 is the name of your combobox.

just starting a line Select in vba is confusing it inot expecting a select
case statement which is another ballgame altogether.

Thanks for the quick reply. when I enter the select statement i get an error:

[quoted text clipped - 17 lines]

 

[TonyT]

Can you copy and paste the exact code and let me know where it is eg,
on_open, on_click etc and of which control.

Type 13 error i believe is type mis-match, and is it highlighting the slect
statement in the code window when it fails? if so what answer do you get in
the immediate windowif you copy and paste the "select etc etc " statement?
(without the me.combobox bit)

TonyT

I did as you suggested and now am getting a different error

Run-time error '13':

Type mismatch




Sorry for being such a pain

Ahh I see, I think

you need something like;

me.mycombobox1.Rowsource = "SELECT .........etc etc etc"
where mycombobox1 is the name of your combobox.

just starting a line Select in vba is confusing it inot expecting a select
case statement which is another ballgame altogether.

Thanks for the quick reply. when I enter the select statement i get an error:

[quoted text clipped - 17 lines]

Any help is greatly appreciated.

 

[timglass via AccessMonster.com]

It's on on_open

Me.Boss.RowSource = "SELECT Boss FROM tbllktable1 UNION SELECT " * " as Boss
FROM tbllktable1;"

Yes it is highlighting the select statement

I get a compile error

Can you copy and paste the exact code and let me know where it is eg,
on_open, on_click etc and of which control.

Type 13 error i believe is type mis-match, and is it highlighting the slect
statement in the code window when it fails? if so what answer do you get in
the immediate windowif you copy and paste the "select etc etc " statement?
(without the me.combobox bit)

TonyT

I did as you suggested and now am getting a different error

[quoted text clipped - 19 lines]

 

[TonyT]

Ok i'm a bit confused now, is the combobox called boss, and the table field
name in tbllktable1 also called boss, and then you want to show "boss" in the
combobox as well?

Your combobox will show * not 'boss' the as lets the code know it's an
alias, you have to keep the * as thats the wildcard symbol used in the second
part of you code to select all records, swapping * for boss will only show
records named boss not all of them as you want

sorry should have noticed earlier - the " " around * should be ' ' instead ;

Me.Boss.RowSource = "SELECT Boss FROM tbllktable1 UNION SELECT " * " as Boss
FROM tbllktable1;"

hope this helps,

TonyT

It's on on_open

Me.Boss.RowSource = "SELECT Boss FROM tbllktable1 UNION SELECT " * " as Boss
FROM tbllktable1;"

Yes it is highlighting the select statement

I get a compile error

Can you copy and paste the exact code and let me know where it is eg,
on_open, on_click etc and of which control.

Type 13 error i believe is type mis-match, and is it highlighting the slect
statement in the code window when it fails? if so what answer do you get in
the immediate windowif you copy and paste the "select etc etc " statement?
(without the me.combobox bit)

TonyT

I did as you suggested and now am getting a different error

[quoted text clipped - 19 lines]

Any help is greatly appreciated.

 

[timglass via AccessMonster.com]

Thanks for your help, sorry about the confusion. Your last suggestion fixed
that problem except when I try to select a value in the combo box I get an
error. It is related to the asterik wildcard. What do I need to do in order
to use the asterik as a wildcard in my combo boxes so that when it is
selected all records for that field are returned? As long as I don't select
the asterik everything seems to work fine.

Thanks again!

Ok i'm a bit confused now, is the combobox called boss, and the table field
name in tbllktable1 also called boss, and then you want to show "boss" in the
combobox as well?

Your combobox will show * not 'boss' the as lets the code know it's an
alias, you have to keep the * as thats the wildcard symbol used in the second
part of you code to select all records, swapping * for boss will only show
records named boss not all of them as you want

sorry should have noticed earlier - the " " around * should be ' ' instead ;

Me.Boss.RowSource = "SELECT Boss FROM tbllktable1 UNION SELECT " * " as Boss
FROM tbllktable1;"

hope this helps,

TonyT

It's on on_open

[quoted text clipped - 20 lines]

 

[Rick Brandt]

There's a small error here. Change the word Bogus to EmployeeNum. The
fields in each portion of a Union query must have the same names.

Barry

Actually they don't. The field names from the first SELECT are used in the
output regardless of the field names in the later SELECTs. All that matters is
that the number and types of the fields be the same.

 

[TonyT]

Rather than me trying to guess what you want to display and where, and also
how you want to trigger it, could you post the code you have tried so far and
what event it occurs in. Presumably it's the LIKE part of the original code
thats failing now is it?

As with most things in access this might not be the *best* way to acheive
what you want, but it's how the thread started so......

keep posting...

TonyT

Thanks for your help, sorry about the confusion. Your last suggestion fixed
that problem except when I try to select a value in the combo box I get an
error. It is related to the asterik wildcard. What do I need to do in order
to use the asterik as a wildcard in my combo boxes so that when it is
selected all records for that field are returned? As long as I don't select
the asterik everything seems to work fine.

Thanks again!

Ok i'm a bit confused now, is the combobox called boss, and the table field
name in tbllktable1 also called boss, and then you want to show "boss" in the
combobox as well?

Your combobox will show * not 'boss' the as lets the code know it's an
alias, you have to keep the * as thats the wildcard symbol used in the second
part of you code to select all records, swapping * for boss will only show
records named boss not all of them as you want

sorry should have noticed earlier - the " " around * should be ' ' instead ;

Me.Boss.RowSource = "SELECT Boss FROM tbllktable1 UNION SELECT " * " as Boss
FROM tbllktable1;"

hope this helps,

TonyT

It's on on_open

[quoted text clipped - 20 lines]

Any help is greatly appreciated.

 

[timglass via AccessMonster.com]

Here is the lne that I have put in the form it is on on open:

Me.Boss.RowSource = "SELECT Boss FROM tbllktable1 UNION SELECT '*' as Bogus
FROM tbllktable1;"

As of now the * is not selecting all records. That seems to be the only part
that's failing.

Here is what I am trying to accomplish:

I have a table that stores data about the amount of hours worked by
temporaries at our company. All the data is keyed manually. The table
contains 12 fields of data. I need to create a filter based on 5 fields Boss,
Department, Standard / Nonstandard, Part Name and a date range. Once I have
that filter working I need to be able to create a report from it. The user
needs a drop down box for each so that they can select the criteria for the
data they want to see. So they may have a specific part name they want to
choose but see all of the data in the other fields for that part name so that
is where they would need the wilcard, the asterik.

If you know of a better way of accomplishing this I am more than willing to
try it. Again thanks for all your help.

Thanks for your help, sorry about the confusion. Your last suggestion fixed
that problem except when I try to select a value in the combo box I get an
error. It is related to the asterik wildcard. What do I need to do in order
to use the asterik as a wildcard in my combo boxes so that when it is
selected all records for that field are returned? As long as I don't select
the asterik everything seems to work fine.

Thanks again!

Ok i'm a bit confused now, is the combobox called boss, and the table field
name in tbllktable1 also called boss, and then you want to show "boss" in the

[quoted text clipped - 19 lines]

 

[TonyT]

I am guessing that maybe you have a 5 column listbox to display the data
selected within this combobox, so, in the after_update event of the combox
'boss' try the following (change names etc as required;

me.myListBox.Rowsource = "SELECT Boss, Dept, Standard, Part, Date FROM
tbllktable1 WHERE Boss LIKE '" & me.boss & "'"

That should show data regardless of whether you select a *valid* selection
or the wildcard character to populate your listbox.

You will then have to pass a similar statement to your reports data source
to be able to print it.

TonyT

Here is the lne that I have put in the form it is on on open:

Me.Boss.RowSource = "SELECT Boss FROM tbllktable1 UNION SELECT '*' as Bogus
FROM tbllktable1;"

As of now the * is not selecting all records. That seems to be the only part
that's failing.

Here is what I am trying to accomplish:

I have a table that stores data about the amount of hours worked by
temporaries at our company. All the data is keyed manually. The table
contains 12 fields of data. I need to create a filter based on 5 fields Boss,
Department, Standard / Nonstandard, Part Name and a date range. Once I have
that filter working I need to be able to create a report from it. The user
needs a drop down box for each so that they can select the criteria for the
data they want to see. So they may have a specific part name they want to
choose but see all of the data in the other fields for that part name so that
is where they would need the wilcard, the asterik.

If you know of a better way of accomplishing this I am more than willing to
try it. Again thanks for all your help.

Thanks for your help, sorry about the confusion. Your last suggestion fixed
that problem except when I try to select a value in the combo box I get an
error. It is related to the asterik wildcard. What do I need to do in order
to use the asterik as a wildcard in my combo boxes so that when it is
selected all records for that field are returned? As long as I don't select
the asterik everything seems to work fine.

Thanks again!

Ok i'm a bit confused now, is the combobox called boss, and the table field
name in tbllktable1 also called boss, and then you want to show "boss" in the

[quoted text clipped - 19 lines]

Any help is greatly appreciated.

 

[timglass via AccessMonster.com]

Here is the lne that I have put in the form it is on on open:

Me.Boss.RowSource = "SELECT Boss FROM tbllktable1 UNION SELECT '*' as Bogus
FROM tbllktable1;"

As of now the * is not selecting all records. That seems to be the only part
that's failing.

Here is what I am trying to accomplish:

I have a table that stores data about the amount of hours worked by
temporaries at our company. All the data is keyed manually. The table
contains 12 fields of data. I need to create a filter based on 5 fields Boss,

Department, Standard / Nonstandard, Part Name and a date range. Once I have
that filter working I need to be able to create a report from it. The user
needs a drop down box for each so that they can select the criteria for the
data they want to see. So they may have a specific part name they want to
choose but see all of the data in the other fields for that part name so that
is where they would need the wilcard, the asterik.

If you know of a better way of accomplishing this I am more than willing to
try it. Again thanks for all your help.

Oops. Please ignore my last post.

Do you see the "*" in the combobox? This will tell you if the union query is
working. Could you post the code you're using to buld the filter?

Barry

I have created a database for tracking hours worked. I now need to create a
way for users to sort through the data. I have four fields that I need to

[quoted text clipped - 14 lines]

Any help is greatly appreciated.