Forum Discussion
date convertion (s)
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
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?
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- 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;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