Report based on user criteria

  • Thread starter Thread starter Stephm
  • Start date Start date
S

Stephm

Hi. I want to run a report that shows all birthdays in a
month by letting the user select the month.

I think I did things the ineffecient way- by setting up a
table listing the 12 months, and then creating an unbound
form to select the month. Then I put the unbound form
info in the query [Forms]![Month Select]![FindMonth]

Here's where I'm having problems: the birthday is 1/12/99
and the selection criteria is January. So how does Access
know that 1/12/99 is in January? I don't care about
years, just that the birthday occurred in the correct
month. DateDiff? Some calendar function? I'm not very
VB/sql skilled. I appreciate any suggestions, details
very much a bonus. Thanks for your help! Steph
 
In your query:

WHERE Month([YourBirthdayField])=[Forms]![Month Select]![FindMonth]

HTH,
Debbie


| Hi. I want to run a report that shows all birthdays in a
| month by letting the user select the month.
|
| I think I did things the ineffecient way- by setting up a
| table listing the 12 months, and then creating an unbound
| form to select the month. Then I put the unbound form
| info in the query [Forms]![Month Select]![FindMonth]
|
| Here's where I'm having problems: the birthday is 1/12/99
| and the selection criteria is January. So how does Access
| know that 1/12/99 is in January? I don't care about
| years, just that the birthday occurred in the correct
| month. DateDiff? Some calendar function? I'm not very
| VB/sql skilled. I appreciate any suggestions, details
| very much a bonus. Thanks for your help! Steph
 
Thanks Debbie- that helped me get on the right track.
But I'm still having a bit of a problem. If I use
WHERE Month(DateOfBirth)=[Forms]![Month Select]!
[FindMonth]and enter "January" nothing pulls up. But if
I use

WHERE Month(DateOfBirth)= [Forms]![Month Select]![MonthID]
and enter "1" I get the correct January birthday records.

The sql I used for my unbound field FindMonth is
SELECT Months.Month
FROM Months
ORDER BY Months.MonthID;

So close but what am I missing? Thanks for your insight,
Steph
 
The Month(DateOfBirth) is looking for a number (1-12), not text. It sounds
like your Months table has 2 fields, MonthID and Month. Is your FindMonth
field a combo box? If so, in your combo box, use both fields (the MonthID
should be first), then make the column width for MonthID 0". That way the
query will be using the first column, but the user will see the second
column.

Hope that makes sense,
Debbie


| Thanks Debbie- that helped me get on the right track.
| But I'm still having a bit of a problem. If I use
| WHERE Month(DateOfBirth)=[Forms]![Month Select]!
| [FindMonth]and enter "January" nothing pulls up. But if
| I use
|
| WHERE Month(DateOfBirth)= [Forms]![Month Select]![MonthID]
| and enter "1" I get the correct January birthday records.
|
| The sql I used for my unbound field FindMonth is
| SELECT Months.Month
| FROM Months
| ORDER BY Months.MonthID;
|
| So close but what am I missing? Thanks for your insight,
| Steph
|
|
|
| >-----Original Message-----
| >In your query:
| >
| >WHERE Month([YourBirthdayField])=[Forms]![Month Select]!
| [FindMonth]
| >
| >HTH,
| >Debbie
| >
| >
| message
| >| >| Hi. I want to run a report that shows all birthdays in
| a
| >| month by letting the user select the month.
| >|
| >| I think I did things the ineffecient way- by setting
| up a
| >| table listing the 12 months, and then creating an
| unbound
| >| form to select the month. Then I put the unbound form
| >| info in the query [Forms]![Month Select]![FindMonth]
| >|
| >| Here's where I'm having problems: the birthday is
| 1/12/99
| >| and the selection criteria is January. So how does
| Access
| >| know that 1/12/99 is in January? I don't care about
| >| years, just that the birthday occurred in the correct
| >| month. DateDiff? Some calendar function? I'm not
| very
| >| VB/sql skilled. I appreciate any suggestions, details
| >| very much a bonus. Thanks for your help! Steph
| >
| >
| >.
| >
 
Debbie,
It does make sense- I've got everything the way I've done
my other parameter forms. Thanks for the reminder and
the help. I finally got it set up correctly and even
have the correct name of the unbound control ;-) Thanks
for your patience! I appreciate it- Steph
 
You're welcome. I'm glad I can help someone ... I've gotten so much help
from these groups and it feels good to give back!
Debbie

| Debbie,
| It does make sense- I've got everything the way I've done
| my other parameter forms. Thanks for the reminder and
| the help. I finally got it set up correctly and even
| have the correct name of the unbound control ;-) Thanks
| for your patience! I appreciate it- Steph
|
| >-----Original Message-----
| >The Month(DateOfBirth) is looking for a number (1-12),
| not text. It sounds
| >like your Months table has 2 fields, MonthID and Month.
| Is your FindMonth
| >field a combo box? If so, in your combo box, use both
| fields (the MonthID
| >should be first), then make the column width for MonthID
| 0". That way the
| >query will be using the first column, but the user will
| see the second
| >column.
| >
| >Hope that makes sense,
| >Debbie
| >
| >
| message
| >| >| Thanks Debbie- that helped me get on the right track.
| >| But I'm still having a bit of a problem. If I use
| >| WHERE Month(DateOfBirth)=[Forms]![Month Select]!
| >| [FindMonth]and enter "January" nothing pulls up. But
| if
| >| I use
| >|
| >| WHERE Month(DateOfBirth)= [Forms]![Month Select]!
| [MonthID]
| >| and enter "1" I get the correct January birthday
| records.
| >|
| >| The sql I used for my unbound field FindMonth is
| >| SELECT Months.Month
| >| FROM Months
| >| ORDER BY Months.MonthID;
| >|
| >| So close but what am I missing? Thanks for your
| insight,
| >| Steph
| >|
| >|
| >|
| >| >-----Original Message-----
| >| >In your query:
| >| >
| >| >WHERE Month([YourBirthdayField])=[Forms]![Month
| Select]!
| >| [FindMonth]
| >| >
| >| >HTH,
| >| >Debbie
| >| >
| >| >
| >| message
| >| >| >| >| Hi. I want to run a report that shows all birthdays
| in
| >| a
| >| >| month by letting the user select the month.
| >| >|
| >| >| I think I did things the ineffecient way- by setting
| >| up a
| >| >| table listing the 12 months, and then creating an
| >| unbound
| >| >| form to select the month. Then I put the unbound
| form
| >| >| info in the query [Forms]![Month Select]![FindMonth]
| >| >|
| >| >| Here's where I'm having problems: the birthday is
| >| 1/12/99
| >| >| and the selection criteria is January. So how does
| >| Access
| >| >| know that 1/12/99 is in January? I don't care about
| >| >| years, just that the birthday occurred in the
| correct
| >| >| month. DateDiff? Some calendar function? I'm not
| >| very
| >| >| VB/sql skilled. I appreciate any suggestions,
| details
| >| >| very much a bonus. Thanks for your help! Steph
| >| >
| >| >
| >| >.
| >| >
| >
| >
| >.
| >
 
Back
Top