query of recent or coming birthdays

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I cannot find the query expression that I need to find the birthdays 'between
today-15 and today+15'. The problem of course, is that I have to be able to
ignore the year in order to get the day and month to only apply to the filter.

Can someone help?
 
SELECT tblTest.*
FROM tblTest
WHERE (((DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))>=Date()
And
(DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))<=Date()+15));

In query design view, the expression in the Field row looks like this ...

DateSerial(Year(Date()),Month([TestTime]),Day([TestTime]))

.... while the expression in the Criteria row looks like this ...
 
Pardon me,

One problem with that is running it Between December 17th and December 31st
where you won't get the results you expect. For instance, DOB is January
3, 2000. If I run the original criteria, I won't see this birthday. I
think this modification to the criteria will solve this.

SELECT *
FROM tblTest
WHERE DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between Date() and Date() + 15
OR DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 15


Brendan Reynolds said:
SELECT tblTest.*
FROM tblTest
WHERE
(((DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))>=Date() And
(DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))<=Date()+15));

In query design view, the expression in the Field row looks like this ...

DateSerial(Year(Date()),Month([TestTime]),Day([TestTime]))

... while the expression in the Criteria row looks like this ...
=Date() And <=Date()+15
Click to expand...

--
Brendan Reynolds


BLTibbs said:
I cannot find the query expression that I need to find the birthdays
'between
today-15 and today+15'. The problem of course, is that I have to be able
to
ignore the year in order to get the day and month to only apply to the
filter.

Can someone help?
Click to expand...
Click to expand...
 
That works for the records with data, but I am getting '#Error' in the fields
where the value is null. How can I handle that?

Brendan Reynolds said:
SELECT tblTest.*
FROM tblTest
WHERE (((DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))>=Date()
And
(DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))<=Date()+15));

In query design view, the expression in the Field row looks like this ...

DateSerial(Year(Date()),Month([TestTime]),Day([TestTime]))

.... while the expression in the Criteria row looks like this ...
=Date() And <=Date()+15
Click to expand...

--
Brendan Reynolds


BLTibbs said:
I cannot find the query expression that I need to find the birthdays
'between
today-15 and today+15'. The problem of course, is that I have to be able
to
ignore the year in order to get the day and month to only apply to the
filter.

Can someone help?
Click to expand...
Click to expand...
 
Add an additional criteria to your query: "[DOB] IS NOT NULL". But see also
John Spencer's post elsewhere in this thread.

--
Brendan Reynolds


BLTibbs said:
That works for the records with data, but I am getting '#Error' in the
fields
where the value is null. How can I handle that?

Brendan Reynolds said:
SELECT tblTest.*
FROM tblTest
WHERE
(((DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))>=Date()
And
(DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))<=Date()+15));

In query design view, the expression in the Field row looks like this ...

DateSerial(Year(Date()),Month([TestTime]),Day([TestTime]))

.... while the expression in the Criteria row looks like this ...
=Date() And <=Date()+15
Click to expand...

--
Brendan Reynolds


BLTibbs said:
I cannot find the query expression that I need to find the birthdays
'between
today-15 and today+15'. The problem of course, is that I have to be
able
to
ignore the year in order to get the day and month to only apply to the
filter.

Can someone help?
Click to expand...
Click to expand...
Click to expand...
 
Thanks to both of you, and I ask for your patience, but where do I put the
code:

SELECT *
FROM tblTest
WHERE DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between Date() and Date() + 15
OR DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 15

I am still learning VBA

John Spencer said:
Pardon me,

One problem with that is running it Between December 17th and December 31st
where you won't get the results you expect. For instance, DOB is January
3, 2000. If I run the original criteria, I won't see this birthday. I
think this modification to the criteria will solve this.

SELECT *
FROM tblTest
WHERE DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between Date() and Date() + 15
OR DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 15


Brendan Reynolds said:
SELECT tblTest.*
FROM tblTest
WHERE
(((DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))>=Date() And
(DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))<=Date()+15));

In query design view, the expression in the Field row looks like this ...

DateSerial(Year(Date()),Month([TestTime]),Day([TestTime]))

... while the expression in the Criteria row looks like this ...
=Date() And <=Date()+15
Click to expand...

--
Brendan Reynolds


