Solved DateAdd Format (Medium Date) (1 Viewer)

[Lochwood]

Here is my code but i would like it to report Medium Date Medium Date.. the actual field and table format is medium date.

Code: between the" & " " &DateAdd("m", -1, Forms![TRHA]![Medical_Due_Subform].Form![Medical_Due]) & " " & "and" & " " & Forms![TRHA]![Medical_Due_Subform].Form![Medical_Due] _

Output: Between the 05/04/2021 and 05-May-21

I am trying to get output to Between the 05-Apr-21 and 05-May-21

Any guidance would be great.. thanks.

 

[bob fitz]

Try:
Code: between the " & DateAdd("m", -1, Format(Forms![TRHA]![Medical_Due_Subform].Form![Medical_Due],"dd/mmm/yy")) & " and " & Forms![TRHA]![Medical_Due_Subform].Form![Medical_Due] _

 

[Minty]

A couple of things - don't format your dates in the table, it's only a representation of the data stored, and could hide a time value that you might not know was present.
Always apply formats either in the report or the forms. It's more efficient and allows you to see the real data in your underlying queries when developing.

Try

Code: between the " & Format(DateAdd("m", -1, Forms![TRHA]![Medical_Due_Subform].Form![Medical_Due]),"dd-mmm-yy") & " and " & Forms![TRHA]![Medical_Due_Subform].Form![Medical_Due] _

Edit: Dang- slow fingers @bob fitz beat me to it

 

[bob fitz]

A couple of things - don't format your dates in the table, it's only a representation of the data stored, and could hide a time value that you might not know was present.
Always apply formats either in the report or the forms. It's more efficient and allows you to see the real data in your underlying queries when developing.

Try

Code: between the " & Format(DateAdd("m", -1, Forms![TRHA]![Medical_Due_Subform].Form![Medical_Due]),"dd-mmm-yy" & " and " & Forms![TRHA]![Medical_Due_Subform].Form![Medical_Due] _

Edit: Dang- slow fingers @bob fitz beat me to it

Actually, our suggestions differ slightly, but I think that both would work

 

[Lochwood]

A couple of things - don't format your dates in the table, it's only a representation of the data stored, and could hide a time value that you might not know was present.
Always apply formats either in the report or the forms. It's more efficient and allows you to see the real data in your underlying queries when developing.

Try

Code: between the " & Format(DateAdd("m", -1, Forms![TRHA]![Medical_Due_Subform].Form![Medical_Due]),"dd-mmm-yy" & " and " & Forms![TRHA]![Medical_Due_Subform].Form![Medical_Due] _

Edit: Dang- slow fingers @bob fitz beat me to it

Minty your solution worked.

 

[arnelgp]

both missed the last Format:

"Between the " & Format(DateAdd("m", -1, Forms![TRHA]![Medical_Due_Subform].Form![Medical_Due]), "dd-mmm-yy") & " and " & Format(Forms![TRHA]![Medical_Due_Subform].Form![Medical_Due], "dd-mmm-yy")

 

[bob fitz]

both missed the last Format:

Actually, I did not "miss" it. I chose not to suggest changing part of the expression that was already returning the required value.

 

[arnelgp]

not missed but only the "missed" part has correct output.

 

[Minty]

Minty your solution worked.

As @arnelgp pointed out, the second part is more by luck than judgement.
I would remove the formatting from the table and reapply during the output phase.