473,569 Members | 2,475 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Trying to extract month from a date field to compare in report

Hi there,
I am trying to run a report using a parameter for where the user chooses a
month from a combo box. Then on the report, I want it to compare the month to
a date field and choose only those dates with the month chosen from the
parameter form.
Basically, its to see who's birthdays are coming up. So on the parameter form
you choose November, so then I want the report to display all the kids who's
birthdays are in November.
Any help would be appreciated as I just can't seem to extract the month from
the DateOfBirth field to compare to what they chose on the parameter form.
Thanks,
G

--
Message posted via http://www.accessmonster.com

Sep 19 '07 #1
3 5720
"gmazza via AccessMonster.c om" <u37142@uwewrot e in message
news:78784b8cc6 d54@uwe...
Hi there,
I am trying to run a report using a parameter for where the user chooses a
month from a combo box. Then on the report, I want it to compare the month
to
a date field and choose only those dates with the month chosen from the
parameter form.
Basically, its to see who's birthdays are coming up. So on the parameter
form
you choose November, so then I want the report to display all the kids
who's
birthdays are in November.
Any help would be appreciated as I just can't seem to extract the month
from
the DateOfBirth field to compare to what they chose on the parameter form.
Thanks,
G

--
Message posted via http://www.accessmonster.com
Try setting the filter in the report open event.

' caution - air code:
Private Sub Report_Open(Can cel As Integer)
Me.Filter = "Month([DateOfBirth])=" & Forms!frmMain!c boMonth
Me.FilterOn = True
End Sub

This assumes that your combo box control is named "cboMonth"
And the form is named "frmMain".

Another Suggestion:
It seems that you have the user select the desired month with the combo box
and then presumably click a button to run the report. This requires that the
form be open whenever you run the report. Normally, this might be fine. But
I find it to be a pain when doing development work, testing new reports, or
executing reports from the database window. Instead, I like to solicit the
user input in the report open event (when the input is relatively simple).
This allows the report to be a truly stand alone object, not dependant on
any other forms being open.

Here's how I do it:

' caution - air code:
Private Sub Report_Open(Can cel As Integer)
Dim intMonth As Integer
intMonth = Cint(InputBox(" Enter Desired Month (ie: 1-12)"))
Me.Filter = "Month([DateOfBirth])=" & intMonth
Me.FilterOn = True
End Sub

Good Luck,
Fred Zuckerman
Sep 20 '07 #2
"gmazza via AccessMonster.c om" <u37142@uwewrot e in
news:78784b8cc6 d54@uwe:
Hi there,
I am trying to run a report using a parameter for where the user
chooses a month from a combo box. Then on the report, I want it to
compare the month to a date field and choose only those dates with
the month chosen from the parameter form.
Basically, its to see who's birthdays are coming up. So on the
parameter form you choose November, so then I want the report to
display all the kids who's birthdays are in November.
Any help would be appreciated as I just can't seem to extract the
month from the DateOfBirth field to compare to what they chose on
the parameter form. Thanks,
G
Month([dateOfBirth]) returns a number from 1 to 12.
In the combobox, make sure that the bound column is the numeric
value for the month. It can be hidden, so the combobox just shows
the month name.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Sep 20 '07 #3
Hi there,
Maybe I'm explaining it wrong, or its not working as it is saying it can't
find frmMain when I try and run the report, maybe cause I do make the form
invisible when I run the report but I commented that out and I am getting a
different error. Here is a little more detail:
When the user clicks the report button from the main menu, a parameter form
comes up asking to choose a month. Once they choose the month, they click a
button saying run report. The control source of my report is a query which is
where I am trying to pull the month from the DateOfBirth field to compare to
the month they entered on the parameter form.
Thanks for your help so far, I hope I'm making sense, and I hope I am
understanding what you are trying to get me to do.
G

Fred Zuckerman wrote:
>Hi there,
I am trying to run a report using a parameter for where the user chooses a
[quoted text clipped - 12 lines]
>Thanks,
G

Try setting the filter in the report open event.