BLTibbs said:
I cannot find the query expression that I need to find the birthdays
'between
today-15 and today+15'. The problem of course, is that I have to be able
to
ignore the year in order to get the day and month to only apply to the
filter.

Can someone help?
Click to expand...
Click to expand...
Click to expand...
 
Assuming you have a query and a field named DOB and are using the query
grid, one way to handle this would be to add a new calculated column.

Field: CalcBirthDay: IIF(DOB is Not Null, DateSerial(Year(Date()),
Month(DOB),Day(DOB),Null)
Criteria (Line1): Between Date() and Date() + 15
Criteria (Line2): Between DateAdd("yyyy",-1,Date()) and
DateAdd("yyyy",-1,Date()) + 15


BLTibbs said:
Thanks to both of you, and I ask for your patience, but where do I put the
code:

SELECT *
FROM tblTest
WHERE DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between Date() and Date() + 15
OR DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 15

I am still learning VBA

John Spencer said:
Pardon me,

One problem with that is running it Between December 17th and December
31st
where you won't get the results you expect. For instance, DOB is
January
3, 2000. If I run the original criteria, I won't see this birthday. I
think this modification to the criteria will solve this.

SELECT *
FROM tblTest
WHERE DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between Date() and Date() + 15
OR DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 15


Brendan Reynolds said:
SELECT tblTest.*
FROM tblTest
WHERE
(((DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))>=Date()
And
(DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))<=Date()+15));

In query design view, the expression in the Field row looks like this
...

DateSerial(Year(Date()),Month([TestTime]),Day([TestTime]))

... while the expression in the Criteria row looks like this ...

=Date() And <=Date()+15

--
Brendan Reynolds


I cannot find the query expression that I need to find the birthdays
'between
today-15 and today+15'. The problem of course, is that I have to be
able
to
ignore the year in order to get the day and month to only apply to the
filter.

Can someone help?
Click to expand...
Click to expand...
Click to expand...
 
Dear John,

I have your formula in the where part of a query but when I click out of the
row, I get an error saying that I have an incorrect number of arguments. Do
you know where it is?

Here is the formula that I pasted in from your help last time.

CalcBirthDay:IIF([birthdate] is not null, DateSerial(Year(Date()),
Month([birthdate]), Day([birthdate]), Null)

John Spencer said:
Assuming you have a query and a field named DOB and are using the query
grid, one way to handle this would be to add a new calculated column.

Field: CalcBirthDay: IIF(DOB is Not Null, DateSerial(Year(Date()),
Month(DOB),Day(DOB),Null)
Criteria (Line1): Between Date() and Date() + 15
Criteria (Line2): Between DateAdd("yyyy",-1,Date()) and
DateAdd("yyyy",-1,Date()) + 15


BLTibbs said:
Thanks to both of you, and I ask for your patience, but where do I put the
code:

SELECT *
FROM tblTest
WHERE DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between Date() and Date() + 15
OR DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 15

I am still learning VBA

John Spencer said:
Pardon me,

One problem with that is running it Between December 17th and December
31st
where you won't get the results you expect. For instance, DOB is
January
3, 2000. If I run the original criteria, I won't see this birthday. I
think this modification to the criteria will solve this.

SELECT *
FROM tblTest
WHERE DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between Date() and Date() + 15
OR DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 15


SELECT tblTest.*
FROM tblTest
WHERE
(((DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))>=Date()
And
(DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))<=Date()+15));

In query design view, the expression in the Field row looks like this
...

DateSerial(Year(Date()),Month([TestTime]),Day([TestTime]))

... while the expression in the Criteria row looks like this ...

=Date() And <=Date()+15

--
Brendan Reynolds


I cannot find the query expression that I need to find the birthdays
'between
today-15 and today+15'. The problem of course, is that I have to be
able
to
ignore the year in order to get the day and month to only apply to the
filter.

Can someone help?
Click to expand...
Click to expand...
Click to expand...
 
You need two closing parentheses after the second "[birthdate]", e.g.
"Day([birthdate]))". One to close the Day() function, the second one to
close the DateSerial() function.
--
Brendan Reynolds

BLTibbs said:
Dear John,

I have your formula in the where part of a query but when I click out of
the
row, I get an error saying that I have an incorrect number of arguments.
Do
you know where it is?

