Forum Discussion

Copper Contributor

Jan 17, 2023

date convertion (s)

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) & "/" & ...

date conversion (s)

Copper Contributor

Jan 17, 2023

Thank you Karl,

I added the mid file this is the return..

Karl_Donaubauer
MVP
Jan 18, 2023
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
- kregggower
  Copper Contributor
  Jan 18, 2023
  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?
  - Karl_Donaubauer
    MVP
    Jan 18, 2023
    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

Resources