parameter query

[bdb0704]

How can I make a parameter so that it only wants for you to enter in a month
only and in another parameter you enter in only the year. The date field is
set up xx/xx/xxxx but i just need month and year to enetr in

 

[Arvin Meyer [MVP]]

How can I make a parameter so that it only wants for you to enter in a
month
only and in another parameter you enter in only the year. The date field
is
set up xx/xx/xxxx but i just need month and year to enetr in

An easy way is to add 2 alias columns:

DateMonth: Month([DateField])

and

DateYear: Year([DateField])

and use a parameter on those 2 columns:

SELECT *, Month([DateField]) AS DateMonth, Year([DateField]) AS DateYear
FROM tblMyData
WHERE (((Month([DateField])) Like [Which Month]) AND ((Year([DateField]))
Like ([Which Year])));

 

[Tom van Stiphout]

On Wed, 24 Sep 2008 18:16:01 -0700, bdb0704

Create a query along these lines:
select * from MyTable
where Year(MyDate) = [Give Year:] and Month(MyDate) = [Give Month:]

Or you could create a nice form where the user could select these
parameters. Then you could add some validation as well, or provide
dropdowns or calendar controls.

-Tom.
Microsoft Access MVP

 

[Ken Sheridan]

Why the LIKE operator rather than the equality operator?

Ken Sheridan
Stafford, England

How can I make a parameter so that it only wants for you to enter in a
month
only and in another parameter you enter in only the year. The date field
is
set up xx/xx/xxxx but i just need month and year to enetr in

An easy way is to add 2 alias columns:

DateMonth: Month([DateField])

and

DateYear: Year([DateField])

and use a parameter on those 2 columns:

SELECT *, Month([DateField]) AS DateMonth, Year([DateField]) AS DateYear
FROM tblMyData
WHERE (((Month([DateField])) Like [Which Month]) AND ((Year([DateField]))
Like ([Which Year])));
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

I always use Like with user supplied parameters, because I normally would
have written it as:

SELECT Month([DateField]) AS DateMonth, Year([DateField]) AS DateYear, *
FROM tblMyData
WHERE (((Month([DateField])) Like [Which Month] & "*") AND
((Year([DateField])) Like ([Which Year]) & "*"));

so that the user can supply only the year or only the month.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Why the LIKE operator rather than the equality operator?

Ken Sheridan
Stafford, England

How can I make a parameter so that it only wants for you to enter in a
month
only and in another parameter you enter in only the year. The date
field
is
set up xx/xx/xxxx but i just need month and year to enetr in

An easy way is to add 2 alias columns:

DateMonth: Month([DateField])

and

DateYear: Year([DateField])

and use a parameter on those 2 columns:

SELECT *, Month([DateField]) AS DateMonth, Year([DateField]) AS DateYear
FROM tblMyData
WHERE (((Month([DateField])) Like [Which Month]) AND ((Year([DateField]))
Like ([Which Year])));
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Ken Sheridan]

There are a couple of potential problems with that approach:

1. It doesn't allow for Nulls, i.e. if a user elects to provide a value for
neither parameter in order to return all rows, in which case any row with a
Null DateField would not be returned.

2. It would also mean that when trying to return rows for January, rows for
October, November or December would also be returned as 10 Like "1*" etc
evaluate to True.

The following, on the other hand, would handle those scenarios:

SELECT *
FROM MyTable
WHERE (YEAR(DateField) = [Which Year]
OR [Which Year] IS NULL)
AND (MONTH(DateField) = [Which Month]
OR [Which Month] IS NULL);

Ken Sheridan
Stafford, England

I always use Like with user supplied parameters, because I normally would
have written it as:

SELECT Month([DateField]) AS DateMonth, Year([DateField]) AS DateYear, *
FROM tblMyData
WHERE (((Month([DateField])) Like [Which Month] & "*") AND
((Year([DateField])) Like ([Which Year]) & "*"));

so that the user can supply only the year or only the month.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Why the LIKE operator rather than the equality operator?

Ken Sheridan
Stafford, England

How can I make a parameter so that it only wants for you to enter in a
month
only and in another parameter you enter in only the year. The date
field
is
set up xx/xx/xxxx but i just need month and year to enetr in

An easy way is to add 2 alias columns:

DateMonth: Month([DateField])

and

DateYear: Year([DateField])

and use a parameter on those 2 columns:

SELECT *, Month([DateField]) AS DateMonth, Year([DateField]) AS DateYear
FROM tblMyData
WHERE (((Month([DateField])) Like [Which Month]) AND ((Year([DateField]))
Like ([Which Year])));
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Arvin Meyer [MVP]]

Which was why I didn't add the [& "*"] in my original function, and only
showed it as an answer to your question. Again, I normally use Like because
it works fine in BOTH situations.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

There are a couple of potential problems with that approach:

1. It doesn't allow for Nulls, i.e. if a user elects to provide a value
for
neither parameter in order to return all rows, in which case any row with
a
Null DateField would not be returned.

