asked on

"Access 2010", Referance a Text Field within another Text Field in a report.

Hello,
I am trying to create an Access 2010 DB that will create a report for requesting travel. The DB is all set up with Tables, Queries, forms and a Single Report.
In the report, I have a field that gives the explanation on when and where the travel will be.
Such as:
"I will be traveling on [date] and going to [Location] and returning on [Date]."
What I would like to do is to have a template text and have the dates and location embedded into the text. The idea is to have a database that will record the requests and have a standardized document to be printed. The static text is a "Label" and not a text field.

Any suggestions would be great.

Thank you for your time,
KixelsyD

PatHartman

Use the Replace() function to replace the token with the actual date.

replace(Me.yourcontrol.Caption, "[Date]", Me.datefield)

If that gives you an error (you may not be able to replace the caption on the fly), then change it to a text box and keep the string in your code as a variable or keep it in a table and get it when you need it. Using a table gives you more flexibility and will allow a user to change it rather than the programmer having to change is.

Dale Fye

Another options would be to create a function that returns your not-so static text, something like:

Public Function fnTravelRequest(dtDepart as variant, _
         strLocation as variant, dtReturn as variant) as string

fnTravelRequest = "I will be traveling on " _
     & iif(IsNull(dtDepart), "TBD", Format(dtDepart, "dd mmm, yy")) _
     & " and going to " & NZ([Location], "TBD") _
     & " and returning on " _
     & iif(IsNull(dtReturn), "TBD", Format(dtReturn, "dd mmm, yy"))

End Function

Open in new window

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kixelsyD

ASKER

Thanks Jeff,

After looking at the DB, I went with just creating a simple query.

Thanks,

Jeffrey Coachman

ok, great, ...glad I could help
;-)

&quot;Access 2010&quot;, Referance a Text Field within another Text Field in a report.

"Access 2010", Referance a Text Field within another Text Field in a report.