Help in MS Access

[Rajesh]

Dear friends

Please help me in following

I've an access data base , which has a table
called employee details. Following are the fileds in my
data base
1.Emp_ID
2.Emp_Name
3.Joining_Date
4.Membership_Expiry_Date
I'm looking for the following,
To generate a report,
Example: There are five members in table.All the five are
having different membership expiry date.I need to get a
report on base of expiry month and year.
Means = Member # 1 Expiriy date is 09-Aug-2003
Member # 2 Expiriy date is 20-Aug-2003
Member # 3 Expiriy date is 11-Sep-2003
Member # 4 Expiriy date is 11-Sep-2003
Member # 5 Expiriy date is 09-Dec-2003
I should be able select the month and date from a combo
box, which means If I selet Aug-2003 then my report
should show Member # 1 & Member # 2 only, if I select Dec-
2003 then the report should show only Member # 5 only.
Moreover is there any way to set up a popup message to
show the members list whose membership expiring the
current month.

Please give an idea to do the above.
many many thanks in advance

Regards

Raj

 

[John Vinson]

Means = Member # 1 Expiriy date is 09-Aug-2003
Member # 2 Expiriy date is 20-Aug-2003
Member # 3 Expiriy date is 11-Sep-2003
Member # 4 Expiriy date is 11-Sep-2003
Member # 5 Expiriy date is 09-Dec-2003
I should be able select the month and date from a combo
box, which means If I selet Aug-2003 then my report
should show Member # 1 & Member # 2 only, if I select Dec-
2003 then the report should show only Member # 5 only.

You can create a small form, frmCrit, with two unbound combo boxes:
cboMonth and cboYear, say. They should return the numbers 1 to 12 and
2003 to whenever you're interested (ten years out or so).

Put two calculated fields in your Query by typing

ExpMonth: Month([Expiry Date])
ExpYear: Year([Expiry Date])

and use criteria of

=Forms!frmCrit!cboMonth
and
=Forms!frmCrit!cboYear

respectively.

Moreover is there any way to set up a popup message to
show the members list whose membership expiring the
current month.

A criterion on Expiry Date of

BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0

will show these...

 

[Rajesh]

Thank you very much for the help

I've one more prob. I've merged the month and year in one
combobox now the answer shows as 8-2003, i have used the
following line in my query. Exp:Month([Expiry Date])&"-
"&Year([Expiry Date]).Now my requirement is to show the
result as Auguest-2003 instead of 8-2003.If change the
format of the combo then the filtering is not working
properly

Regards

Raj

-----Original Message-----

Means = Member # 1 Expiriy date is 09-Aug-2003
Member # 2 Expiriy date is 20-Aug-2003
Member # 3 Expiriy date is 11-Sep-2003
Member # 4 Expiriy date is 11-Sep-2003
Member # 5 Expiriy date is 09-Dec-2003
I should be able select the month and date from a combo
box, which means If I selet Aug-2003 then my report
should show Member # 1 & Member # 2 only, if I select Dec-
2003 then the report should show only Member # 5 only.

You can create a small form, frmCrit, with two unbound combo boxes:
cboMonth and cboYear, say. They should return the numbers 1 to 12 and
2003 to whenever you're interested (ten years out or so).

Put two calculated fields in your Query by typing

ExpMonth: Month([Expiry Date])
ExpYear: Year([Expiry Date])

and use criteria of

=Forms!frmCrit!cboMonth
and
=Forms!frmCrit!cboYear

respectively.

Moreover is there any way to set up a popup message to
show the members list whose membership expiring the
current month.

A criterion on Expiry Date of

BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0

will show these...



.

 

[Ken Snell]

Change the expression to this:

Exp:Format([Expiry Date], "mmmm-yyyy")

--
Ken Snell
<MS ACCESS MVP>
..

Thank you very much for the help

I've one more prob. I've merged the month and year in one
combobox now the answer shows as 8-2003, i have used the
following line in my query. Exp:Month([Expiry Date])&"-
"&Year([Expiry Date]).Now my requirement is to show the
result as Auguest-2003 instead of 8-2003.If change the
format of the combo then the filtering is not working
properly

Regards

Raj

-----Original Message-----

Means = Member # 1 Expiriy date is 09-Aug-2003
Member # 2 Expiriy date is 20-Aug-2003
Member # 3 Expiriy date is 11-Sep-2003
Member # 4 Expiriy date is 11-Sep-2003
Member # 5 Expiriy date is 09-Dec-2003
I should be able select the month and date from a combo
box, which means If I selet Aug-2003 then my report
should show Member # 1 & Member # 2 only, if I select Dec-
2003 then the report should show only Member # 5 only.

You can create a small form, frmCrit, with two unbound combo boxes:
cboMonth and cboYear, say. They should return the numbers 1 to 12 and
2003 to whenever you're interested (ten years out or so).

Put two calculated fields in your Query by typing

ExpMonth: Month([Expiry Date])
ExpYear: Year([Expiry Date])

and use criteria of

=Forms!frmCrit!cboMonth
and
=Forms!frmCrit!cboYear

respectively.

Moreover is there any way to set up a popup message to
show the members list whose membership expiring the
current month.

A criterion on Expiry Date of

BETWEEN DateSerial(Year(Date()), Month(Date()), 1) AND
DateSerial(Year(Date()), Month(Date()) + 1, 0

will show these...



.