prompt for date and insert into letter

[rinova]

Hi everyone,

I'm using MS Access 2002 and I need some help with the following, I'm trying to create a prompt for a date and then enter that date into the body of the letter.

Also can the date format be short and converted into a long format on the letter.

Example:
Prompt - Enter Date
Date entered = 12/31/2013

Sample Letter:

Your subscription ends on December 31, 2013 if you would like to renew etc.....

How can I accomplish this?

Thank you,
Rich

 

[David R]

Yes and yes.

Are there rules for when that date is? 12 months out, rounded up? End of the following month? Etc... You may not even have to prompt your user, but may be able to set it automatically based on some other factor.

As for the second, in your letter (report) all you need to do is change the Format of the report control bound to that field to Long Date.

 

[rinova]

No rules for the date. I just need the date I enter to display in the letter. I'm creating a button to print the letter. Before the letter is printing it will prompt for various group numbers and at the end it will prompt for a date. I will use this date in the body of the letter, for all the letters.

I'm having a hard time passing that information into the letter. I don't know how to create a prompt from within the report or query and then enter it into the body of the letter.

Can I create a prompt from the query and then use it in the report?

Thank you.

 

[David R]

In your query add a calculated field:

ExpirationDate: [Enter expiration date]

When you run the query, or the report the query is based on, it should ask you for said date. I believe your report can use

="Format([ExpirationDate],"mmmm dd, yyyy")

to reformat it as you like, but I'm headed out the door and can't check myself right now. It MIGHT require you to wrap it in CDate() to get around the fact that it was entered as a parameter prompt.

Post back if you run into further trouble.

 

[rinova]

Yep that was the answer. I entered your code into the query and it worked perfectly.

I have one more issue and instead of taking an hour to figure it out I know you will have the answer quickly.

I have a gender field F=Female and M=Male I want to query that field to determine if the title for each letter will be Mr. or Mrs. M=Mr. and F=Mrs.

Should I create a new post for this problem?

Thank you so much!

 

[David R]

Ordinarily yes you'd make a new post, but this one is super easy, and you'll learn something for the future.

In the 'title' control, make the recordsource something like

=IIF([GenderField] = "F","Ms.","Mr.")

That is of course assuming you NEVER get blank gender entries, have no need to differentiate between married and unmarried folks, gender identity compliance,etc.

If you need more robustness, look into the Select Case function, but you'll need a bit of code or several nested IIF()s for that. In that case, definitely make a new thread.

 

[rinova]

The code worked, never had a doubt. Thank you.