Run a query for the system day

[Mark_Milly]

Hi Guys,

I hope you can help. I am a novice.

I am trying to run a report from a query to give me the birthday's for today
and so on. The birthdates are entered in dd/mm/yyyy, what my main problem is
the year is not the same as anyones else's. How do I run the report based on
the date of the system to bring up client's born on that Month and day what
ever the year is.

I hope you can help. Cheers

 

[Marshall Barton]

I am trying to run a report from a query to give me the birthday's for today
and so on. The birthdates are entered in dd/mm/yyyy, what my main problem is
the year is not the same as anyones else's. How do I run the report based on
the date of the system to bring up client's born on that Month and day what
ever the year is.

Add two calculated fields to the report's record source
query
BirthMonth: Month(BirthDate)
Month(Date())

BirthDay: Day(BirthDate)
Day(Date())

 

[Mark_Milly]

Hi Marshall,

I think I have done this right!!

It came up with an error saying the expression is too complex, blah, blah.
I have attached the SQL code to see if I did do it right or not.

SELECT [All New Data].[Ref #], [All New Data].[First Name], [All New
Data].[Last Name], [All New Data].DOB, [All New Data].Address, [All New
Data].Suburb, [All New Data].State, [All New Data].[New Client
table].Postcode, [BirthMonth] AS Expr1, [BirthDay] AS Expr2
FROM [All New Data]
WHERE ((([BirthMonth])=Month("BirthDate")) AND
(([BirthDay])=Day("BirthDay"))) OR ((([BirthMonth])=Month(Date())) AND
(([BirthDay])=Day(Date())));


Thanks in advance

 

[Marshall Barton]

No, not quite. I might have confused you by telling you
what to enter in the query design grid. Here's the SQL
view:

SELECT [All New Data].[Ref #], [All New Data].[First Name],
[All New Data].[Last Name], [All New Data].DOB,
[All New Data].Address, [All New Data].Suburb,
[All New Data].State, [All New Data].Postcode,
Month(DOB) AS BirthMonth, Day(DOB) AS BirthDay
FROM [All New Data]
WHERE Month(DOB)=Month(Date()) AND Day(DOB)=Day(Date())

There was an excess reference to [New Client table] that I
removed because it didn't seem to make sense, but maybe you
meant for it to do something?
--
Marsh
MVP [MS Access]

It came up with an error saying the expression is too complex, blah, blah.
I have attached the SQL code to see if I did do it right or not.

SELECT [All New Data].[Ref #], [All New Data].[First Name], [All New
Data].[Last Name], [All New Data].DOB, [All New Data].Address, [All New
Data].Suburb, [All New Data].State, [All New Data].[New Client
table].Postcode, [BirthMonth] AS Expr1, [BirthDay] AS Expr2
FROM [All New Data]
WHERE ((([BirthMonth])=Month("BirthDate")) AND
(([BirthDay])=Day("BirthDay"))) OR ((([BirthMonth])=Month(Date())) AND
(([BirthDay])=Day(Date())));

Add two calculated fields to the report's record source
query
BirthMonth: Month(BirthDate)
Month(Date())

BirthDay: Day(BirthDate)
Day(Date())

 

[Mark_Milly]

Thank you Marshall, you are a life saver...................

No, not quite. I might have confused you by telling you
what to enter in the query design grid. Here's the SQL
view:

SELECT [All New Data].[Ref #], [All New Data].[First Name],
[All New Data].[Last Name], [All New Data].DOB,
[All New Data].Address, [All New Data].Suburb,
[All New Data].State, [All New Data].Postcode,
Month(DOB) AS BirthMonth, Day(DOB) AS BirthDay
FROM [All New Data]
WHERE Month(DOB)=Month(Date()) AND Day(DOB)=Day(Date())

There was an excess reference to [New Client table] that I
removed because it didn't seem to make sense, but maybe you
meant for it to do something?
--
Marsh
MVP [MS Access]

It came up with an error saying the expression is too complex, blah, blah.
I have attached the SQL code to see if I did do it right or not.

SELECT [All New Data].[Ref #], [All New Data].[First Name], [All New
Data].[Last Name], [All New Data].DOB, [All New Data].Address, [All New
Data].Suburb, [All New Data].State, [All New Data].[New Client
table].Postcode, [BirthMonth] AS Expr1, [BirthDay] AS Expr2
FROM [All New Data]
WHERE ((([BirthMonth])=Month("BirthDate")) AND
(([BirthDay])=Day("BirthDay"))) OR ((([BirthMonth])=Month(Date())) AND
(([BirthDay])=Day(Date())));

Mark_Milly wrote:
I am trying to run a report from a query to give me the birthday's for today
and so on. The birthdates are entered in dd/mm/yyyy, what my main problem is
the year is not the same as anyones else's. How do I run the report based on
the date of the system to bring up client's born on that Month and day what
ever the year is.

Add two calculated fields to the report's record source
query
BirthMonth: Month(BirthDate)
Month(Date())

BirthDay: Day(BirthDate)
Day(Date())

 

[Mark_Milly]

Hi Guys,
I need to now redo the below SQl to show not todays date but one week from
today to send out birthday cards etc, can anyone help me please?

Cheers Mark

Thank you Marshall, you are a life saver...................

No, not quite. I might have confused you by telling you
what to enter in the query design grid. Here's the SQL
view:

SELECT [All New Data].[Ref #], [All New Data].[First Name],
[All New Data].[Last Name], [All New Data].DOB,
[All New Data].Address, [All New Data].Suburb,
[All New Data].State, [All New Data].Postcode,
Month(DOB) AS BirthMonth, Day(DOB) AS BirthDay
FROM [All New Data]
WHERE Month(DOB)=Month(Date()) AND Day(DOB)=Day(Date())

There was an excess reference to [New Client table] that I
removed because it didn't seem to make sense, but maybe you
meant for it to do something?
--
Marsh
MVP [MS Access]

It came up with an error saying the expression is too complex, blah, blah.
I have attached the SQL code to see if I did do it right or not.

SELECT [All New Data].[Ref #], [All New Data].[First Name], [All New
Data].[Last Name], [All New Data].DOB, [All New Data].Address, [All New
Data].Suburb, [All New Data].State, [All New Data].[New Client
table].Postcode, [BirthMonth] AS Expr1, [BirthDay] AS Expr2
FROM [All New Data]
WHERE ((([BirthMonth])=Month("BirthDate")) AND
(([BirthDay])=Day("BirthDay"))) OR ((([BirthMonth])=Month(Date())) AND
(([BirthDay])=Day(Date())));


Mark_Milly wrote:
I am trying to run a report from a query to give me the birthday's for today
and so on. The birthdates are entered in dd/mm/yyyy, what my main problem is
the year is not the same as anyones else's. How do I run the report based on
the date of the system to bring up client's born on that Month and day what
ever the year is.


:
Add two calculated fields to the report's record source
query
BirthMonth: Month(BirthDate)
Month(Date())

BirthDay: Day(BirthDate)
Day(Date())