Date Mid (1 Viewer)

Gismo

Registered User.
Local time
Today, 11:16
Joined
Jun 12, 2017
Messages
1,298
Hi All,
I have a tracking number and date which I need to join in one field
Tracking number is formatted to "00000" and Date I only need the last 2 digits of the year
Sample: 00001/19
Tracking number is 000001 and the year is 19 from 2019/01/01
Currently my result is all digits from year field.
00001/19/01/2

Please could you assist, where am I going wrong?

Capture.PNG

Code:
SELECT [SB Tbl History].SBNo, Format([TrackingNo],"00000") & "/" & Mid([YearPost],3.2) AS [Tracking No]
FROM [SB/AD Master] RIGHT JOIN [SB Tbl History] ON ([SB/AD Master].[SB/AD No] = [SB Tbl History].SBNo) AND ([SB/AD Master].[Aircraft Type] = [SB Tbl History].[Aircraft Type]) AND ([SB/AD Master].Category = [SB Tbl History].Category) AND ([SB/AD Master].Index = [SB Tbl History].Index);
 

June7

AWF VIP
Local time
Today, 01:16
Joined
Mar 9, 2014
Messages
5,423
Is [YearPost] a date/time type?

Format([YearPost],"YY")

If that is a text field: Mid([YearPost],3,2)

Note the comma instead of dot.
 

Users who are viewing this thread

Top Bottom