Query to manage date output

[Chuck]

Basic information:

1. I am running merged letters and drawing data for the merged fields from an Access query.
2. I have a field called "CalDate", which contains a complete date in this format (mmddyyyy).
3. In my letter I want to reference only the month and the year and in this way: " ....in June of 2018".
4. What I am attempting to do is pull out the month and the year from the date field as separate pieces of information and incorporate them in my text in a flexible way.



I am wondering whether it would be possible to do this with a query.

[JoeM]

What is the data type of "CalDate"?
Is it Text or Date?

[JoeM]

If the CalDate field is the Date data type, you can use this calculation:

Format([CalDate],"mmmm") & " of " & Format([CalDate],"yyyy")

If the CalDate field is the Text data type, you can use this calculation:

Format(DateSerial(2019,Left([CalDate1],2),1),"mmmm") & " of " & Right([CalDate1],4)

[Chuck]

The data format is Date.

One thought that I had was to create two new fields in the underlying table, one being Month and the other being Year. I would then try to devise a query that would update these fields with the relevant components of the existing Date field. If this could be done then I would have flexibility of use with the date components. The other requirement is to be able to represent the month in text and the year with 4 digits, ie June 1967.

[JoeM]

The data format is Date.

Then you should be able to use my first formula in my previous post.

One thought that I had was to create two new fields in the underlying table, one being Month and the other being Year.

You can, but there really isn't a need to.

The other requirement is to be able to represent the month in text and the year with 4 digits, ie June 1967.

Its even easier than your original condition, if you do not need to show the word "of". You can then just use the FORMAT function, i.e.

Format([CalDate],"mmmm yyyy")

The FORMAT function is going to be very valuable to you here, as it has a lot of uses. You will want to be sure to become familiar with it.

[Chuck]

I have been experimenting and tried to extract the month and populate a newly created field, LastCalMonth, using an update query. I used this: Format([CalDate],"mmmm"). The error message that I receive is that there is a type conversion failure. To confirm, all of the fields that I am using that hold date content are Date fields, not text.

[JoeM]

I have been experimenting and tried to extract the month and populate a newly created field, LastCalMonth, using an update query.

Why?
It is seldom necessary to store the value in a table field if it can easily be calculated at the query level. You can use a query for pretty much anything you can use a table for.
Actually storing at the table level violates the Rules of Normalization (fields within a table should not be dependent upon one another), and can undermine the dynamic nature and data integrity of your database.

I used this: Format([CalDate],"mmmm"). The error message that I receive is that there is a type conversion failure. To confirm, all of the fields that I am using that hold date content are Date fields, not text.

This seems to suggest that your field really is not a "Date" Data Type. Can you open your Table in "Design View", and post a screen print of your Field Names and Data Types?

[Chuck]

Why?
It is seldom necessary to store the value in a table field if it can easily be calculated at the query level. You can use a query for pretty much anything you can use a table for.
Actually storing at the table level violates the Rules of Normalization (fields within a table should not be dependent upon one another), and can undermine the dynamic nature and data integrity of your database.


This seems to suggest that your field really is not a "Date" Data Type. Can you open your Table in "Design View", and post a screen print of your Field Names and Data Types?

(Please note: I originally did not spell the field name correctly in my posting. CallDate with 2 els is correct.)

I will post screenshots later. For some reason I am not able to paste my screenshots to this message although I can paste the content onto a document.

[JoeM]

You can also update your database for analysis. Just be sure to first remove any sensitive/personal data first.
I cannot download files from my current location, but I can from home.

[Micron]

Be careful using the format function on anything because it converts the result to a string.
Depending on what you do with the result, you may have to wrap the result in the CDate function, assuming the string can be evaluated to a date and you need an actual date value.

[Chuck]

There is absolutely no question that the fields containing dates in my DB are date fields. As for posting a screenshot, there must be something I am not aware of because I am not able to paste the jpg. Is it supposed to be possible to paste a jpg within the reply window?

[Micron]

No, you use the image icon in the post toolbar and upload. This might help

https://www.accessforums.net/showthread.php?t=70301

[Chuck]

Here is the screenshot as well as the query SQL.

UPDATE owners SET owners.LastCallMonth = Format([CallDate],"mmmm")
WHERE (((owners.OwnerID)=129));

[JoeM]

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.

[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.

I run merged letters drawing on data from MS Access.
In a letter I want to reference the date of the last call.
The date of the last call is held in the DB (LastCallDate).
This is how it would look if I simply used the mergefield LastCalDate as is: "The last time we were in contact was 1/1/2019". This is not what I want.
What I want to say is "The last time we were in contact was in January of 2019".
For this reason I am trying to find a way to extract the month and the year from my date field (LastCalDate).