Query based on form parameters

[James]

Dear group,

Can anyone tell me how to do the following?

I have a basic list of numbers in a table

I want to be able to search these by letting the user type "<10" or
">=50" on a simple form that has a list box showing the results

I have tried creating a query that uses the criteria the user types on
the form – as below

SELECT tblLocations.ICEID, tblLocations.LectureRoomTheatre
FROM tblLocations
WHERE (((tblLocations.LectureRoomTheatre)=[Forms]![frmVenueSelect]![txtTSCap]));

The form has a free text box that you can type anything.

But I get an error "The expression is typed incorrectly or is too
complex" etc

If I type <10 directly into the query it works just fine.


Best regards

James

[Wayne Morgan]

> WHERE
(((tblLocations.LectureRoomTheatre)=[Forms]![frmVenueSelect]![txtTSCap]));

If you replace your parameter with the text from the textbox, you will get

1) WHERE (((tblLocations.LectureRoomTheatre)=<10 Or >=50));
or if one at a time,
2) WHERE (((tblLocations.LectureRoomTheatre)=>=50));

Access will accept <= or =<, but not =>=. Also, in the first one it should
be
WHERE (((tblLocations.LectureRoomTheatre)=<10 Or
(tblLocations.LectureRoomTheatre)>=50));

If you are going to do this, the best thing may be to rewrite the SQL of the
query, concatenating in the text from the textbox.

