Forms problem with Query

[Addy]

I have built a form that will run a query on click of the command
button. The query is pulling data from a table called Contracts.

In the Query I want to filter on a field called "Margin" Basically
I want to be able to see margins greater then 50%., less than n% and so
on and so forth.

On my form I have to controls, they are txt.Planned Margin and
combo_criteria.

On the text box a user can type in the value, and the combo allows them
to select the criteria like >,>=, <,<=, =.

For the queries parameter I have linked it back to the txt.Planned and
combo_criteria.

Problem is when I run this, nothing comes up with any value I enter.

I am guess its something with my operators (>,>=, <,<=, =.).

Here is the SQL.

SELECT [Modality Map].Modality, [Contract Cost Report].[Customer
Number], [Contract Cost Report].[Customer Name], [Contract Cost
Report].[Functional Location], [Contract Cost Report].[Functional Locn
Description], [Contract Cost Report].[Contract Start Date], [Contract
Cost Report].[Contract End Date], [Contract Cost Report].[Actual Margin
% YTD], [Contract Cost Report].[Planned Margin % YTD]
FROM [Modality Map] INNER JOIN [Contract Cost Report] ON [Modality
Map].[Cost ctr] = [Contract Cost Report].Modality
WHERE ((([Contract Cost Report].[Planned Margin %
YTD])=[Forms]![Contract Analysis]![ combo_criteria].[Text] &
[Forms]![Contract Analysis]![ txt.Planned].[Text]));

 

[roader]

WHERE ((([Contract Cost Report].[Planned Margin %
YTD])=[Forms]![Contract Analysis]![ combo_criteria].[Text] &
[Forms]![Contract Analysis]![ txt.Planned].[Text]));

When you do this, it will look like

WHERE ((([Contract Cost Report].[Planned Margin % YTD]) = >= 50
(Note the equal sign)


One solution will be to put the Criteria in the DoCmd.OpenQuery() 's
where condition

code will look something like this

dim strWhere as string

strWhere = "[Contract Cost Report]![Planned Margin % YTD]"&[
combo_criteria] &[ txt.Planned]
docmd.openquery(<all the other stuff>,strWhere)

Note that
If u are using access forms, [ txt.Planned].[Text] will not work
.....You can use [ txt.Planned].[Value] or just [
txt.Planned]

 

[Addy]

Is there a way to do this without jumping into VBA code?

WHERE ((([Contract Cost Report].[Planned Margin %
YTD])=[Forms]![Contract Analysis]![ combo_criteria].[Text] &
[Forms]![Contract Analysis]![ txt.Planned].[Text]));When you do this, it will look like

WHERE ((([Contract Cost Report].[Planned Margin % YTD]) = >= 50
(Note the equal sign)

One solution will be to put the Criteria in the DoCmd.OpenQuery() 's
where condition

code will look something like this

dim strWhere as string

strWhere = "[Contract Cost Report]![Planned Margin % YTD]"&[
combo_criteria] &[ txt.Planned]
docmd.openquery(<all the other stuff>,strWhere)

Note that
If u are using access forms, [ txt.Planned].[Text] will not work
....You can use [ txt.Planned].[Value] or just [
txt.Planned]

 

[Uncle Gizmo]

Try removing the .[Text] part, I don't think it is nessecary. (From
Both)

Cheers Tony

 

[roader]

Or you can use Between operator

Instead of providing a text field and a combo just provide two text
fields

say txtField1 and txtField2

Then in the query u can say
WHERE ((([Contract Cost Report].[Planned Margin % YTD]) Between
[forms]![yourForm]![txtField1] and [forms]![yourform]![txtfield2]

Make sure that the field [Planned Margin % YTD] is a number data type

if it is a text you can use cInt( [Planned Margin % YTD]) to make it a
number


I dont know if this work for your situation...Any way best of luck

 

[Addy]

Thanks I got it to work using VBA.