CROSSTAB QUERY CRITERIA PROBLEM 2

[stupiet]

To anyone who can help,

I have created a crosstab query which lists the total production for each day.

I want this query to only list data between a certain date range that a user enters in on a form. I tried putting "Where" in the "Total" field and then put the following code in the "criteria" field:

>= [Forms]![RetrieveForm]![StartDate]

After I run it, it tells me that the Jet database engine does not recognize the code.

I tried putting just a random date as the criteria, like this:

>= #01/01/02#

And this does work.

Can anyone please help and tell me how I can solve this?

I appreciate your time.

Thanks

Stupiet

 

[LLudden]

Can you post the query?

 

[mjmurray]

Hi have you tried something like this in your query
between 01/06/2002 and 30/06/2002

I seem to remeber doing one similar to this awhile back

 

[stupiet]

Here is my Query:

TRANSFORM Avg(MASTERQUERY.GoodPH) AS AvgOfGoodPH
SELECT MASTERQUERY.DateFin, MASTERQUERY.Core AS Winder
FROM MASTERQUERY
WHERE (((MASTERQUERY.DateFin)>=[Forms]![RETRIEVEREPORT]![StartDate] And (MASTERQUERY.DateFin)<=[Forms]![RETRIEVEREPORT]![EndDate]))
GROUP BY MASTERQUERY.DateFin, MASTERQUERY.Core
ORDER BY MASTERQUERY.Core
PIVOT MASTERQUERY.Name;

 

[oharab]

You need to specify the parameters explicitly.
In your query design view choose query->parameters.
in the box that appears put

[Forms]![RetrieveForm]![StartDate]

and choose &quot;value&quot; or &quot;Date/Time&quot; as the datatype.

your query should run fine now. You need to remember that if you change the parameters you need to change it in both places.

Good luck

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[stupiet]

Ben, it works perfect now! Thanks a lot for your help.

 

[LarryDeLaruelle]

Ben:

Thanks. I've been using some clunky workarounds on this problem for some time.

Glad I now have an appropriate solution. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[LarryDeLaruelle]

Ben:

Well, back to the drawing board.

If I just put a reference to the form/control it works fine.

If I add the >= and <= as in:

>=[Forms]![frmCrossTabTest]![txtStart] And <=[Forms]![frmCrossTabTest]![txtEnd]

it gives me an invalid bracketing of name message.

I've tried using separate lines for each part; I've tried using between; I've tried both value and date/time. All with the same results.

Without the operators, the results are not what I want.

Is it not possible to use operators or am I missing something? Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[oharab]

Larry,
In the parameters box, just put:
[Forms]![frmCrossTabTest]![txtStart]

And

[Forms]![frmCrossTabTest]![txtEnd]

ignore the logic signs.

Then put the full >=[Forms]![frmCrossTabTest]![txtStart] And <=[Forms]![frmCrossTabTest]![txtEnd]
on your criteria as normal. All you are doing is defining the parameters before you actually use them.

HTH

Ben
----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------

 

[LarryDeLaruelle]

Ben:

Thanks for the reply.

Ok. Followed the directions -- put the form/control references (sans logic signs) in the parameters dialog (tried both Date/Time and Value).

Put the date field to search on into the query grid and set it to Where and put the form/control references in the criteria cell (with the logic signs).

Works, after a fashion -- I don't get any error messages, but the query does not return any records either.

I double checked the dates coming from the form and, using that date range, I should get a result. (Yep, I checked the logic operators for correct position too.)

I appreciate you taking the time to help me with this; sure would like to get this thing functional. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[LarryDeLaruelle]

Ben:

I got it. I kept tinkering with it and noticed that I had not set the format for the date fields to Date. Once I did that it worked great.

Thanks again. Really appreciate the help. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[oharab]

Finally, solved!
I was starting to worry then. I'd run out of ideas.

I can sleep now.

Night!

B ----------------------------------
Ben O'Hara
bo104@westyorkshire.pnn.police.uk
----------------------------------