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
13
Medium Priority
?
263 Views
Last Modified: 2012-05-12
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
Comment
Question by:Becky Edwards
  • 6
  • 4
11 Comments
 
LVL 11

Expert Comment

by:dougaug
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

by:Becky Edwards
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

by:mlmcc
ID: 37032533
Try this

({PATIENT.BIRTH_DATE}) IN DateAdd('m',1,Maximum(LastFullMonth)+1) to_ DateAdd('m',2,Maximum(LastFullMonth)+1)

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

 Month({PATIENT.BIRTH_DATE}) = Month(DateAdd('m',1,CurrentDate))

mlmcc
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 

Author Comment

by:Becky Edwards
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

by:Becky Edwards
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

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

mlmcc
0
 

Author Comment

by:Becky Edwards
ID: 37032777
Here is the one I meant to include. Birth Month query result
0
 

Author Comment

by:Becky Edwards
ID: 37032785
Yes, when I used yours I got no results back.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37032909
Did you try this one

Month({PATIENT.BIRTH_DATE}) = Month(DateAdd('m',1,CurrentDate))

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

mlmcc
0
 
LVL 101

Accepted Solution

by:
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

by:Becky Edwards
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question