Search criteria for querys

[blake7]

Hi I have the following expression in a query six times searching for
different words in the “LIKE†area.

Expr1: DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'")

Everything works fine but I would like to enter some criteria between two
dates from my main table, I have tried making a form from the query which
displays the six boxes OK, then tried adding two text boxes or two combo
boxes to act as the criteria etc, but it does not seem to work

I have tried the following;

Between [forms]![my form name]![text1] and [forms]![my form name]![text2]

Is it possible to set criteria for an expression??
Regards Tony

 

[Steve Sanford]

If I understand right, you want to limit the records in counted in [main
Audit Data] where [Boiler Type] LIKE '*icos*' AND [SomeDateField] Between two
dates??

This gets ugly because of word wrap, but this shouold work. You *do* have a
date field in the main table??

--Untested--

Expr1: DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*' AND
[SomeDateField] Between #" & Format([forms]![my form
name]![text1],"m/d/yyyy") & "# and #" & Format([forms]![my form
name]![text2],"m/d/yyyy") & "#")



Copy the four lines and past into a query.

HTH

 

[blake7]

Hi Steve, thanks for you reply it works great, just one question i created a
form based on the query, when I open the form I am prompted for the start
date and end date as per the criteria, the form opens and the results are
shown, if I then enter two different dates into the text boxes and press
enter there is no change, in other words the query runs just the one time
when you open the form. Is it possible to do what I am asking, I wanted the
user to enter dates in the boxes on the form and see the results straight
away. Thanks for your time. Regards Tony

If I understand right, you want to limit the records in counted in [main
Audit Data] where [Boiler Type] LIKE '*icos*' AND [SomeDateField] Between two
dates??

This gets ugly because of word wrap, but this shouold work. You *do* have a
date field in the main table??

--Untested--

Expr1: DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*' AND
[SomeDateField] Between #" & Format([forms]![my form
name]![text1],"m/d/yyyy") & "# and #" & Format([forms]![my form
name]![text2],"m/d/yyyy") & "#")



Copy the four lines and past into a query.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi I have the following expression in a query six times searching for
different words in the “LIKE†area.

Expr1: DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'")

Everything works fine but I would like to enter some criteria between two
dates from my main table, I have tried making a form from the query which
displays the six boxes OK, then tried adding two text boxes or two combo
boxes to act as the criteria etc, but it does not seem to work

I have tried the following;

Between [forms]![my form name]![text1] and [forms]![my form name]![text2]

Is it possible to set criteria for an expression??
Regards Tony

 

[Steve Sanford]

You have to requery the recordsource (ie the query).

Add a button in the *form header*. Change the button caption to "ReQuery" or
"ReCount". Change the name from the default name to "btnRecount" (or like
that).

The code in the button click event would be

Me.Requery


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi Steve, thanks for you reply it works great, just one question i created a
form based on the query, when I open the form I am prompted for the start
date and end date as per the criteria, the form opens and the results are
shown, if I then enter two different dates into the text boxes and press
enter there is no change, in other words the query runs just the one time
when you open the form. Is it possible to do what I am asking, I wanted the
user to enter dates in the boxes on the form and see the results straight
away. Thanks for your time. Regards Tony

If I understand right, you want to limit the records in counted in [main
Audit Data] where [Boiler Type] LIKE '*icos*' AND [SomeDateField] Between two
dates??

This gets ugly because of word wrap, but this shouold work. You *do* have a
date field in the main table??

--Untested--

Expr1: DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*' AND
[SomeDateField] Between #" & Format([forms]![my form
name]![text1],"m/d/yyyy") & "# and #" & Format([forms]![my form
name]![text2],"m/d/yyyy") & "#")



Copy the four lines and past into a query.

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hi I have the following expression in a query six times searching for
different words in the “LIKE†area.

Expr1: DCount("*","[main Audit Data]","[Boiler Type] LIKE '*icos*'")

Everything works fine but I would like to enter some criteria between two
dates from my main table, I have tried making a form from the query which
displays the six boxes OK, then tried adding two text boxes or two combo
boxes to act as the criteria etc, but it does not seem to work

I have tried the following;

Between [forms]![my form name]![text1] and [forms]![my form name]![text2]

Is it possible to set criteria for an expression??
Regards Tony