How to ignore the Year in a Date - Query

  • Thread starter Thread starter Kristin M
  • Start date Start date
K

Kristin M

Hi - I am trying to figure out how to retrieve all records from a range of
months regardless of the year. I've used the date part "M" but it still puts
a year in. I've also tried extracting the month of the date and converting
it to a number but that failed as well. I'm working in Access 2003.

The end result I need is a parameter query where the user specifies the
months they want to see (aka Jan - March) and all accounts with renewal dates
in those months, regardless of the year would show up.

Thanks for any help you can provide.
 
DatePart is a way to go, probably even preferable to Format.

DatePart("m", dateTimeField)
BETWEEN 1 AND 3


should do, to retreive data from January to March (inclusive), any year.


Vanderghast, Access MVP
 
Thank you! It works when I put "Between 1 and 2" in the Criteria. When I
add the parameter part: Between [Enter start month] and [Enter end month], it
returns any month with a 1 or 2 in it. I get months 1, 2, 10, 11 and 12.

Also, I'm ultimately looking to pull based on month and day, regardless of
year. Pulling by month was a second best solution. Is there a way to do
that?

Thanks so much for your help!
 
If 1, 2, 10, 11 and 12 comes out, it is because a STRING comparision is
performed rather than a NUMERICAL comparison. Indeed, "az" is between "a"
and "b", and so "10" is between "1" and "2". You should force your
parameters to be number, rather than letting Access guess, and comes with
the wrong solution (wrong about what you want). You can try, in SQL view,
something like:

PARAMETERS [Enter start month] Short, [Enter end month] Short;
SELECT ...



To get and months and dates, add another column:

DatePart("d", dateTimeField)
IN(1, 2, 3, 6, 7, 8, 9, 10, 13)

as example, or, using ""w" :

DatePart("w", dateTimeField)
NOT IN (1, 7)

to exclude Sunday and Saturday.



Vanderghast, Access MVP


Kristin M said:
Thank you! It works when I put "Between 1 and 2" in the Criteria. When I
add the parameter part: Between [Enter start month] and [Enter end month],
it
returns any month with a 1 or 2 in it. I get months 1, 2, 10, 11 and 12.

Also, I'm ultimately looking to pull based on month and day, regardless of
year. Pulling by month was a second best solution. Is there a way to do
that?

Thanks so much for your help!

vanderghast said:
DatePart is a way to go, probably even preferable to Format.

DatePart("m", dateTimeField)
BETWEEN 1 AND 3


should do, to retreive data from January to March (inclusive), any year.


Vanderghast, Access MVP
Click to expand...
Click to expand...
 
The syntax is to have a first line with all the parameters, then goes your
select statement.

Alternative, with the graphical interface, have you query as usual, then,
from the menu (Access 2003), under Query, the last item is Parameters... It
opens a form with two columns, one for the parameters names you use, and the
second, for the data type they should be. Once done, in graphical view, you
still can take a look at the SQL view, but that is not necessary (but still,
you can observe what it would have been if all the job would have been done
in the SQL view).


Vanderghast, Access MVP


Kristin M said:
Thank you - I am not familar with SQL so please forgive my lack of
knowledge
here...I went into SQL view and the statement now appears as follows and
I'm
getting a Syntax Error (missing operator):

SELECT....(all the fields I have selected)...FROM [table name] where
PARAMETERS [Enter Start Month] Short, [Enter End Month] Short, SELECT
Datepart ("M", [Event Date]);

Could you tell me what is wrong or missing from the statement?

Thanks again,
Kristin
vanderghast said:
If 1, 2, 10, 11 and 12 comes out, it is because a STRING comparision is
performed rather than a NUMERICAL comparison. Indeed, "az" is between "a"
and "b", and so "10" is between "1" and "2". You should force your
parameters to be number, rather than letting Access guess, and comes with
the wrong solution (wrong about what you want). You can try, in SQL view,
something like:

PARAMETERS [Enter start month] Short, [Enter end month] Short;
SELECT ...



To get and months and dates, add another column:

DatePart("d", dateTimeField)
IN(1, 2, 3, 6, 7, 8, 9, 10, 13)

as example, or, using ""w" :

