Query Parameters on a Form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Secret Squirrel - 22 Nov 2005 20:59 GMT

I have a form that is used to enter the parameters for a query. I have it set
up with 3 unbound text boxes to enter the starting month number, the ending
month number, and the year. When I enter a month number greater than 9 it
won't show any data on the report. But when I enter any starting and ending
numbers between 1 & 9 the report works fine. Anyone have any idea why it
won't work with 10 thru 12? Here is what I have in my query;

WHERE (((tblRMA.Customer)=[Forms]![frmCustomerSelect]![CustomerSelect]) AND
((Year([Date Notified]))=[Forms]![frmCustomerSelect]![Text6]) AND
((Month([Date Notified])) Between [Forms]![frmCustomerSelect]![Text2] And
[Forms]![frmCustomerSelect]![Text4]))
ORDER BY Year([Date Notified]), Month([Date Notified]);

Reply to this Message

John Spencer - 22 Nov 2005 22:16 GMT

I would try forcing the data type.

AND Month([Date Notified]) Between CLng([Forms]![frmCustomerSelect]![Text2]) And
CLng([Forms]![frmCustomerSelect]![Text4])

You can also set the parameter type either in the SQL Text or by using the query
grid and selecting parameters from the pop up menu for the query grid.

> I have a form that is used to enter the parameters for a query. I have it set
> up with 3 unbound text boxes to enter the starting month number, the ending
[quoted text clipped - 8 lines]
> [Forms]![frmCustomerSelect]![Text4]))
> ORDER BY Year([Date Notified]), Month([Date Notified]);

Reply to this Message

Secret Squirrel - 23 Nov 2005 00:49 GMT

If I was to use the query parameters then it would prompt me to enter the
data along with the text boxes. Is there a way around this?

> I would try forcing the data type.
>
[quoted text clipped - 16 lines]
> > [Forms]![frmCustomerSelect]![Text4]))
> > ORDER BY Year([Date Notified]), Month([Date Notified]);

Reply to this Message

John Spencer - 23 Nov 2005 03:01 GMT

The parameters you enter are
[Forms]![frmCustomerSelect]![Text2]
and
[Forms]![frmCustomerSelect]![Text4]

> If I was to use the query parameters then it would prompt me to enter the
> data along with the text boxes. Is there a way around this?
[quoted text clipped - 19 lines]
> > > [Forms]![frmCustomerSelect]![Text4]))
> > > ORDER BY Year([Date Notified]), Month([Date Notified]);

Reply to this Message

Secret Squirrel - 23 Nov 2005 12:48 GMT

That didn't work. It still did the same thing. Any other ideas?

> The parameters you enter are
> [Forms]![frmCustomerSelect]![Text2]
[quoted text clipped - 24 lines]
> > > > [Forms]![frmCustomerSelect]![Text4]))
> > > > ORDER BY Year([Date Notified]), Month([Date Notified]);

Reply to this Message

John Spencer - 23 Nov 2005 15:36 GMT

Not really.
Is the form open when you try to run the query?

Can you post the SQL text of your query?

At the beginning of the SQL statement you should have something like:
Parameters [Forms]![frmCustomerSelect]![Text2] Long,
[Forms]![frmCustomerSelect]![Text4] Long;
SELECT ...
FROM ...
WHERE (((tblRMA.Customer)=[Forms]![frmCustomerSelect]![CustomerSelect]) AND
((Year([Date Notified]))=[Forms]![frmCustomerSelect]![Text6]) AND
((Month([Date Notified])) Between [Forms]![frmCustomerSelect]![Text2] And
[Forms]![frmCustomerSelect]![Text4]))
ORDER BY Year([Date Notified]), Month([Date Notified]);

If this prompts you for
[Forms]![frmCustomerSelect]![Text2] and
[Forms]![frmCustomerSelect]![Text4]
then I would suspect that you either have the names of the controls
incorrect or the form is not open.

If this runs without the prompts, but gives you incorrect or no data, then I
would start looking at the data.

> That didn't work. It still did the same thing. Any other ideas?
>
[quoted text clipped - 38 lines]
>> > > > [Forms]![frmCustomerSelect]![Text4]))
>> > > > ORDER BY Year([Date Notified]), Month([Date Notified]);

Reply to this Message

Secret Squirrel - 23 Nov 2005 15:50 GMT

Yes the form is opened when I run the query. The form is what triggers the
query. Here is the SQL text of the query;

