Parameter Query grabs only Month and Year specifically

[Marc]

I have several parameter queries set up for between certain dates, ie.
between 01/01/06 and 02/01/06.

However, I was wondering if I could minimize any difficulty and just type
'January or Jan 2006. Where Jan/January = month1...

I hope this is clear.

Thanks in advance,
Marc

 

[Allen Browne]

Marc, if you have a parameter named (say) MonthStart, you could use this
criteria:

= [MonthStart] And < DateAdd("m", 1, [MonthStart])

 

[Dirk Goldgar]

I have several parameter queries set up for between certain dates, ie.
between 01/01/06 and 02/01/06.

However, I was wondering if I could minimize any difficulty and just
type 'January or Jan 2006. Where Jan/January = month1...

The CDate function will interpret both "January 2006" and "Jan 2006" as
#1/1/2006#, so you could conceivable set up your query to accept a date
in that format and pull the month and year out of it; e.g.,

SELECT * FROM MyTable
WHERE MyDateField >= CDate([Enter Start Date (month year)];

Clearly that runs the risk of not getting an exactly appropriate date
specification, but it may be good enough for your purposes.

 

[Marc]

Thanks for the immediate response.

However, I keep getting this error message:

"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

Any common reason for this?

Thanks again,
Marc

I have several parameter queries set up for between certain dates, ie.
between 01/01/06 and 02/01/06.

However, I was wondering if I could minimize any difficulty and just
type 'January or Jan 2006. Where Jan/January = month1...

The CDate function will interpret both "January 2006" and "Jan 2006" as
#1/1/2006#, so you could conceivable set up your query to accept a date
in that format and pull the month and year out of it; e.g.,

SELECT * FROM MyTable
WHERE MyDateField >= CDate([Enter Start Date (month year)];

Clearly that runs the risk of not getting an exactly appropriate date
specification, but it may be good enough for your purposes.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Allen Browne]

It might help to declare the parameter, so Access knows it is meant to be a
date.

In query design view, choose Parameters on the Query menu.
Access opens the Parameters dialog.
Enter:
[Enter Start Date (month year)] Date/Time

You could also try CVDate() rather than CDate(), in case the parameter is
left blank.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the immediate response.

However, I keep getting this error message:

"This expression is typed incorrectly, or it is too complex to be
evaluated.
For example, a numeric expression may contain too many complicated
elements.
Try simplifying the expression by assigning parts of the expression to
variables."

Any common reason for this?

Thanks again,
Marc

I have several parameter queries set up for between certain dates, ie.
between 01/01/06 and 02/01/06.

However, I was wondering if I could minimize any difficulty and just
type 'January or Jan 2006. Where Jan/January = month1...

The CDate function will interpret both "January 2006" and "Jan 2006" as
#1/1/2006#, so you could conceivable set up your query to accept a date
in that format and pull the month and year out of it; e.g.,

SELECT * FROM MyTable
WHERE MyDateField >= CDate([Enter Start Date (month year)];

Clearly that runs the risk of not getting an exactly appropriate date
specification, but it may be good enough for your purposes.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Marc]

Thanks for the immediate reply.

Dirk,

Using your suggestion, when operator types in month "January 2006", it pulls
all records in January 2006 or specified Month, Year to current date. Is
there a way to only pull records for specified month instead of specified
month and those following?

I hope this helps.

Thanks again,
Marc

I have several parameter queries set up for between certain dates, ie.
between 01/01/06 and 02/01/06.

However, I was wondering if I could minimize any difficulty and just
type 'January or Jan 2006. Where Jan/January = month1...

The CDate function will interpret both "January 2006" and "Jan 2006" as
#1/1/2006#, so you could conceivable set up your query to accept a date
in that format and pull the month and year out of it; e.g.,

SELECT * FROM MyTable
WHERE MyDateField >= CDate([Enter Start Date (month year)];

Clearly that runs the risk of not getting an exactly appropriate date
specification, but it may be good enough for your purposes.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks for the immediate response.

However, I keep getting this error message:

"This expression is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning
parts of the expression to variables."

Any common reason for this?

I accidentally left off the closing parenthesis. The example SQL should
have been:

SELECT * FROM MyTable
WHERE MyDateField >= CDate([Enter Start Date (month year)]);

 

[Dirk Goldgar]

Thanks for the immediate reply.

Dirk,

Using your suggestion, when operator types in month "January 2006",
it pulls all records in January 2006 or specified Month, Year to
current date. Is there a way to only pull records for specified
month instead of specified month and those following?

I guess you found and fixed my typo in the original SQL.

Sure, you can combine what I suggested with what Allen suggested:

SELECT * FROM MyTable
WHERE
MyDateField >=
CDate([Enter Month (month year)])
AND
MyDateField <
DateAdd("m", 1, CDate([Enter Month (month year)]);

 

[Marc]

Dirk and Allan,

Thank you both for your valuable input on this matter.

Whenever "January 2006" is entered into the query, all records included are
that of January 2006 up to the present date.

So, if I want to look at January 2006 records, the query grabs records from
January 2006, Feb 2006, March 2006, April 2006, and so on up to the present
date. As if January 2006 is only a "start month" per say.

How can I eliminate the other monthly records pulled and only display the
exact monthly records queried. For instance, when I query "January 2006", I
only want to see records in January 2006 and not the forthcoming months.

Please help.
Both of you have been great in this effort.

I only hope I have made my purpose more specific and clearer for a final
solution.

Thanks again,
Marc

Thanks for the immediate reply.

Dirk,

Using your suggestion, when operator types in month "January 2006",
it pulls all records in January 2006 or specified Month, Year to
current date. Is there a way to only pull records for specified
month instead of specified month and those following?

I guess you found and fixed my typo in the original SQL.

Sure, you can combine what I suggested with what Allen suggested:

SELECT * FROM MyTable
WHERE
MyDateField >=
CDate([Enter Month (month year)])
AND
MyDateField <
DateAdd("m", 1, CDate([Enter Month (month year)]);

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Douglas J. Steele]

Did you include the

AND
MyDateField <
DateAdd("m", 1, CDate([Enter Month (month year)]);

portion DIrk included in his response? Did you ensure that the parameter
([Enter Month (month year)] above) is typed identically in both places?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dirk and Allan,

Thank you both for your valuable input on this matter.

Whenever "January 2006" is entered into the query, all records included
are
that of January 2006 up to the present date.

So, if I want to look at January 2006 records, the query grabs records
from
January 2006, Feb 2006, March 2006, April 2006, and so on up to the
present
date. As if January 2006 is only a "start month" per say.

How can I eliminate the other monthly records pulled and only display the
exact monthly records queried. For instance, when I query "January 2006",
I
only want to see records in January 2006 and not the forthcoming months.

Please help.
Both of you have been great in this effort.

I only hope I have made my purpose more specific and clearer for a final
solution.

Thanks again,
Marc

Thanks for the immediate reply.

Dirk,

Using your suggestion, when operator types in month "January 2006",
it pulls all records in January 2006 or specified Month, Year to
current date. Is there a way to only pull records for specified
month instead of specified month and those following?

I guess you found and fixed my typo in the original SQL.

Sure, you can combine what I suggested with what Allen suggested:

SELECT * FROM MyTable
WHERE
MyDateField >=
CDate([Enter Month (month year)])
AND
MyDateField <
DateAdd("m", 1, CDate([Enter Month (month year)]);

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

How can I eliminate the other monthly records pulled and only display
the exact monthly records queried. For instance, when I query
"January 2006", I only want to see records in January 2006 and not
the forthcoming months.

The SQL I posted, suitably modified to fit your table and field names,
should have done that. If Doug's clarification didn't set it right,
please post the SQL of the query you tried.