By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,254 Members | 2,477 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,254 IT Pros & Developers. It's quick & easy.

help with report

P: 5
Hi,

I have a membership table where I collect the birthdays.....I separated the fields in to 'BirthDateDay', "BrithDateMonth', BirthDateYear'.... I also collect other birthdays for example wife birthdays, i have also separated them like the normal birthdays...
I then created a query that shows me all birthdays ('BirthDateMonth' and 'WifeBithDateMonth') that is in January which is simple.

What i am try to do with that is create a report where the date is sorted into alphabetically order and where only the month of January is showing...
for example if someone wife's birthday is in January and his birthday is in another month i.e. march, i want only the wife's birthdays to show...

what i have done so far is in the control source of the two textboxes in the report is if the BirthDateMonth or WifeBithDateMonth is not Januray that textbox should be blank e.g.
=IIf([BirthDateMonth]="January",[BirthDateDay] & " " & [BirthDateMonth]," ")
or
=IIf([WifeBirthDateMonth]="January",[wifeBirthDateDay] & " " & [wifeDateBirthMonth]," ")

The problem with that their a lot of blank spaces and that i the date sorted in alphabetically of date and into tabular form? The problem is i am having is setting the textboxes to false??
May 20 '07 #1
Share this Question
Share on Google+
9 Replies


puppydogbuddy
Expert 100+
P: 1,923
I think the easiest way to do this is to:

1. You only need one textbox, so delete one and change the control source of the remaining one to>>>>> Enter a month name

Place the following commands in your report launcher or button that launches each report:
Report #1
DoCmd.OpenReport "YourReportName", acViewPreview, , "[BirthDateMonth] = '" & [yourTextboxName] & "'"

Report #2
DoCmd.OpenReport "YourReportName", acViewPreview, , "[WifesBirthDateMonth] = '" & [yourTextboxName] & "'"

When the report opens, it should prompt you to enter the month name, and then return the desired results.
May 20 '07 #2

P: 5
I think the easiest way to do this is to:

1. You only need one textbox, so delete one and change the control source of the remaining one to>>>>> Enter a month name

Place the following commands in your report launcher or button that launches each report:
Report #1
DoCmd.OpenReport "YourReportName", acViewPreview, , "[BirthDateMonth] = '" & [yourTextboxName] & "'"

Report #2
DoCmd.OpenReport "YourReportName", acViewPreview, , "[WifesBirthDateMonth] = '" & [yourTextboxName] & "'"

When the report opens, it should prompt you to enter the month name, and then return the desired results.
doesnt this create to seperate reports, i want all in the same report
May 21 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
doesnt this create to seperate reports, i want all in the same report
[font=Tahoma]It is your same base report (the one with the query for all records). You would just open this same base report with the additional criteria added as shown. Here is how it works: If you enter January into the textbox and click button #1, you will get all birthdays for January; if you click button #2 you get all wifes birthdays for January. If you leave the textbox blank and click the button, you’ll get all birthdays or all wifes birthdays, as applicable.[/font]

[font=Tahoma] [/font]

[font=Tahoma]Button#1[/font]

[font=Tahoma]Private Sub btnBirthdays_Click()[/font]

DoCmd.OpenReport "YourBaseReportName", acViewPreview, , "[BirthDateMonth] = '" & [yourTextboxName] & "'"

End Sub



Button#2

Private Sub btnWifesBirthdays_Click()

DoCmd.OpenReport "YourBaseReportName", acViewPreview, , "[WifesBirthDateMonth] = '" & [yourTextboxName] & "'"

End Sub
May 21 '07 #4

P: 5
[font=Tahoma]It is your same base report (the one with the query for all records). You would just open this same base report with the additional criteria added as shown. Here is how it works: If you enter January into the textbox and click button #1, you will get all birthdays for January; if you click button #2 you get all wifes birthdays for January. If you leave the textbox blank and click the button, you’ll get all birthdays or all wifes birthdays, as applicable.[/font]

[font=Tahoma] [/font]

[font=Tahoma]Button#1[/font]

[font=Tahoma]Private Sub btnBirthdays_Click()[/font]

DoCmd.OpenReport "YourBaseReportName", acViewPreview, , "[BirthDateMonth] = '" & [yourTextboxName] & "'"

End Sub



Button#2

Private Sub btnWifesBirthdays_Click()

DoCmd.OpenReport "YourBaseReportName", acViewPreview, , "[WifesBirthDateMonth] = '" & [yourTextboxName] & "'"

End Sub
i am sorry, i am not understanding it you want me to create to buttons in a report??
May 21 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
i am sorry, i am not understanding it you want me to create to buttons in a report??
No, I assumed you had a reports launcher. How do you launch your reports? Don't you have a switchboard or reports menu form with buttons (or other ways) to select a specific report from a list of reports?
May 22 '07 #6

P: 5
No, I assumed you had a reports launcher. How do you launch your reports? Don't you have a switchboard or reports menu form with buttons (or other ways) to select a specific report from a list of reports?
thank you and sorry for the confusion.... let me try something new that seems to hard to do......

is it possible with access to take two fields and combine the data to put into one long field???
May 26 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
thank you and sorry for the confusion.... let me try something new that seems to hard to do......

is it possible with access to take two fields and combine the data to put into one long field???
Yes, it can be done. As an example, you can concatenate fields for lastName and firstName by putting the following expression in a textbox txtFullName on a form.

txtFullName = [FirstName] & [LastName]

where txtFullName is bound to a field called FullName in your table. There are other ways to combine data fields. Whether such a combination is meaningful depends on the application.
May 27 '07 #8

P: 5
Yes, it can be done. As an example, you can concatenate fields for lastName and firstName by putting the following expression in a textbox txtFullName on a form.

txtFullName = [FirstName] & [LastName]

where txtFullName is bound to a field called FullName in your table. There are other ways to combine data fields. Whether such a combination is meaningful depends on the application.
Thank You for the reply...
With this it just puts [FirstName] & [LastName] next to each other on the same line is it possible to put them on seperate lines

Thank You
May 30 '07 #9

puppydogbuddy
Expert 100+
P: 1,923
Thank You for the reply...
With this it just puts [FirstName] & [LastName] next to each other on the same line is it possible to put them on seperate lines

Thank You
You can put spaces between them like this: [FirstName] & " " & [LastName]

For separate lines, you can try:

[FirstName] & vbCrLf & [LastName]

or other syntax discussed in this thread:
http://www.thescripts.com/forum/thread199729.html
May 30 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.