SELECT tblRMA.Customer, tblRMA.RMA, tblRMA.SalesRep, tblRMA.[WO#-Line#],
tblRMA.[Date Notified], tblRMA.[Discrepant Qty], tblRMA.[U/M],
tblRMA.[Discrepancy Description], tblRMA.[Customer Expectation],
tblRMA.[Discrepancy Code], tblRMA.[Date Disposition Made],
tblRMA.[Disposition Code], tblRMA.[Credit Amount], tblRMA.[Raw Mat'l Cost],
tblRMA.[Labor Cost], tblRMA.[In / Out Frt Cost], tblRMA.[Misc Cost],
tblRMA.DateClosed, [Raw Mat'l Cost]+[Labor Cost]+[In / Out Frt Cost]+[Misc
Cost] AS [Total Cost], Year([Date Notified]) AS YearNum, Month([Date
Notified]) AS MonthNum
FROM tblRMA
WHERE (((tblRMA.Customer)=[Forms]![frmCustomerSelect]![CustomerSelect]) AND
((Year([Date Notified]))=[Forms]![frmCustomerSelect]![Text6]) AND
((Month([Date Notified])) Between [Forms]![frmCustomerSelect]![Text2] And
[Forms]![frmCustomerSelect]![Text4]))
ORDER BY Year([Date Notified]), Month([Date Notified]);

> Not really.
> Is the form open when you try to run the query?
[quoted text clipped - 63 lines]
> >> > > > [Forms]![frmCustomerSelect]![Text4]))
> >> > > > ORDER BY Year([Date Notified]), Month([Date Notified]);

Reply to this Message

John Spencer - 23 Nov 2005 17:30 GMT

The SQL you posted has none of the modifications that have been suggested to
you. The following should work - have you tried this variation? IF so,
what happens? Error message, no records returned, wrong records returned,
parameter box requesting input?

Parameters [Forms]![frmCustomerSelect]![Text2] Long,
[Forms]![frmCustomerSelect]![Text4] Long;
SELECT tblRMA.Customer, tblRMA.RMA, tblRMA.SalesRep, tblRMA.[WO#-Line#],
tblRMA.[Date Notified], tblRMA.[Discrepant Qty], tblRMA.[U/M],
tblRMA.[Discrepancy Description], tblRMA.[Customer Expectation],
tblRMA.[Discrepancy Code], tblRMA.[Date Disposition Made],
tblRMA.[Disposition Code], tblRMA.[Credit Amount], tblRMA.[Raw Mat'l Cost],
tblRMA.[Labor Cost], tblRMA.[In / Out Frt Cost], tblRMA.[Misc Cost],
tblRMA.DateClosed, [Raw Mat'l Cost]+[Labor Cost]+[In / Out Frt Cost]+[Misc
Cost] AS [Total Cost], Year([Date Notified]) AS YearNum, Month([Date
Notified]) AS MonthNum
FROM tblRMA
WHERE (((tblRMA.Customer)=[Forms]![frmCustomerSelect]![CustomerSelect]) AND
((Year([Date Notified]))=[Forms]![frmCustomerSelect]![Text6]) AND
((Month([Date Notified])) Between [Forms]![frmCustomerSelect]![Text2] And
[Forms]![frmCustomerSelect]![Text4]))
ORDER BY Year([Date Notified]), Month([Date Notified]);

> Yes the form is opened when I run the query. The form is what triggers the
> query. Here is the SQL text of the query;
[quoted text clipped - 90 lines]
>> >> > > > [Forms]![frmCustomerSelect]![Text4]))
>> >> > > > ORDER BY Year([Date Notified]), Month([Date Notified]);

Reply to this Message

Secret Squirrel - 23 Nov 2005 17:49 GMT

I wanted to show you the original text of the SQL. I did put the code in the
way you wrote it and it works fine now. No problems whatsoever. Thank you
very much for your help John! Happy Turkey Day!

> The SQL you posted has none of the modifications that have been suggested to
> you. The following should work - have you tried this variation? IF so,
[quoted text clipped - 113 lines]
> >> >> > > > [Forms]![frmCustomerSelect]![Text4]))
> >> >> > > > ORDER BY Year([Date Notified]), Month([Date Notified]);

Reply to this Message

Dale Fye - 22 Nov 2005 22:19 GMT

Squirrel,

My guess is that the text boxes on your form are returning strings rather
than numbers. Why don't you use combo boxes instead? That way, you could
guarantee that the user will enter values within the range you want.

Try it like:

WHERE tblRMA.Customer = [Forms]![frmCustomerSelect]![CustomerSelect])
AND Year([Date Notified]) = Val([Forms]![frmCustomerSelect]![Text6])
AND Month([Date Notified]) Between
Val([Forms]![frmCustomerSelect]![Text2]) And
Val([Forms]![frmCustomerSelect]![Text4])
ORDER BY Year([Date Notified]), Month([Date Notified]);

HTH
Dale

>I have a form that is used to enter the parameters for a query. I have it
>set
[quoted text clipped - 12 lines]
> [Forms]![frmCustomerSelect]![Text4]))
> ORDER BY Year([Date Notified]), Month([Date Notified]);

Reply to this Message

Secret Squirrel - 23 Nov 2005 00:46 GMT

Dale,
Is the way you have it set up with a combo box?

> Squirrel,
>
[quoted text clipped - 30 lines]
> > [Forms]![frmCustomerSelect]![Text4]))
> > ORDER BY Year([Date Notified]), Month([Date Notified]);

Reply to this Message

Secret Squirrel - 23 Nov 2005 12:48 GMT

This didn't work either. Any other ideas?

> Squirrel,
>
[quoted text clipped - 30 lines]
> > [Forms]![frmCustomerSelect]![Text4]))
> > ORDER BY Year([Date Notified]), Month([Date Notified]);

Reply to this Message