Here is the formula that I pasted in from your help last time.

CalcBirthDay:IIF([birthdate] is not null, DateSerial(Year(Date()),
Month([birthdate]), Day([birthdate]), Null)

John Spencer said:
Assuming you have a query and a field named DOB and are using the query
grid, one way to handle this would be to add a new calculated column.

Field: CalcBirthDay: IIF(DOB is Not Null, DateSerial(Year(Date()),
Month(DOB),Day(DOB),Null)
Criteria (Line1): Between Date() and Date() + 15
Criteria (Line2): Between DateAdd("yyyy",-1,Date()) and
DateAdd("yyyy",-1,Date()) + 15


BLTibbs said:
Thanks to both of you, and I ask for your patience, but where do I put
the
code:

SELECT *
FROM tblTest
WHERE DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between Date() and Date() + 15
OR DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 15

I am still learning VBA

:

Pardon me,

One problem with that is running it Between December 17th and December
31st
where you won't get the results you expect. For instance, DOB is
January
3, 2000. If I run the original criteria, I won't see this birthday.
I
think this modification to the criteria will solve this.

SELECT *
FROM tblTest
WHERE DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between Date() and Date() + 15
OR DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 15


message
SELECT tblTest.*
FROM tblTest
WHERE
(((DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))>=Date()
And
(DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))<=Date()+15));

In query design view, the expression in the Field row looks like
this
...

DateSerial(Year(Date()),Month([TestTime]),Day([TestTime]))

... while the expression in the Criteria row looks like this ...

=Date() And <=Date()+15

--
Brendan Reynolds


I cannot find the query expression that I need to find the birthdays
'between
today-15 and today+15'. The problem of course, is that I have to
be
able
to
ignore the year in order to get the day and month to only apply to
the
filter.

Can someone help?
Click to expand...
Click to expand...
Click to expand...
 
It looks as if I missed a closing parentheses after Day([Birthdate])

CalcBirthDay:IIF([birthdate] is not null,
DateSerial(Year(Date()), Month([birthdate]), Day([birthdate])), Null)

BLTibbs said:
Dear John,

I have your formula in the where part of a query but when I click out of
the
row, I get an error saying that I have an incorrect number of arguments.
Do
you know where it is?

Here is the formula that I pasted in from your help last time.

CalcBirthDay:IIF([birthdate] is not null, DateSerial(Year(Date()),
Month([birthdate]), Day([birthdate]), Null)

John Spencer said:
Assuming you have a query and a field named DOB and are using the query
grid, one way to handle this would be to add a new calculated column.

Field: CalcBirthDay: IIF(DOB is Not Null, DateSerial(Year(Date()),
Month(DOB),Day(DOB),Null)
Criteria (Line1): Between Date() and Date() + 15
Criteria (Line2): Between DateAdd("yyyy",-1,Date()) and
DateAdd("yyyy",-1,Date()) + 15


BLTibbs said:
Thanks to both of you, and I ask for your patience, but where do I put
the
code:

SELECT *
FROM tblTest
WHERE DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between Date() and Date() + 15
OR DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 15

I am still learning VBA

:

Pardon me,

One problem with that is running it Between December 17th and December
31st
where you won't get the results you expect. For instance, DOB is
January
3, 2000. If I run the original criteria, I won't see this birthday.
I
think this modification to the criteria will solve this.

SELECT *
FROM tblTest
WHERE DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between Date() and Date() + 15
OR DateSerial(Year(Date()), Month(DOB),Day(DOB))
Between DateAdd("yyyy",-1,Date()) and DateAdd("yyyy",-1,Date()) + 15


message
SELECT tblTest.*
FROM tblTest
WHERE
(((DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))>=Date()
And
(DateSerial(Year(Date()),Month([TestTime]),Day([TestTime])))<=Date()+15));

In query design view, the expression in the Field row looks like
this
...

DateSerial(Year(Date()),Month([TestTime]),Day([TestTime]))

... while the expression in the Criteria row looks like this ...

=Date() And <=Date()+15

--
Brendan Reynolds


I cannot find the query expression that I need to find the birthdays
'between
today-15 and today+15'. The problem of course, is that I have to
be
able
to
ignore the year in order to get the day and month to only apply to
the
filter.

Can someone help?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top