Using partial dates in a query expression

[LaurenM]

Hi all,

I am trying to create an expression that will give me a numerical value
based on specific date criteria. I need to use only the month and day
information within a date. Since the year will always change, I can't include
it in my expression. For example, I want to write something like, If the
month and day is from 1/1 - 4/15, the result is 1. Do I need to convert the
date information to what the numerical equivalent would be in order to do
something like this? I am not all that confident with writing code but I
would appreciate any helpful advice with this.

Thank you in advance!
LaurenM

 

[KARL DEWEY]

Open your query in design view and scroll the grid to the right to a blank
column.
Click in the Field row and pull dow you date field name.
Edit that block in the grid to look like this --
Month_Day: Format([YourDateFieldName], "m/d")

In the Criteria row enter this --
[Enter Month/Day (1/14)]

 

[John Spencer]

If you want to ignore the year in a date then you have to strip out the
year in some fashion.

Format([SomeDate],"mmdd")

Or

Month([SomeDate])
Day ([SomeDate])

Or use Date Serial to give all dates the same year
DateSerial(2004,Month([SomeDate]),Day([SomeDate]))

Depending on which of these you select you will need to vary the criteria


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[LaurenM]

Hi John,

Thank you for your response. The query does work to change the format of the
dates but if I want then do a query with an IIf statement like
IIf([DateField]=>1/1 AND <4/15,1), it won't work. Maybe I am going about this
the wrong way. Any further advice would be greatly appreciated.

Thank you

If you want to ignore the year in a date then you have to strip out the
year in some fashion.

Format([SomeDate],"mmdd")

Or

Month([SomeDate])
Day ([SomeDate])

Or use Date Serial to give all dates the same year
DateSerial(2004,Month([SomeDate]),Day([SomeDate]))

Depending on which of these you select you will need to vary the criteria


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi all,

I am trying to create an expression that will give me a numerical value
based on specific date criteria. I need to use only the month and day
information within a date. Since the year will always change, I can't include
it in my expression. For example, I want to write something like, If the
month and day is from 1/1 - 4/15, the result is 1. Do I need to convert the
date information to what the numerical equivalent would be in order to do
something like this? I am not all that confident with writing code but I
would appreciate any helpful advice with this.

Thank you in advance!
LaurenM

 

[LaurenM]

Hi Karl,

Thank you for your response. The query does work to change the format of the
dates but if I want then do a query with an IIf statement like
IIf([DateField]=>1/1 AND <4/15,1), it won't work. Maybe I am going about this
the wrong way. Any further advice would be greatly appreciated.

Thanks!
LaurenM

Open your query in design view and scroll the grid to the right to a blank
column.
Click in the Field row and pull dow you date field name.
Edit that block in the grid to look like this --
Month_Day: Format([YourDateFieldName], "m/d")

In the Criteria row enter this --
[Enter Month/Day (1/14)]

--
KARL DEWEY
Build a little - Test a little

Hi all,

I am trying to create an expression that will give me a numerical value
based on specific date criteria. I need to use only the month and day
information within a date. Since the year will always change, I can't include
it in my expression. For example, I want to write something like, If the
month and day is from 1/1 - 4/15, the result is 1. Do I need to convert the
date information to what the numerical equivalent would be in order to do
something like this? I am not all that confident with writing code but I
would appreciate any helpful advice with this.

Thank you in advance!
LaurenM

 

[John Spencer]

Then the criteria if you used the first method would have to be

Where Format(DateField,"mmdd") Between "0101" and "0415"

or

Where Format(DateField,"mm/dd") between "01/01" and "04/15"


if you used date serial you would need
WHERE DateSerial(2004,Month(DateField),Day(DateField)) Between CDate([start
month and day] & "/2004") and CDate([End Month and Day] & "/2004")

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Hi John,

Thank you for your response. The query does work to change the format of the
dates but if I want then do a query with an IIf statement like
IIf([DateField]=>1/1 AND <4/15,1), it won't work. Maybe I am going about this
the wrong way. Any further advice would be greatly appreciated.

Thank you

If you want to ignore the year in a date then you have to strip out the
year in some fashion.

Format([SomeDate],"mmdd")

Or

Month([SomeDate])
Day ([SomeDate])

Or use Date Serial to give all dates the same year
DateSerial(2004,Month([SomeDate]),Day([SomeDate]))

Depending on which of these you select you will need to vary the criteria


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi all,

I am trying to create an expression that will give me a numerical value
based on specific date criteria. I need to use only the month and day
information within a date. Since the year will always change, I can't include
it in my expression. For example, I want to write something like, If the
month and day is from 1/1 - 4/15, the result is 1. Do I need to convert the
date information to what the numerical equivalent would be in order to do
something like this? I am not all that confident with writing code but I
would appreciate any helpful advice with this.

Thank you in advance!
LaurenM

 

[KARL DEWEY]

Use the calculated field I posted earlier.
If you are entering the information at prompt the use this --
In the Criteria row enter this --
Between [Enter Start Month/Day (1/14)] AND [Enter End Month/Day (2/10)]


--
KARL DEWEY
Build a little - Test a little

Hi Karl,

Thank you for your response. The query does work to change the format of the
dates but if I want then do a query with an IIf statement like
IIf([DateField]=>1/1 AND <4/15,1), it won't work. Maybe I am going about this
the wrong way. Any further advice would be greatly appreciated.

Thanks!
LaurenM

Open your query in design view and scroll the grid to the right to a blank
column.
Click in the Field row and pull dow you date field name.
Edit that block in the grid to look like this --
Month_Day: Format([YourDateFieldName], "m/d")

In the Criteria row enter this --
[Enter Month/Day (1/14)]

--
KARL DEWEY
Build a little - Test a little

Hi all,

I am trying to create an expression that will give me a numerical value
based on specific date criteria. I need to use only the month and day
information within a date. Since the year will always change, I can't include
it in my expression. For example, I want to write something like, If the
month and day is from 1/1 - 4/15, the result is 1. Do I need to convert the
date information to what the numerical equivalent would be in order to do
something like this? I am not all that confident with writing code but I
would appreciate any helpful advice with this.

Thank you in advance!
LaurenM