DatePart("w", dateTimeField)
NOT IN (1, 7)

to exclude Sunday and Saturday.



Vanderghast, Access MVP


Kristin M said:
Thank you! It works when I put "Between 1 and 2" in the Criteria.
When I
add the parameter part: Between [Enter start month] and [Enter end
month],
it
returns any month with a 1 or 2 in it. I get months 1, 2, 10, 11 and
12.

Also, I'm ultimately looking to pull based on month and day, regardless
of
year. Pulling by month was a second best solution. Is there a way to
do
that?

Thanks so much for your help!

:

DatePart is a way to go, probably even preferable to Format.

DatePart("m", dateTimeField)
BETWEEN 1 AND 3


should do, to retreive data from January to March (inclusive), any
year.


Vanderghast, Access MVP


Hi - I am trying to figure out how to retrieve all records from a
range
of
months regardless of the year. I've used the date part "M" but it
still
puts
a year in. I've also tried extracting the month of the date and
converting
it to a number but that failed as well. I'm working in Access 2003.

The end result I need is a parameter query where the user specifies
the
months they want to see (aka Jan - March) and all accounts with
renewal
dates
in those months, regardless of the year would show up.

Thanks for any help you can provide.
Click to expand...
Click to expand...
Click to expand...
 
It worked classifying the parameters as decimals, thank you very much for
your help!! I also looked at the SQL view and now I understand what you were
saying.

vanderghast said:
The syntax is to have a first line with all the parameters, then goes your
select statement.

Alternative, with the graphical interface, have you query as usual, then,
from the menu (Access 2003), under Query, the last item is Parameters... It
opens a form with two columns, one for the parameters names you use, and the
second, for the data type they should be. Once done, in graphical view, you
still can take a look at the SQL view, but that is not necessary (but still,
you can observe what it would have been if all the job would have been done
in the SQL view).


Vanderghast, Access MVP


Kristin M said:
Thank you - I am not familar with SQL so please forgive my lack of
knowledge
here...I went into SQL view and the statement now appears as follows and
I'm
getting a Syntax Error (missing operator):

SELECT....(all the fields I have selected)...FROM [table name] where
PARAMETERS [Enter Start Month] Short, [Enter End Month] Short, SELECT
Datepart ("M", [Event Date]);

Could you tell me what is wrong or missing from the statement?

Thanks again,
Kristin
vanderghast said:
If 1, 2, 10, 11 and 12 comes out, it is because a STRING comparision is
performed rather than a NUMERICAL comparison. Indeed, "az" is between "a"
and "b", and so "10" is between "1" and "2". You should force your
parameters to be number, rather than letting Access guess, and comes with
the wrong solution (wrong about what you want). You can try, in SQL view,
something like:

PARAMETERS [Enter start month] Short, [Enter end month] Short;
SELECT ...



To get and months and dates, add another column:

DatePart("d", dateTimeField)
IN(1, 2, 3, 6, 7, 8, 9, 10, 13)

as example, or, using ""w" :

DatePart("w", dateTimeField)
NOT IN (1, 7)

to exclude Sunday and Saturday.



Vanderghast, Access MVP


Thank you! It works when I put "Between 1 and 2" in the Criteria.
When I
add the parameter part: Between [Enter start month] and [Enter end
month],
it
returns any month with a 1 or 2 in it. I get months 1, 2, 10, 11 and
12.

Also, I'm ultimately looking to pull based on month and day, regardless
of
year. Pulling by month was a second best solution. Is there a way to
do
that?

Thanks so much for your help!

:

DatePart is a way to go, probably even preferable to Format.

DatePart("m", dateTimeField)
BETWEEN 1 AND 3


should do, to retreive data from January to March (inclusive), any
year.


Vanderghast, Access MVP


Hi - I am trying to figure out how to retrieve all records from a
range
of
months regardless of the year. I've used the date part "M" but it
still
puts
a year in. I've also tried extracting the month of the date and
converting
it to a number but that failed as well. I'm working in Access 2003.

The end result I need is a parameter query where the user specifies
the
months they want to see (aka Jan - March) and all accounts with
renewal
dates
in those months, regardless of the year would show up.

Thanks for any help you can provide.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top