Syntax for using "Left" to select part of a field as a Control Source for a report

[Budatlitho]

I have an Access report that the Record Source query includes a field called "dates" containing a Short Text-format field whose values are, for example, dates that are 8 characters long, i.e: "09/07/24".


I want to display the only the 5 characters, i.e: "09/07"
In the Control Source for the field I have =Left$([dates],5)
I know I don't have the correct syntax.


Bud

[Welshgasman]

Why are you pasting 3 pictures which are the same?
Dates should be held as Dates and they are really numeric. It is the formatting that you see.

However I have just tested

? left(date,5)
30/09

in the immediate window and that works, so I have learnt something new today.

You could just as easy have a format of "mm/dd" or "dd/mm" depending on where you live? on that control, which would probably be my choice.

[Micron]

I'm on the fence with the date thing in this case, given that to get something like "09/30" you'd have to use Format(), in which case it will turn it into a string anyway. If the posted info is the only use for these dates I'm not sure it matters. You wouldn't be able to use DatePart on valid dates either, unless you'd be OK with single characters for month and day values less than 10.

[Budatlitho]

Could not figure out how to delete the extra photos, sorry.
The field in the table is defined as Small Text, not date.

[June7]

What makes you think your syntax is not correct?

Why are dates saved as text?

[Welshgasman]

I'm on the fence with the date thing in this case, given that to get something like "09/30" you'd have to use Format(), in which case it will turn it into a string anyway. If the posted info is the only use for these dates I'm not sure it matters. You wouldn't be able to use DatePart on valid dates either, unless you'd be OK with single characters for month and day values less than 10.

@Micron, No I was thinking of the Format property of the control?
I believe that does not convert the value to a string?
I tried it on a date control of mine, but did not test to see if it became a string. How would one do that?

[Micron]

I'd use the immediate window.
?Typename(controlnamehere.Value). This is one of the few places you need .Value

[Welshgasman]

I'd use the immediate window.
?Typename(controlnamehere.Value). This is one of the few places you need .Value

? Typename(Forms!frmDailyEntry!sfcsfrmdaily.form.txt dailydate.Value)
Date