CurrentDb.QueryDefs("MyQuery").SQL = "SELECT tblLocations.ICEID,
tblLocations.LectureRoomTheatre FROM tblLocations WHERE
(((tblLocations.LectureRoomTheatre)" & [Forms]![frmVenueSelect]![txtTSCap] &
"));"

This will work for a single item, such as in #2 above. It wil get more
complicated, but the basic idea remains the same, if you choose to allow
Ands and Ors.

--
Wayne Morgan
MS Access MVP
"James" <ja***********@hotmail.com> wrote in message
news:c8*************************@posting.google.co m...

Dear group,

Can anyone tell me how to do the following?

I have a basic list of numbers in a table

I want to be able to search these by letting the user type "<10" or
">=50" on a simple form that has a list box showing the results

I have tried creating a query that uses the criteria the user types on
the form - as below

SELECT tblLocations.ICEID, tblLocations.LectureRoomTheatre
FROM tblLocations
WHERE (((tblLocations.LectureRoomTheatre)=[Forms]![frmVenueSelect]![txtTSCap]));
The form has a free text box that you can type anything.

But I get an error "The expression is typed incorrectly or is too
complex" etc

If I type <10 directly into the query it works just fine.


Best regards

James

[James]

Wayne,

Thanks for the reply – still a bit stuck.

I replaced the query with the exact SQL you suggested – I got a nasty
error about syntax.

Then I made a slight change to this to make it work – I took out the
quotes.

SELECT tblLocations.ICEID, tblLocations.LectureRoomTheatre,
([tblLocations].[LectureRoomTheatre]) &
[Forms]![frmVenueSelect]![txtTSCap]
FROM tblLocations
WHERE (((([tblLocations].[LectureRoomTheatre]) &
[Forms]![frmVenueSelect]![txtTSCap])));

But the result I get is as follows:-

ICEID LectureRoomTheatre Expr1
6249 10 10>1
6734 2 2>1
7431 0 0>1
7432 0 0>1
7433 0 0>1
7434 0 0>1
Etc.

When I look at the SQL it has been changed to the following?!?!?!??!

SELECT tblLocations.ICEID, tblLocations.LectureRoomTheatre,
([tblLocations].[LectureRoomTheatre]) &
[Forms]![frmVenueSelect]![txtTSCap] AS Expr1
FROM tblLocations
WHERE (((([tblLocations].[LectureRoomTheatre]) &
[Forms]![frmVenueSelect]![txtTSCap])<>False));

What am I doing wrong?

James

[Wayne Morgan]

You can't replace the SQL with what I suggested by placing it directly in
the SQL view of the query. What I suggested was doing this in code, which
would have concatenated the value of the textbox into the SQL statement.

--
Wayne Morgan
MS Access MVP
"James" <ja***********@hotmail.com> wrote in message
news:c8**************************@posting.google.c om...

Wayne,

Thanks for the reply - still a bit stuck.

I replaced the query with the exact SQL you suggested - I got a nasty
error about syntax.

Then I made a slight change to this to make it work - I took out the
quotes.

SELECT tblLocations.ICEID, tblLocations.LectureRoomTheatre,
([tblLocations].[LectureRoomTheatre]) &
[Forms]![frmVenueSelect]![txtTSCap]
FROM tblLocations
WHERE (((([tblLocations].[LectureRoomTheatre]) &
[Forms]![frmVenueSelect]![txtTSCap])));

But the result I get is as follows:-

ICEID LectureRoomTheatre Expr1
6249 10 10>1
6734 2 2>1
7431 0 0>1
7432 0 0>1
7433 0 0>1
7434 0 0>1
Etc.

When I look at the SQL it has been changed to the following?!?!?!??!

SELECT tblLocations.ICEID, tblLocations.LectureRoomTheatre,
([tblLocations].[LectureRoomTheatre]) &
[Forms]![frmVenueSelect]![txtTSCap] AS Expr1
FROM tblLocations
WHERE (((([tblLocations].[LectureRoomTheatre]) &
[Forms]![frmVenueSelect]![txtTSCap])<>False));

What am I doing wrong?

James

[J. Paul Schmidt]

This example uses minimum and maximum dates which is the same idea as mimimum
and maximum numbers.

The free downloadable sample database at www.bullschmidt.com/access uses the
query by form concept so that on the invoices dialog one can optionally choose
a rep, a customer, and perhaps a date range, click on a button that says
"Input," and then have the invoice form open up showing all the invoices that
match the criteria.

And here is how the query by form concept can work.

On the invoices dialog there are the following controls:
InvDateMin with DefaultValue of =DateSerial(Year(Date())-1,1,1)
InvDateMax with DefaultValue of =Date()
InvRepNum with DefaultValue of *
InvCustNum with DefaultValue of *

Also on the invoices dialog there is a command button called cmdInput to open
the invoices form with the following code behind the OnClick property:
DoCmd.OpenForm "frmInv"

And of course there could be a button to open a report the same way:
DoCmd.OpenReport "rptInv", acViewPreview

The invoices form (frmInv) has RecordSource property of qryInv.

And the qryInv query's criteria for the InvDate field has:
Between [Forms]![frmInvDialog]![InvDateMin] And
[Forms]![frmInvDialog]![InvDateMax]

And the qryInv query's criteria for the RepNum field has:
Like [Forms]![frmInvDialog]![InvRepNum]

And the qryInv query's criteria for the CustNum field has:
Like [Forms]![frmInvDialog]![CustNum]

One related point is that you probably wouldn't want to allow blanks (i.e.
Nulls) in fields that are going to be used with Like in any criteria for that
field. Otherwise the blanks wouldn't be shown.

For example (based on what is entered into a last name search field):

Like 'Smith' would show Smith records

Like '' would show no records (probably not what one would want)

Like '*' would show all records

And to counter that I like to have the search fields have a DefaultValue of *
and not allow the search fields to be blank.

Or a more complicated solution would be to create the query's SQL statement
dynamically so that the criteria on a particular field isn't used unless
needed.

For example:

' Set strSQL.
strSQL = "SELECT * FROM MyTable WHERE (1=1)"
If Not IsNull(Rep) Then
strSQL = strSQL & " AND (Rep='" & Rep & "')"
End If
If Not IsNull(Customer) Then
strSQL = strSQL & " AND (Customer='" & Customer & "')"
End If

Best regards,
J. Paul Schmidt, Freelance Access and ASP Web Developer
www.Bullschmidt.com
Classic ASP Design Tips, ASP Web Database Demo, ASP Bar Chart Tool...