[SOLVED] I need to split a text column in Access that stores dates in MM/YYYY and MM/DD/YYYY format to remove the day format

I need to split a text column in Access that stores dates in MM/YYYY and MM/DD/YYYY format to remove the day format

I have a program that writes dates as text in a column called Expression. Is there a way to write a query that will remove the day of the month? I need it to be something that I can share with multiple people to use in multiple databases. Examples of the format of the values stored are 6/2021, 06/2021, and 06/01/2021.

Solution

You can use IIf in the query to compare the length of the data, and pad/split as required. Something like:

SELECT 
    Expression, 
    IIf(Len([Expression])=6,"0" & [Expression],IIf(Len([Expression])=7,[Expression],IIf(Len([expression])=10,Left(Expression,2) & "/" & right(expression,4)))) AS OutputData
FROM tblFormatDate;

You may need to add a few more IIfs, and also a final false return statement. If it starts to get messy with the data, then you may want to look at creating a custom VBA function that makes the process flow easier to understand.