using a form to query

[timglass via AccessMonster.com]

I have a table that I need to query with 5 variables. The first is date
range and that is no problem but where I am having a problem is with the
other fields. The other fields are text fields and are labled Supervisor,
Department, Standard/Nonstandard and Part Name. I have created a query from
the main table that holds all the history. Each of the four fields listed
have their own tables with values What I have done is create a form that
contains combo boxes from the fields. I then created a link in the query to
the corresponding fields. What I need help with is is there a wild card
character that can be entered by the user on the form for each field that
will select all records. I tried * but that only works within a query and
some of the fields have too many records for them not to be a drop down box.

 

[Trey Davis]

Tables and Queries must be used in the recordsource property of a form.

HTH

Trey Davis
Microsoft Access MVP

 

[Klatuu]

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.

 

[timglass via AccessMonster.com]

I know that but I have setup the form so that users have a way selecting
variables that are tied to a query without the need to type. What I need to
know is there a wild card character that can be used that the user can either
select or type in that will select all records at the form itself?

Tables and Queries must be used in the recordsource property of a form.

HTH

Trey Davis
Microsoft Access MVP

I have a table that I need to query with 5 variables. The first is date
range and that is no problem but where I am having a problem is with the

[quoted text clipped - 10 lines]

some of the fields have too many records for them not to be a drop down
box.

 

[Trey Davis]

Sorry, this issue is beyond the scope of help you may get from the
newsgroups

Trey Davis
*NOT* an MVP

I know that but I have setup the form so that users have a way selecting
variables that are tied to a query without the need to type. What I need
to
know is there a wild card character that can be used that the user can
either
select or type in that will select all records at the form itself?

Tables and Queries must be used in the recordsource property of a form.

HTH

Trey Davis
Microsoft Access MVP

I have a table that I need to query with 5 variables. The first is date
range and that is no problem but where I am having a problem is with the

[quoted text clipped - 10 lines]

some of the fields have too many records for them not to be a drop down
box.

 

[timglass via AccessMonster.com]

This is how i entered what you suggested:

Private Sub Supervisor_BeforeUpdate(Cancel As Integer)
Select Supervisor
From tbllktable1
UINION Select "*" as Bogus
From tbllktable1
End Sub;

But when I try to use the form I get a compile error: syntax error.
The first line is highlighted in yellow and the second line of text is
highlighted.

Any suggestions?

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.

I have a table that I need to query with 5 variables. The first is date
range and that is no problem but where I am having a problem is with the

[quoted text clipped - 7 lines]

will select all records. I tried * but that only works within a query and
some of the fields have too many records for them not to be a drop down box.

 

[Barry Gilbert]

The select statement that Kaltuu suggested needs to be out int he rowsource
or your comboboxes. This will have the effect of allowing the user to either
select a value from the table or a *, which will indicate that the filter
should return everything from this field.

How are you filtering records? If you're using VBA, you'll build a filter
string like:
Me.Filter = "MyField1 Like('" & Me.MyCombo1 & "') AND MyField2 Like ('" &
Me.MyCombo2 & "')" etc...

Barry

This is how i entered what you suggested:

Private Sub Supervisor_BeforeUpdate(Cancel As Integer)
Select Supervisor
From tbllktable1
UINION Select "*" as Bogus
From tbllktable1
End Sub;

But when I try to use the form I get a compile error: syntax error.
The first line is highlighted in yellow and the second line of text is
highlighted.

Any suggestions?

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.

I have a table that I need to query with 5 variables. The first is date
range and that is no problem but where I am having a problem is with the

[quoted text clipped - 7 lines]

will select all records. I tried * but that only works within a query and
some of the fields have too many records for them not to be a drop down box.