Convert Short Date filed to a String

[JonathanT]

I have a Short Date field on a form connected to a table. I want to convert that field to a string and store that in a different field in the table. In other words if 1/1/2023 is my Short Date field I want to convert that to January and store that value in another field in the table. I want to be able to query the table based on a month. Is this possible?

[Gicu]

You don't need to store it in another field. In your query add a calculated field MyMonth:Month([YourDateField]) and in its criteria row put 1 (for January). Or you could use the MyMonth:MonthName(Month([YourDateField])) if you want to return the actual month names instead of their numbers. Or look into the Format() function if you need month and year or any other formats.
Cheers,

[CJ_London]

@jonathanT - are you aware that dates are stored as a double data type? The format you see for short date is just that. A format based on your local settings. The underlying value stores date and time,the number before the decimal point is the number of days since 31/12/1899 and the value after is the number of seconds to now divided by the number of seconds in a day (86400)

So if you want to see a month name format as long date - mmmm dd yyyy (assuming us format and see January) or you might use dd mmm yyyy if you wanted UK format and see Jan.

To search for a month use Gifu’s suggestion

[JonathanT]

Thanks for the replies. I was able to calculate the Month and Year in my query using the example provided by Gicu. I have been unable to also calculate the Quarter using the same configuration . Is that possible?

[CJ_London]

use the datepart function
https://learn.microsoft.com/en-us/of...epart-function


or calculate from month - ((month(mydate)-1)/3)+1 - note the direction of \
?((1-1)\3)+1
1
?((2-1)\3)+1
1
?((3-1)\3)+1
1
?((4-1)\3)+1
2
?((12-1)\3)+1
4

[JonathanT]

Thanks for the replies. I was able to calculate the Month and Year in my query using the example provided by Gicu. I have been unable to also calculate the Quarter using the same configuration . Is that possible?

[JonathanT]

I was able to solve my quarter issue by using the following calculated field. ReportQuarter: Format([currdate],"q")

[CJ_London]

just be aware that the value created using the format function is text - since the range can only be 1-4 it wont impact sorting, but you will need to use quotation delimiters if using as a criteria or filter