' caution - air code:
Private Sub Report_Open(Can cel As Integer)
Me.Filter = "Month([DateOfBirth])=" & Forms!frmMain!c boMonth
Me.FilterOn = True
End Sub

This assumes that your combo box control is named "cboMonth"
And the form is named "frmMain".

Another Suggestion:
It seems that you have the user select the desired month with the combo box
and then presumably click a button to run the report. This requires that the
form be open whenever you run the report. Normally, this might be fine. But
I find it to be a pain when doing development work, testing new reports, or
executing reports from the database window. Instead, I like to solicit the
user input in the report open event (when the input is relatively simple).
This allows the report to be a truly stand alone object, not dependant on
any other forms being open.

Here's how I do it:

' caution - air code:
Private Sub Report_Open(Can cel As Integer)
Dim intMonth As Integer
intMonth = Cint(InputBox(" Enter Desired Month (ie: 1-12)"))
Me.Filter = "Month([DateOfBirth])=" & intMonth
Me.FilterOn = True
End Sub

Good Luck,
Fred Zuckerman
--
Message posted via http://www.accessmonster.com

Sep 20 '07 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
5554
by: Keith Crooks | last post by:
I have an access database with a field set to the current date (this is taken from the system date how can i extract from that field just the month and year. or have a field that gives me only the month and year from the system ( I dont want to have to put in the month and year manually) regards keith
3
2444
by: Alec | last post by:
Hi All, I want to create a report listing all the people's birthdays that are in the current month, in other words, if I clicked to open the report today, it would show everyone who's birthday is in september. I would then want to maybe show only people who are turning 21, or 50, or whatever age I input. Any idea's anyone?
5
102375
by: rs | last post by:
I have a table with a timestamp field which contains the date and time. ie. 9/13/2004 9:10:00 AM. I would like to split this field into 2 fields, one with just the DATE portion ie 9/13/2004 and the other with just the TIME portion. ie 9:10:00 AM. I can make the table view display what I want by placing the same data in 3 fields and...
6
4830
by: Burghew | last post by:
Hello, I generate invoices for my customers evry month. I want to keep a form which will allow the user to select the Month and Year through a combo and thus generate reports based on the month selected. Also, please let me know how I can keep a copy of invoices which I generate for future reference. Awaiting eagerly for your replies ...
18
6623
by: PC Datasheet | last post by:
An Access user saw my name in a newsgroup and sent me a request for help on a project. As part of the project, a list of the dates in a month was needed. For anyone needing a list of dates in a month, here is what I used: 1. Create a table named TblNumbers with one field named Num and populate the table with 1 to 31 2. Create a query...
10
1251
by: Jim | last post by:
I'm sure this has been asked before but I can't find any postings. I have a table that has weekly inspections for multiple buildings. What I need to do is break these down by the week of the month of my choosing. Let me explain; I can get my week numbers no problem. I can get the date from the week numbers no problem. What I haven't been...
3
2555
by: HowHow | last post by:
I need to sort the "DateOfBirth" by the day (dd) regarless of month (mm)and year (yyyy). I have a query called q_DC_Client, in criteria, I am using this code below: Like "*" & "/" & !! & "/" & "*" I have a form called F_DC_Birthday with combo box called ComboBdayMonth. The combo takes information from T_Month table, where I only have two...
10
2543
by: Gilles Ganault | last post by:
Hello Out of curiosity, is there a smarter, easier way to read data sent by a form, and save them into a database? I have about 20 fields, and it'd be easier if I could just use a loop to go through an array and generate the SQL query in a couple of lines: ====== //If 'id' set -update; Otherwise -insert if($_POST)
9
1839
by: sparks | last post by:
Right now I had to build a report that allowed the people to check for gross outliers in their data input. short I am looking at 2.5* std dev + - anyway I used 2 dummy variables in the query the report is tied to. start date and end date. This pops up an input box for these values and they put in 11/01/08 and 11/31/08 and it runs the report...
0
7711
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7938
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8145
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7694
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
5245
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3679
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3667
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1236
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
970
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.