date convertion (s)

[kregggower]

need help converting a date formatted 20230117093739 YYYYMMDDHHMMSS

to 01/17/2023 (in one cell) 9:37:39 AM (in the next cell). i normally use this mid string
Date: Mid([INB_DATE_TIME],5,2) & "/" & Mid([INB_DATE_TIME],7,2) & "/" & Mid([INB_DATE_TIME],1,4)

Time: Mid([INB_DATE_TIME],9,2)&":"&Mid([INB_DATE_TIME],11,2)&":"&Mid([INB_DATE_TIME],13,2)

and the use DATE() to only show the current date. but the query comes up blank (no data) i know i have current days date.

[#2]

Hi,

With the string functions you create a string. If you want to compare it with a date, you have to convert it to a date:

NewDate: cdate(Mid([INB_DATE_TIME],5,2) & "/" & Mid([INB_DATE_TIME],7,2) & "/" & Mid([INB_DATE_TIME],1,4))

Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon

[#3]

@kregggower 

Thank you Karl,

I added the mid file this is the return..

[#4]

@kregggower 

you can also create a custom function and call it from your query:

Public Function fnToDateTime(ByVal var As Variant, ByVal whichPart As String) As Variant
' arnelgp
'
' Note:
'
' whichPart can be "D" (for date) or "T" for time
'
Static pvar As Variant
Static retDate As Date, retTime As Date
Dim d As String, t As String
If IsNull(var) Then
    Exit Function
End If
If var <> pvar Then
    d = Left$(var, 8)
    t = Mid$(var, 9)
    retDate = DateSerial(Left$(d, 4), Mid$(d, 5, 2), Right$(d, 2))
    retTime = #12:00:00 AM#
    If Len(t) <> 0 Then
        retTime = CDate(Left$(t, 2) & ":" & Mid$(t, 3, 2) & ":" & Right$(t, 2))
    End If
    pvar = var
End If
Select Case whichPart
    Case "D"
        fnToDateTime = retDate
    Case "T"
        fnToDateTime = retTime
End Select
End Function

on your Query:

select [strDateField], fnToDateTime([strDateField], "D") As Dte, fnToDateTime([strDateField], "T") As Tim 

from yourTable;

[#5]

Hi,

Usually such a "type mismatch" happens when there are empty values in some records. If that's the case in your data then you can try to exclude empty values:

NewDate: IIf(Not IsNull([INB_DATE_TIME]),cdate(Mid([INB_DATE_TIME],5,2) & "/" & Mid([INB_DATE_TIME],7,2) & "/" & Mid([INB_DATE_TIME],1,4)))

Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon

[#6]

Karl,
Thank you for the reply The "IIf(Not IsNull([INB_DATE_TIME])" really helped clean up the data in the table Thank You! and is shows the date in the query if i run it with out filters "DATE()" but if i use the "DATE()" or "DATE()+1" in the Criteria field no dates are returned the out put query is blank, like there is no matching dates?

[#7]

arnel_gp,
Thank you for you reply. but i've just starting to look into VB language and it may as well be Hieroglyphics. it is over my head (for now). but thank you very much for your input.

[#8]

Hi,

Some more ideas:

1. Test if this criteria returns any data: <>Date()

2. Write this into the field line of a new column in the query: IsDate([NewDate])

Does it say True/-1 or False/0 in data view?

3. Go to the SQL view of your query and copy the SQL statement into your next message here. Maybe we can see the problem.

Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon

[#9]

Karl,
thank you for your assistance!

<>Date() Returns all date in query.
IsDate([NewDate]) added to new cell and returned -1.
Below is the SQL data...
SELECT STOR_DEL.PKG_NUM, IIf(Not IsNull([INB_DATE_TIME]),CDate(Mid([INB_DATE_TIME],5,2) & "/" & Mid([INB_DATE_TIME],7,2) & "/" & Mid([INB_DATE_TIME],1,4))) AS NewDate, IIf(Not IsNull([INB_DATE_TIME]),Mid([INB_DATE_TIME],5,2) & "/" & Mid([INB_DATE_TIME],7,2) & "/" & Mid([INB_DATE_TIME],1,4)) AS [Date], IIf(Not IsNull([INB_DATE_TIME]),Mid([INB_DATE_TIME],9,2) & ":" & Mid([INB_DATE_TIME],11,2) & ":" & Mid([INB_DATE_TIME],13,2)) AS NewTime, STOR_DEL.CUSTOM1, STOR_DEL.CUSTOM2, STOR_DEL.CUSTOM3, STOR_DEL.CUSTOM4, STOR_DEL.CUSTOM5, STOR_DEL.CUSTOM6, STOR_DEL.CUSTOM7, STOR_DEL.CUSTOM8, STOR_DEL.CUSTOM9, STOR_DEL.CUSTOM10, STOR_DEL.CUSTOM11, STOR_DEL.CUSTOM12, STOR_DEL.CUSTOM13, STOR_DEL.CUSTOM14, STOR_DEL.CUSTOM15, STOR_DEL.CUSTOM16, STOR_DEL.CUSTOM17, STOR_DEL.CUSTOM18, STOR_DEL.CUSTOM19, STOR_DEL.CUSTOM20, STOR_DEL.APP1, STOR_DEL.APP2, STOR_DEL.APP3, STOR_DEL.FLAG, STOR_DEL.INB_DATA_SRC, STOR_DEL.INB_DATE_TIME, STOR_DEL.INB_USER_NAME, STOR_DEL.DEL_DATA_SRC, STOR_DEL.DEL_DATE_TIME, STOR_DEL.DEL_USER_NAME, STOR_DEL.DEL_RECIP_NAME, STOR_DEL.DEL_RECIP_LEN, STOR_DEL.DEL_RECIP_SIG, STOR_DEL.PARENT_NUM
FROM STOR_DEL;

[#10]

Hi,

The results of your tests seem to be ok.

In the SQL text I don't see a problem but also not a WHERE clause, i.e. you didn't show the most important thing: your criteria that doesn't work.

Do you really see the current date in the NewDate column (it's over midnight here in Central Europe 😉 before you use the criteria for this column?

Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon

[#11]

@Karl_Donaubauer 

This is a clipping of the query with out any restrictions...

if i put in a date restrictions DATE()

this is what the query returns with DATE() in the query.. 

[#12]

Hi,

The column Date_Test in your picture is a string again instead of a date. You didn't convert it into a date with CDate(). We can easily see this because the values are left aligned whereas date values would be aligned to the right. A comparison of Date() and strings doesn't work, as discussed earlier.

Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon

[#13]

Instead of bunch of Mid, you can simply use this.
to Return date:

IIF(IsNull([INB_DATE_TIME]),[INB_DATE_TIME],CDate(Format(Left([INB_DATE_TIME],8),"@@@@-@@-@@")))

To return the Time:

IIF(IsNull([INB_DATE_TIME]),[INB_DATE_TIME],CDate(Format(Right([INB_DATE_TIME],6),"@@:@@:@@")))