Date select form

G

Guest

I have a form that I use to select a date criteria for my report. The form is
bound to a query and I have 5 fields on my form all linked to the query's
criteria. I have 2 fields that let me enter the starting & ending month and 2
that let me enter the starting & ending year. The 5th is for selecting the
customer. The report works fine when I chose any criteria within the same
year but when I select a starting month and year from 2005 and an ending
month & year from 2006 it doesn't work. It shows everything from 2005. Am I
missing something here on how this should be set up?
 
J

John Vinson

I have a form that I use to select a date criteria for my report. The form is
bound to a query and I have 5 fields on my form all linked to the query's
criteria. I have 2 fields that let me enter the starting & ending month and 2
that let me enter the starting & ending year. The 5th is for selecting the
customer. The report works fine when I chose any criteria within the same
year but when I select a starting month and year from 2005 and an ending
month & year from 2006 it doesn't work. It shows everything from 2005. Am I
missing something here on how this should be set up?
Click to expand...

Could you please post the SQL of the query, and describe the structure
of your table?

Do you have separate fields for year and month, or a Date/Time field
in the table?

John W. Vinson[MVP]
 
G

Guest

The field is a Date/Time field in my table. Here is the SQL;

PARAMETERS [Forms]![QuotesPGTypeSelect]![StartingMonth] Long,
[Forms]![QuotesPGTypeSelect]![EndingMonth] Long;
SELECT tblQuotesPinGage.QuoteID, tblQuotesPinGage.DateCreated,
tblQuotesPinGage.Customer, tblQuotesPinGage.[Order#],
tblQuotesPinGage.DatePOReceived, tblQuotesPinGage.DateOrderDue,
tblQuotesPinGage.QuoteTotal, tblQuotesPinGage.OrderTotal,
tblQuotesPinGage.SalesRep, tblQuotesPinGage.Type, tblQuotesPinGage.ShipDate,
tblQuotesPinGage.Results, Month([DateCreated]) AS MonthNum,
Year([DateCreated]) AS YearNum
FROM tblQuotesPinGage
WHERE (((tblQuotesPinGage.Type)=[Forms]![QuotesPGTypeSelect]![Type]) AND
((Month([DateCreated])) Between [Forms]![QuotesPGTypeSelect]![StartingMonth]
And [Forms]![QuotesPGTypeSelect]![EndingMonth]) AND ((Year([DateCreated]))
Between [Forms]![QuotesPGTypeSelect]![StartingYear] And
[Forms]![QuotesPGTypeSelect]![EndingYear]))
ORDER BY Month([DateCreated]), Year([DateCreated]);
 
J

John Vinson

WHERE (((tblQuotesPinGage.Type)=[Forms]![QuotesPGTypeSelect]![Type]) AND
((Month([DateCreated])) Between [Forms]![QuotesPGTypeSelect]![StartingMonth]
And [Forms]![QuotesPGTypeSelect]![EndingMonth]) AND ((Year([DateCreated]))
Between [Forms]![QuotesPGTypeSelect]![StartingYear] And
[Forms]![QuotesPGTypeSelect]![EndingYear]))
ORDER BY Month([DateCreated]), Year([DateCreated]);
Click to expand...

Well, this does exactly what you're asking it to do: which is NOT to
select records in date order. It's treating the year and the month
independently.

Try using the DateSerial() function to convert the form controls to
date/time values:

WHERE (((tblQuotesPinGage.Type)=[Forms]![QuotesPGTypeSelect]![Type])
AND [DateCreated] BETWEEN
DateSerial([Forms]![QuotesPGTypeSelect]![StartingYear],
[Forms]![QuotesPGTypeSelect]![StartingMonth], 1)
AND
DateSerial([Forms]![QuotesPGTypeSelect]![EndingYear],
[Forms]![QuotesPGTypeSelect]![EndingMonth] + 1, 0)
ORDER BY DateCreated;

John W. Vinson[MVP]
 
G

Guest

That worked perfectly. I understand how I did it wrong now. Thanks for your
help!

John Vinson said:
WHERE (((tblQuotesPinGage.Type)=[Forms]![QuotesPGTypeSelect]![Type]) AND
((Month([DateCreated])) Between [Forms]![QuotesPGTypeSelect]![StartingMonth]
And [Forms]![QuotesPGTypeSelect]![EndingMonth]) AND ((Year([DateCreated]))
Between [Forms]![QuotesPGTypeSelect]![StartingYear] And
[Forms]![QuotesPGTypeSelect]![EndingYear]))
ORDER BY Month([DateCreated]), Year([DateCreated]);
Click to expand...

Well, this does exactly what you're asking it to do: which is NOT to
select records in date order. It's treating the year and the month
independently.

Try using the DateSerial() function to convert the form controls to
date/time values:

WHERE (((tblQuotesPinGage.Type)=[Forms]![QuotesPGTypeSelect]![Type])
AND [DateCreated] BETWEEN
DateSerial([Forms]![QuotesPGTypeSelect]![StartingYear],
[Forms]![QuotesPGTypeSelect]![StartingMonth], 1)
AND
DateSerial([Forms]![QuotesPGTypeSelect]![EndingYear],
[Forms]![QuotesPGTypeSelect]![EndingMonth] + 1, 0)
ORDER BY DateCreated;

John W. Vinson[MVP]
Click to expand...
 
G

Guest

Actually I take that back. I didn't test it via the form when I responded
earlier. When I try to open the form I get a message saying "This expression
is typed incorrectly, or it is too complex to be evaluated." Any idea what's
causing this? It works fine when I run the query from the query screen.

John Vinson said:
WHERE (((tblQuotesPinGage.Type)=[Forms]![QuotesPGTypeSelect]![Type]) AND
((Month([DateCreated])) Between [Forms]![QuotesPGTypeSelect]![StartingMonth]
And [Forms]![QuotesPGTypeSelect]![EndingMonth]) AND ((Year([DateCreated]))
Between [Forms]![QuotesPGTypeSelect]![StartingYear] And
[Forms]![QuotesPGTypeSelect]![EndingYear]))
ORDER BY Month([DateCreated]), Year([DateCreated]);
Click to expand...

Well, this does exactly what you're asking it to do: which is NOT to
select records in date order. It's treating the year and the month
independently.

Try using the DateSerial() function to convert the form controls to
date/time values:

WHERE (((tblQuotesPinGage.Type)=[Forms]![QuotesPGTypeSelect]![Type])
AND [DateCreated] BETWEEN
DateSerial([Forms]![QuotesPGTypeSelect]![StartingYear],
[Forms]![QuotesPGTypeSelect]![StartingMonth], 1)
AND
DateSerial([Forms]![QuotesPGTypeSelect]![EndingYear],
[Forms]![QuotesPGTypeSelect]![EndingMonth] + 1, 0)
ORDER BY DateCreated;

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

Actually I take that back. I didn't test it via the form when I responded
earlier. When I try to open the form I get a message saying "This expression
is typed incorrectly, or it is too complex to be evaluated." Any idea what's
causing this? It works fine when I run the query from the query screen.
Click to expand...

I have no idea. What's the Recordsource property of the form? Where
else are you referring to the query on the form, or the form on the
query?

John W. Vinson[MVP]
 
G

Guest

The recordsource is set to the query. I'm referring to the query with the
text/combo boxes that are written in the sql. Nothing else.
 
J

John Vinson

The recordsource is set to the query. I'm referring to the query with the
text/combo boxes that are written in the sql. Nothing else.
Click to expand...

Very odd. You don't have the Type or the Year textbox in the
Parameters, though I don't think that should do it.

Try

PARAMETERS [Forms]![QuotesPGTypeSelect]![StartingMonth] Long,
[Forms]![QuotesPGTypeSelect]![EndingMonth] Long,
[Forms]![QuotesPGTypeSelect]![StartingYear] Long,
[Forms]![QuotesPGTypeSelect]![EndingYear] Long,
[Forms]![QuotesPGTypeSelect]![Type] Text;
SELECT tblQuotesPinGage.QuoteID, tblQuotesPinGage.DateCreated,
tblQuotesPinGage.Customer, tblQuotesPinGage.[Order#],
tblQuotesPinGage.DatePOReceived, tblQuotesPinGage.DateOrderDue,
tblQuotesPinGage.QuoteTotal, tblQuotesPinGage.OrderTotal,
tblQuotesPinGage.SalesRep, tblQuotesPinGage.Type,
tblQuotesPinGage.ShipDate,
tblQuotesPinGage.Results, Month([DateCreated]) AS MonthNum,
Year([DateCreated]) AS YearNum
FROM tblQuotesPinGage
WHERE (((tblQuotesPinGage.Type)=[Forms]![QuotesPGTypeSelect]![Type])
AND
[DateCreated] >= "#" &
DateSerial([Forms]![QuotesPGTypeSelect]![StartingYear],
[Forms]![QuotesPGTypeSelect]![StartingMonth], 1) & "#"
AND
"#" & DateSerial([Forms]![QuotesPGTypeSelect]![EndingYear],
[Forms]![QuotesPGTypeSelect]![EndingMonth] + 1, 0) & "#"
ORDER BY DateCreated;

Also check to be sure you don't have anything unexpected in the
OrderBy or Filter properties of the report.

John W. Vinson[MVP]
 
G

Guest

That didn't work either. But now I get that error message when I try to run
the query without using the form. I checked the report properties and
everything looks ok. Would it have anything to do with the combo boxes on my
form? The 4 date ones are set to value lists. The starting & ending month
ones are set to a value list with the month#. The starting & ending years are
also set to a value list with the 4 digit year as choices. And the "Type"
combo box is set to a separate sort query with these type listing.

John Vinson said:
The recordsource is set to the query. I'm referring to the query with the
text/combo boxes that are written in the sql. Nothing else.
Click to expand...

Very odd. You don't have the Type or the Year textbox in the
Parameters, though I don't think that should do it.

Try

PARAMETERS [Forms]![QuotesPGTypeSelect]![StartingMonth] Long,
[Forms]![QuotesPGTypeSelect]![EndingMonth] Long,
[Forms]![QuotesPGTypeSelect]![StartingYear] Long,
[Forms]![QuotesPGTypeSelect]![EndingYear] Long,
[Forms]![QuotesPGTypeSelect]![Type] Text;
SELECT tblQuotesPinGage.QuoteID, tblQuotesPinGage.DateCreated,
tblQuotesPinGage.Customer, tblQuotesPinGage.[Order#],
tblQuotesPinGage.DatePOReceived, tblQuotesPinGage.DateOrderDue,
tblQuotesPinGage.QuoteTotal, tblQuotesPinGage.OrderTotal,
tblQuotesPinGage.SalesRep, tblQuotesPinGage.Type,
tblQuotesPinGage.ShipDate,
tblQuotesPinGage.Results, Month([DateCreated]) AS MonthNum,
Year([DateCreated]) AS YearNum
FROM tblQuotesPinGage
WHERE (((tblQuotesPinGage.Type)=[Forms]![QuotesPGTypeSelect]![Type])
AND
[DateCreated] >= "#" &
DateSerial([Forms]![QuotesPGTypeSelect]![StartingYear],
[Forms]![QuotesPGTypeSelect]![StartingMonth], 1) & "#"
AND
"#" & DateSerial([Forms]![QuotesPGTypeSelect]![EndingYear],
[Forms]![QuotesPGTypeSelect]![EndingMonth] + 1, 0) & "#"
ORDER BY DateCreated;

Also check to be sure you don't have anything unexpected in the
OrderBy or Filter properties of the report.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

That didn't work either. But now I get that error message when I try to run
the query without using the form. I checked the report properties and
everything looks ok. Would it have anything to do with the combo boxes on my
form? The 4 date ones are set to value lists. The starting & ending month
ones are set to a value list with the month#. The starting & ending years are
also set to a value list with the 4 digit year as choices. And the "Type"
combo box is set to a separate sort query with these type listing.
Click to expand...

wierd. Sounds like something's gotten corrupted, or there was
something wrong with the SQL all along! Could you please repost the
current version of the SQL, and the Name and RowSource properties of
the combo boxes?

John W. Vinson[MVP]
 
G

Guest

I changed the SQL back to the original with the first set of changes you
made. The reason I did this is because it at least works from the query
itself. Here is the SQL and the combo box info

PARAMETERS [Forms]![QuotesPGTypeSelect]![StartingMonth] Long,
[Forms]![QuotesPGTypeSelect]![EndingMonth] Long,
[Forms]![QuotesPGTypeSelect]![StartingYear] Long,
[Forms]![QuotesPGTypeSelect]![EndingYear] Long,
[Forms]![QuotesPGTypeSelect]![Type] Text ( 255 );
SELECT [tblQuotesPinGage].[QuoteID], [tblQuotesPinGage].[DateCreated],
[tblQuotesPinGage].[Customer], [tblQuotesPinGage].[Order#],
[tblQuotesPinGage].[DatePOReceived], [tblQuotesPinGage].[DateOrderDue],
[tblQuotesPinGage].[QuoteTotal], [tblQuotesPinGage].[OrderTotal],
[tblQuotesPinGage].[SalesRep], [tblQuotesPinGage].[Type],
[tblQuotesPinGage].[ShipDate], [tblQuotesPinGage].[Results]
FROM tblQuotesPinGage
WHERE ((([tblQuotesPinGage].[DateCreated]) Between
DateSerial([Forms]![QuotesPGTypeSelect]![StartingMonth],[Forms]![QuotesPGTypeSelect]![StartingYear],1)
And
DateSerial([Forms]![QuotesPGTypeSelect]![EndingMonth],[Forms]![QuotesPGTypeSelect]![EndingYear]+1,0))
And (([tblQuotesPinGage].[Type])=[Forms]![QuotesPGTypeSelect]![Type]))
ORDER BY [tblQuotesPinGage].[DateCreated];

Combo Boxes:

Name: StartingMonth RowSource: 1;2;3;4;5;6;7;8;9;10;11;12
Name: EndingMonth RowSource: 1;2;3;4;5;6;7;8;9;10;11;12
Name: StartingYear RowSource: 2005;2006;2007
Name: EndingYear RowSource: 2005;2006;2007
Name: Type RowSource: qryQuotesTypePGSort
SQL of qryQuotesTypePGSort:
SELECT tblQuotesTypePinGage.Type, tblQuotesTypePinGage.Description
FROM tblQuotesTypePinGage
ORDER BY tblQuotesTypePinGage.Type;
 
G

Guest

Any luck with the SQL info I posted?

John Vinson said:
wierd. Sounds like something's gotten corrupted, or there was
something wrong with the SQL all along! Could you please repost the
current version of the SQL, and the Name and RowSource properties of
the combo boxes?

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

I changed the SQL back to the original with the first set of changes you
made. The reason I did this is because it at least works from the query
itself. Here is the SQL and the combo box info

PARAMETERS [Forms]![QuotesPGTypeSelect]![StartingMonth] Long,
[Forms]![QuotesPGTypeSelect]![EndingMonth] Long,
[Forms]![QuotesPGTypeSelect]![StartingYear] Long,
[Forms]![QuotesPGTypeSelect]![EndingYear] Long,
[Forms]![QuotesPGTypeSelect]![Type] Text ( 255 );
SELECT [tblQuotesPinGage].[QuoteID], [tblQuotesPinGage].[DateCreated],
[tblQuotesPinGage].[Customer], [tblQuotesPinGage].[Order#],
[tblQuotesPinGage].[DatePOReceived], [tblQuotesPinGage].[DateOrderDue],
[tblQuotesPinGage].[QuoteTotal], [tblQuotesPinGage].[OrderTotal],
[tblQuotesPinGage].[SalesRep], [tblQuotesPinGage].[Type],
[tblQuotesPinGage].[ShipDate], [tblQuotesPinGage].[Results]
FROM tblQuotesPinGage
WHERE ((([tblQuotesPinGage].[DateCreated]) Between
DateSerial([Forms]![QuotesPGTypeSelect]![StartingMonth],[Forms]![QuotesPGTypeSelect]![StartingYear],1)
And
DateSerial([Forms]![QuotesPGTypeSelect]![EndingMonth],[Forms]![QuotesPGTypeSelect]![EndingYear]+1,0))
Click to expand...

That's a problem right there: I think I got the order of arguments
wrong. It should be Year, Month, Day - so swap the first two arguments
in each DateSerial function call.


John W. Vinson[MVP]
 
G

Guest

Hi John,
Ok I swapped the arguments like you said but it still won't let me open the
form. It does work when I run it from the query itself but I still get the
error message when I try to open then form.

John Vinson said:
I changed the SQL back to the original with the first set of changes you
made. The reason I did this is because it at least works from the query
itself. Here is the SQL and the combo box info

PARAMETERS [Forms]![QuotesPGTypeSelect]![StartingMonth] Long,
[Forms]![QuotesPGTypeSelect]![EndingMonth] Long,
[Forms]![QuotesPGTypeSelect]![StartingYear] Long,
[Forms]![QuotesPGTypeSelect]![EndingYear] Long,
[Forms]![QuotesPGTypeSelect]![Type] Text ( 255 );
SELECT [tblQuotesPinGage].[QuoteID], [tblQuotesPinGage].[DateCreated],
[tblQuotesPinGage].[Customer], [tblQuotesPinGage].[Order#],
[tblQuotesPinGage].[DatePOReceived], [tblQuotesPinGage].[DateOrderDue],
[tblQuotesPinGage].[QuoteTotal], [tblQuotesPinGage].[OrderTotal],
[tblQuotesPinGage].[SalesRep], [tblQuotesPinGage].[Type],
[tblQuotesPinGage].[ShipDate], [tblQuotesPinGage].[Results]
FROM tblQuotesPinGage
WHERE ((([tblQuotesPinGage].[DateCreated]) Between
DateSerial([Forms]![QuotesPGTypeSelect]![StartingMonth],[Forms]![QuotesPGTypeSelect]![StartingYear],1)
And
DateSerial([Forms]![QuotesPGTypeSelect]![EndingMonth],[Forms]![QuotesPGTypeSelect]![EndingYear]+1,0))
Click to expand...

That's a problem right there: I think I got the order of arguments
wrong. It should be Year, Month, Day - so swap the first two arguments
in each DateSerial function call.


John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

Hi John,
Ok I swapped the arguments like you said but it still won't let me open the
form. It does work when I run it from the query itself but I still get the
error message when I try to open then form.
Click to expand...

All I can suggest at this point is to create a NEW form; use the query
as its Recordsource. Don't put *any* controls on it at first - just
see if it opens.

Then copy and paste controls a few at a time from the old form onto
the new one. There's *some* expression somewhere that's left over from
previous work on the form that's causing the error; I cannot guess
where, but if you suddenly start getting the error after copying some
control you'll know which one is messed up.

John W. Vinson[MVP]
 
G

Guest

John, I tried that as well. I just created a new form with no controls on it
at all. I made the recordsource the query and then tried to open the form. I
got the same message. This tells me that it could be the query??? Do you
agree?
 
G

Guest

John,
Ok this may sound weird but here's what I did to make it work. I removed the
whole "DateSerial" references and the "DateCreated" field from the query but
left all the combo boxes on my form. Now it works fine. How does it know what
month/year to select when there is no reference to the "DateCreated" field in
the query and no "DateSerial" reference either? It is working fine and
showing the "DateCreated" on the report but there is no reference to it. Does
this look/sound normal to you? Here is the new SQL that works.

PARAMETERS [Forms]![QuotesPGTypeSelect]![StartingMonth] Long,
[Forms]![QuotesPGTypeSelect]![EndingMonth] Long;
SELECT [tblQuotesPinGage].[QuoteID], [tblQuotesPinGage].[Customer],
[tblQuotesPinGage].[Order#],
[tblQuotesPinGage].[DatePOReceived], [tblQuotesPinGage].[DateOrderDue],
[tblQuotesPinGage].[QuoteTotal], [tblQuotesPinGage].[OrderTotal],
[tblQuotesPinGage].[SalesRep], [tblQuotesPinGage].[Type],
[tblQuotesPinGage].[ShipDate], [tblQuotesPinGage].[Results]
FROM tblQuotesPinGage


Combo Boxes:

Name: StartingMonth RowSource: 1;2;3;4;5;6;7;8;9;10;11;12
Name: EndingMonth RowSource: 1;2;3;4;5;6;7;8;9;10;11;12
Name: StartingYear RowSource: 2005;2006;2007
Name: EndingYear RowSource: 2005;2006;2007
Name: Type RowSource: qryQuotesTypePGSort
SQL of qryQuotesTypePGSort:
SELECT tblQuotesTypePinGage.Type, tblQuotesTypePinGage.Description
FROM tblQuotesTypePinGage
ORDER BY tblQuotesTypePinGage.Type;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Date range in months from month and year fields 3
Between Dates or All Dates if No Date Entered 3
Date Serial Problem 2
Access 2007 sorting problem 2
First and Last Days from "Month" 4
Create join where one value is not exact to other 2
Report Criteria in Form 3
Query criteria 4

Top