Run a query for the system day

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

Guest

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
 
Mark_Milly said:
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())
 
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
 
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]


Mark_Milly said:
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())));


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

BirthDay: Day(BirthDate)
Day(Date())
 
Thank you Marshall, you are a life saver................... :)

Marshall Barton said:
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]


Mark_Milly said:
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.

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

BirthDay: Day(BirthDate)
Day(Date())
 
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

Mark_Milly said:
Thank you Marshall, you are a life saver................... :)

Marshall Barton said:
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]


Mark_Milly said:
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())
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top