Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Data based on Next Month

Posted on 2011-10-26
Medium Priority
263 Views
I need to pull people from the database if their birthday month is next month.
How can I do this?

and Month({PATIENT.BIRTH_DATE}) = Current Month + 1
0
Question by:Becky Edwards
• 6
• 4

LVL 11

Expert Comment

ID: 37032247
If you are using SQL Server, try this:

select *
from PATIENT
where datepart(mm, PATIENT.BIRTH_DATE) =  datepart(mm, dateadd(mm,  1, getdate()))
0

Author Comment

ID: 37032268
I am putting this in the report selection on a crystal report.

The sequel server one won't work.
0

LVL 101

Expert Comment

ID: 37032533
Try this

You can't just compare the months because of year change.

mlmcc
0

Author Comment

ID: 37032661
I tried that formula and it didn't return anything.  And I know there should be stuff there because I have the report working with this formula, choosing 11 as the birth month.

Month({PATIENT.BIRTH_DATE}) = {?BirthMonth}

What am I doing wrong?
0

Author Comment

ID: 37032699
I am asking only that the birth month is 10 (or 11).

What I need now is to just build that piece into the report, so the user does not have to tell me what month they want, but that they just get all patients whose birth month is next month.
0

LVL 101

Expert Comment

ID: 37032711
Did you try my formula as the select formula?

mlmcc
0

Author Comment

ID: 37032777
Here is the one I meant to include.
0

Author Comment

ID: 37032785
0

LVL 101

Expert Comment

ID: 37032909
Did you try this one

The first one won't work because the dates are being compared with the year

mlmcc
0

LVL 101

Accepted Solution

mlmcc earned 500 total points
ID: 37033134
Another one that will work is

Month(CurrentDate)+1 = Month({PATIENT.BIRTH_DATE})
OR
Month(CurrentDate)+1 = Month({PATIENT.BIRTH_DATE}) +12

ANother idea

If Month(CurrentDate) = 12 then
Month({PATIENT.BIRTH_DATE}) = 1
Else
Month({PATIENT.BIRTH_DATE}) = Month(CurrentDate)+1

mlmcc
0

Author Closing Comment

ID: 37033955
Yes, they all work!  The first one seems to return the fastest, the second one takes the longest, but I prefer the third one because I know it will work in December, and I am not sure the first one will.  Since I won't be working on this or checking it in December for January, I will go the safest route, which is the third one.

Thank you so much!!!!!!!  :)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customerâ€™s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. â€¦
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirementsâ€¦
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calculâ€¦
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micrâ€¦
###### Suggested Courses
Course of the Month11 days, 6 hours left to enroll