Query to manage date output

[Micron]

If you're not going to use these dates in calculations and they're just for inserting into some document/letter and you actually need the table, then why not just make the table fields text? If you are calculating (e.g. deriving next date from another date by adding x days) the query can have date data type fields from the table as well as formatted fields for use in the letter. However, I don't mean to say that you would be storing the calculations, because generally speaking, you should not.



OK, I see you posted while I was composing. You letter producing code could simply use format function within the string concatenation rather than altering table values:

"The last time we were in contact was in " & Format([CalDate],"mmmm") & " of " & Format([CalDate],"yyyy") & "."

[JoeM]

Looks like we are pretty much right back where we started.
You should be able to use my original suggestion to return it to say what you want, and it can all be done via Query calculations (no need to store it at the Table level).

This is precisely the kind of thing that you use Queries for. In addition to selecting records, you can do calculations (including ones with text), that you want to show on your final output (whether that be a Report, Form, export, or merged with some document).

[Chuck]

OK, I missed the fact that you are trying to use this value to update another field. As Micron said, the FORMAT function returns a text value, not a date value. So if "LastCallMonth" is a date field, yes, you will get a type conversion error, as your are trying to apply a text value to a date field.

However, this all seems totally unnecessary. It sounds like that you just want to set "LastCallMonth" to be the same value as "CallDate". What is the point of copying over the value from one field to a table to another field in the same record? Also note that every valid date must have a month, day, and year. It sounds like all you really want is to display that date in a different format. You can easily change the format of any date field you want to display using the Format property of that field (at the Query level). All you have to do to do that is right-click on the field in Query Design View, select Properties, and poulate the Format property with something like mmmm yyyy.

So I don't see any real need to copy this field over to a new field at all. Even if you do, you just set the value to equal the value of the other field, and appy the desired Format at the Table/Query level. No special calculation is needed.

Thank you very much for providing this solution, which of course is the perfect solution. I did not fully appreciate it when you initially posted it. I must have read your post too quickly.

[Chuck]

To be clear, the solution that I used was the one contained here: " All you have to do to do that is right-click on the field in Query Design View, select Properties, and populate the Format property with something like mmmm yyyy."