Question How to remove Date Seporator?

[cos]

I have a lot of dates in one column (dd/mm/yy), and i somehow need to duplicte them into another column where they are shown without the date seporator (ddmmyy), so that i can use that figure in a future calculation where a veriety of fields are combined to show one unique product number.

Any ideas on how i can achive this? Because there is nothing in my other sources... :banghead:

 

[bob fitz]

Try this:
Create a query using the table that has your date field.
Create a new column in the query with this:
NoSepDate: Day([YourFieldName]) & Month([YourFieldName] & Year([YourFieldName])
Replace YourFieldName with the name of your date field.

 

[Trevor G]

Add an Expression in a query and you can then use left, mid and right formula like in Excel and it will give you a result in the way you want. Example below

Expr3: Left([Date_Week_Ending],2) & IIf(Len([Date_Week_Ending])=6,Mid([Date_Week_Ending],3,2),Mid([Date_Week_Ending],4,2)) & Right([Date_Week_Ending],2)

 

[cos]

its a good theory, exept: how do i store that in a table, and for dates such as 1st of January 2012, the number displayed is 112012, and i need the zero's to make up a number like 01012012...

Any ideas on how i could solve this?

 

[cos]

Add an Expression in a query and you can then use left, mid and right formula like in Excel and it will give you a result in the way you want. Example below

your idea didn't work on mine

 

[Trevor G]

If the year starts with 20 then you can just add that it in, are you showing the prpoer way it is added to the database as this is a different format from your first question.

Expr3: Left([Date_Week_Ending],2) & IIf(Len([Date_Week_Ending])=6,Mid([Date_Week_Ending],3,2),Mid([Date_Week_Ending],4,2)) & "20" & Right([Date_Week_Ending],2)

 

[DavidAtWork]

you need to store the value as string variable
Dim myDay, myMonth, myNewDate as String

If Month([myDate])<10
Then myMonth = "0" & Month([myDate])
Else myMonth = Month([myDate])
End if

If Day([myDate])<10
Then myDay = "0" & Day([myDate])
Else myDay = Day([myDate])
End if

myNewDate = myDay & myMonth & Year([myDate])

David

 

[bob fitz]

Trevor's formula gives the result you need. If you need the year as 4 numbers change the "2" near the end to 4:
Expr3: Left([Date_Week_Ending],2) & IIf(Len([Date_Week_Ending])=6,Mid([Date_Week_Ending],3,2),Mid([Date_Week_Ending],4,2)) & Right([Date_Week_Ending],4)

exept: how do i store that in a table

Maybe you could create a new field in the table and then make an update query using the formula to fill the new field.