MS Access 2003 report concatenation

Question

I am currently working on a report for MS Access 2003 with three fields consisting of a [Lease Start] Date, [Lease End] Date, and [Financial Notes] String. The Management wants all three combined under Financial Notes on the Report. I have set up the Concatenation as:

=CVar([Lease Start]) & " - " & CVar([Lease Ends]) & " " & [Financial Notes]

Yet I am getting a # Error when I run the report.

HansUp HansUp · Accepted Answer · 2013-04-04T18:08:33

See what that expression returns in a query of your report's record source.

SELECT
    CVar([Lease Start]) & " - "
        & CVar([Lease Ends])
        & " " & [Financial Notes]
        AS report_expression
FROM YourTableOrQuery;

I don't know whether this is significant, but I'm puzzled why you're using CVar() there. When you give it a Date/Time value, it returns a Date/Time value. Access should oblige by casting that to a string when you concatenate, but it would do the same for the original Date/Time value without CVar. I don't understand why CVar is useful there.

Since you're building a string, my inclination would be to use Format().

SELECT
    Format([Lease Start], "m/d/yyyy") & " - "
        & Format([Lease Ends], "m/d/yyyy")
        & " " & [Financial Notes]
        AS report_expression
FROM YourTableOrQuery;

However as I admitted, I have no clue whether this is a significant issue.

MS Access 2003 report concatenation

1 Answers