Set Date Format in 'Replace' Formula

[Nadine67]

Hello and thank you in advance for any attention this post may recieve.

I am using MS Access 2010 on a Windows XP platform.

So I have a report containing a text box with the Control Source as a formula to display 'Date' field and 'Title' field as a title (Date : Title). My formula is returning the information I have asked for, but the date is not the format I want.

My formula is =Replace(Replace("|1 : |2 ","|1",Nz([ProblemDate],"")),"|2",Nz([Title],"(Untitled)"))

It is returning the date in a 'General Date' format (DD/MM/YY hh:mm:ss PM) where I want it to display as 'Medium Date' (dd-mmm-yy)format.

I do not know how to write the format into my formula.

Nadine

[June7]

Date field is a date/time type?

Format([Date], "dd-mmm-yy") & ":" & Nz([Title], "(Untitled)")

Date is a reserved word. Should avoid reserved words as names.

[Nadine67]

Hello June

The field is 'ProblemDate', not 'Date'. Hmmmmm I am unsure if 'ProblemDate' will conflict with reserved word Date.

I have used your suggestion and now my formula is =Replace(Replace("|1 : |2 ","|1",Format([ProblemDate],"dd-mmm-yy")),"|2",Nz([Title],"(Untitled)")) and it the date (ProblemDate) is exactly as I want it to display (15-Jul-15 : This is my Title).

Thank you June for once again solving my issue.

Nadine

[CJ_London]

Also be aware that all dates are actually numbers - what you see as 06/01/2015 or Jan 6th 2015 is just the way it is formatted - so replace won't work unless you use the format function first which turns it into a string

dates are a decimal number, the value to the left of the decimal point is the number of days from way back when and to the right of the decimal point represents the time expressed as the current time in seconds as a percentage of 24hr x 60min x 60 secs

so today is 24th July 2015 and the time is 1:24am

this is stored as 42209.058587963

Same with currency - the $/£ etc symbol is not stored

[Nadine67]

Oh thank you for that June. I understand how dates are stored more clearly.

The date was displaying as dd/mm/yy hh:mm:ss before I inserted 'Format' in my formula. Now that 'format' is in there it is displaying how I want/told it to.

Champion!

[June7]

Did not notice actual field name was ProblemDate after reading the statement before the expression.

I did some testing and was surprised that Replace() did work on a date/time value. Examples:

Replace(Date(), "/", "-")

returns 7-22-2015

Replace(Date(), "/", ":")

returns 7:23:2015

Weird, I did not expect that.

Really don't need both Replace and Format. Why not simply:

Format([ProblemDate], "dd-mmm-yy") & " : " & Nz([Title], "(Untitled)")

[Nadine67]

Hey there June

Oh ok I will do that. no need to add more clutter is it makes no difference hey.

I hope you have a great day!

Nadine