Convert Number to Date/Time

[Jerseynjphillypa]

Currently I have a field called DateEnter as a number, but I am looking to convert it to a Date/Time.

The number is enter the following way: 201003 --> yyyymm but I want to show up as Date/Time as mm/yyyy.

I tried to play with the formatting,



Format([DateEnter],"mm/yyyy") in the query but that didn't work

[pbaldy]

Check out the DateSerial() function, along with the Mid() function to grab the two different components (you'll need to hard-code a day).

[RayMilhon]

If your talking about a permanent conversion that is saved in your table Paul's is the method to use. However if you're talking about doing the conversion in a query for display on a report or form you can do it this way: In a module add the following

Public function dateconversion(Byref lngvalue as Long) As string
    Dim tmpmonth as string
    Dim tmpyear as string
    If len(trim(str(lngvalue)))=6 Then
        tmpmonth = Right(trim(str(lngvalue)),2)
        If tmpmonth <1 or tmpmonth > 12
                dateconversion = "Invalid Month"
        Else 
              ' here you may want to do some validation similar to the month as
              '(Personal Assumption here)  years greater than 2200 probably aren't valid and depending on how far back your data goes years prior should
              'be validated as well
              tmpyear = left(trim(str(lngvalue)),4)
              dateconversion = tmpmonth & "/" & tmpyear
        End if
    Else
         dateconversion = ""
    End if
End function

[Jerseynjphillypa]

Thanks for the help

So I did was
Mid([DateEnter],5,2) & "/" & Mid([DateEnter],1,4)

But if the DateEnter field is blank, it still shows "/". How can I remove the / or do the commend only when there is a Date.

Would I use ISNA?

[pbaldy]

You understand that's not converting it to date/time, just displaying it differently? You can test the field with IIf() and IsNumeric() and only use your function when appropriate.

[Jerseynjphillypa]

I tried to do the following expression so I can get it into a Date/Time

DateSerial(Mid([DateEnter],5,2)) but i get the error "The expression you entered has a function containing the wrong number of arguments

[pbaldy]

Did you look in help at the DateSerial() function? It requires all 3 components of a date.

[Jerseynjphillypa]

yes...but what if I don't know the day and want to leave that part blank

[pbaldy]

Then you can't use a date/time data type, as there's no such thing as a date without a day. I might just put a 1 in for the day for every record.

[RayMilhon]

Which is why I made the suggestion I made there was no day in the original post. which means you can leave it as numeric or change it to text. Which is what my function does. The function can be used to convert the current numeric field into a text field I'm just not sure why you would want to do that when the data already exists you're simply changing the way it looks.

[pbaldy]

But the original request was "I am looking to convert it to a Date/Time", which presumably meant data type.