Expression entry no work.

[Jorge]

Hello again...

I have a query that prompts the user to enter in a date which then gives the
reults.
The user should be able to add expressions that get placed in the criteria
of the the query and also give results.

Can we enter operators?
such as..................... >10/31/2004

When we do this it comes back with an error stating the expression is to
complex to be evaluated or it is typed incorrectly etc...

Whats upwith that?

Jorge

From: "Anne" <[email protected]>
Subject: Re: Query / Form - user enter
Date: Friday, November 12, 2004 4:40 AM

Jorge,
You have to enter a parameter in your query.
Select the field where you enter your date criteria. In the criteria
section, enter something like [Please enter the Payroll Date
(DD/MM/YY)].
When the user open the query, he will be prompt with the message in
the brackets. In fact the brackets create the parameter.

Now if you want to update it from a form, this is easy as well:
Let's say your form is called frmPayroll and the control with the
Payroll date: PayrollDate.
In the criteria of the date field in your query enter:
FORMS!frmPayroll!PayrollDate
The query will use the date in the control as a criteria. Make sure
that the control is not null.

Anne

 

[Rick Brandt]

Hello again...

I have a query that prompts the user to enter in a date which then gives the
reults.
The user should be able to add expressions that get placed in the criteria
of the the query and also give results.

Can we enter operators?
such as..................... >10/31/2004

Dates need to be delimited with #.

 

[Dirk Goldgar]

Hello again...

I have a query that prompts the user to enter in a date which then
gives the reults.
The user should be able to add expressions that get placed in the
criteria of the the query and also give results.

Can we enter operators?
such as..................... >10/31/2004

When we do this it comes back with an error stating the expression is
to complex to be evaluated or it is typed incorrectly etc...

Whats upwith that?

You can't do this with a simple parameter query, because the parameter
has to be a single value, not a comparson expression. What you could do
is display a form with a text box in which the user will enter the date
value *or expression*, then use code behind the OK button on that form
to build a filter criterion from whatever was entered in that text box
and open a form to display the results, filtered by that criterion. It
might look soimething like this:

'----- start of example code -----
Private Sub cmdOK_Click()

Dim strCriterion As String

strCriterion = _
Application.BuildCriteria("DateField", dbDate, Me!txtCriterion)

DoCmd.OpenForm "frmQueryResults", WhereCondition:=strCriterion

End Sub
'----- end of example code -----

In the above, "DateField" would be the name of the date field in the
query, and "frmQueryResults" would be the name of a form with that query
as its recordsource. The query itself would not have any
parameter-based WHERE clause; it would return results for any date,
unless the filter is applied as shown above.

 

[John Vinson]

Hello again...

I have a query that prompts the user to enter in a date which then gives the
reults.
The user should be able to add expressions that get placed in the criteria
of the the query and also give results.

Can we enter operators?
such as..................... >10/31/2004

No, you cannot. Parameters can only contain the data to be searched,
*not* operators such as >, OR, BETWEEN and so on.

When we do this it comes back with an error stating the expression is to
complex to be evaluated or it is typed incorrectly etc...

Whats upwith that?

A misleading error message; it should say "you can't pass operators as
parameters".

If you want the user to be able to type in general query expressions
you will need to construct the SQL string in code. You need to hope
that they actually understand the sometimes tricky issues of Boolean
logical expressions; many users will be quite willing to put in an
expression such as

#10/31/2004# AND #11/4/2004#

because they want to see results from the first date and the second
date, and then be surprised when they get no answers (because OR is
the right operator for that search).

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[hildaechevarria]

Hello again...

I have a query that prompts the user to enter in a date which then gives
the
reults.
The user should be able to add expressions that get placed in the criteria
of the the query and also give results.

Can we enter operators?
such as..................... >10/31/2004

When we do this it comes back with an error stating the expression is to
complex to be evaluated or it is typed incorrectly etc...

Whats upwith that?

Jorge

From: "Anne" <[email protected]>
Subject: Re: Query / Form - user enter
Date: Friday, November 12, 2004 4:40 AM

Jorge,
You have to enter a parameter in your query.
Select the field where you enter your date criteria. In the criteria
section, enter something like [Please enter the Payroll Date
(DD/MM/YY)].
When the user open the query, he will be prompt with the message in
the brackets. In fact the brackets create the parameter.

Now if you want to update it from a form, this is easy as well:
Let's say your form is called frmPayroll and the control with the
Payroll date: PayrollDate.
In the criteria of the date field in your query enter:
FORMS!frmPayroll!PayrollDate
The query will use the date in the control as a criteria. Make sure
that the control is not null.

Anne

Hello

I have created query in which I display the several dates based on
payroll
end of week.
The table has a years worth of dates.

I know how to edit the query etc to enter the criteria needed.
But the user(s) do not! I'd like the user to select the query and it prompts
them for a date, the date is entered in the criteria for the query. Voila'
the query runs - giving the proper answer.

I also based a form on this query so I was hoping it will do the same
thing...

Thanx in advance..
Jorge