2. It would also mean that when trying to return rows for January, rows
for
October, November or December would also be returned as 10 Like "1*" etc
evaluate to True.

The following, on the other hand, would handle those scenarios:

SELECT *
FROM MyTable
WHERE (YEAR(DateField) = [Which Year]
OR [Which Year] IS NULL)
AND (MONTH(DateField) = [Which Month]
OR [Which Month] IS NULL);

Ken Sheridan
Stafford, England

I always use Like with user supplied parameters, because I normally would
have written it as:

SELECT Month([DateField]) AS DateMonth, Year([DateField]) AS DateYear, *
FROM tblMyData
WHERE (((Month([DateField])) Like [Which Month] & "*") AND
((Year([DateField])) Like ([Which Year]) & "*"));

so that the user can supply only the year or only the month.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Why the LIKE operator rather than the equality operator?

Ken Sheridan
Stafford, England

:

How can I make a parameter so that it only wants for you to enter in
a
month
only and in another parameter you enter in only the year. The date
field
is
set up xx/xx/xxxx but i just need month and year to enetr in

An easy way is to add 2 alias columns:

DateMonth: Month([DateField])

and

DateYear: Year([DateField])

and use a parameter on those 2 columns:

SELECT *, Month([DateField]) AS DateMonth, Year([DateField]) AS
DateYear
FROM tblMyData
WHERE (((Month([DateField])) Like [Which Month]) AND
((Year([DateField]))
Like ([Which Year])));
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Ken Sheridan]

Sorry to press the point, Arvin, but I don't think you've thought this
through fully. Try the following query on the Customers table in the sample
Northwind database using your approach:

PARAMETERS
[Enter Country] TEXT(50),
[Enter Region] TEXT(50);
SELECT *
FROM Customers
WHERE Country LIKE [Enter Country] & "*"
AND Region LIKE [Enter Region] & "*";

Now assuming you want to query the table by country only, open the query and
enter USA at the first prompt and press OK at the second without entering
anything. Now do the same but enter UK at the first prompt.

Now do exactly the same with this query:

PARAMETERS
[Enter Country] TEXT(50),
[Enter Region] TEXT(50);
SELECT *
FROM Customers
WHERE (Country = [Enter Country]
OR [Enter Country] IS NULL)
AND (Region = [Enter Region]
OR [Enter Region] IS NULL);

See what I mean? With your approach most UK customers are not returned as
NULL LIKE (NULL & "*") evaluates to NULL. In fact NULL LIKE anything
evaluates to NULL of course. When this is taken in conjunction with the
first parameter TRUE AND NULL evaluates to NULL, so even though a row
contains UK in the Country column if the Region column is NULL the row won't
be returned.

The reason for the Null region for London BTW is that the regional
administrative unit in the UK is the County, of which Isle of Wight is one.
Large metropolitan areas such as London, Birmingham etc are not in any
county. London is a collection of boroughs, and cities like Birmingham are
what is known as unitary authorities and exercise the functions which
elsewhere are split between two levels of local government.

Ken Sheridan
Stafford, England

Which was why I didn't add the [& "*"] in my original function, and only
showed it as an answer to your question. Again, I normally use Like because
it works fine in BOTH situations.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

There are a couple of potential problems with that approach:

1. It doesn't allow for Nulls, i.e. if a user elects to provide a value
for
neither parameter in order to return all rows, in which case any row with
a
Null DateField would not be returned.

2. It would also mean that when trying to return rows for January, rows
for
October, November or December would also be returned as 10 Like "1*" etc
evaluate to True.

The following, on the other hand, would handle those scenarios:

SELECT *
FROM MyTable
WHERE (YEAR(DateField) = [Which Year]
OR [Which Year] IS NULL)
AND (MONTH(DateField) = [Which Month]
OR [Which Month] IS NULL);

Ken Sheridan
Stafford, England

I always use Like with user supplied parameters, because I normally would
have written it as:

SELECT Month([DateField]) AS DateMonth, Year([DateField]) AS DateYear, *
FROM tblMyData
WHERE (((Month([DateField])) Like [Which Month] & "*") AND
((Year([DateField])) Like ([Which Year]) & "*"));

so that the user can supply only the year or only the month.

--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


Why the LIKE operator rather than the equality operator?

Ken Sheridan
Stafford, England

:

How can I make a parameter so that it only wants for you to enter in
a
month
only and in another parameter you enter in only the year. The date
field
is
set up xx/xx/xxxx but i just need month and year to enetr in

An easy way is to add 2 alias columns:

DateMonth: Month([DateField])

and

DateYear: Year([DateField])

and use a parameter on those 2 columns:

SELECT *, Month([DateField]) AS DateMonth, Year([DateField]) AS
DateYear
FROM tblMyData
WHERE (((Month([DateField])) Like [Which Month]) AND
((Year([DateField]))
Like ([Which Year])));
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com