How to pass results from mid function to datediff?

[Sgt_Utz]

I have been trying to figure this out for 2 days now. I keep getting the error "data type mismatch".



What I'm trying to do is use the date at the end of a file name to see what documents are over 1 year old. I'm trying to pass the results from the mid function to date diff. I've tried many different things but here's my current code with no success. I've tried adding # to the beginning and end but still no luck.

Btw the date in the document is using the format "2017-09-09" and have verified the mid function below pulls just this.

Datediff('d',Date(),mid(tblinfo.attachments.filena me,instrrev(tblinfo.attachments.filename," ")+1,instrrev(tblinfo.attachments.filename,"." )-instrrev(tblinfo.attachments.filename," ")-1))

[RuralGuy]

Simplify your code by making it multiple steps. Move the value you need into a variable first and then use the variable in your DateDiff() function. You can then MsgBox or Debug.Print the values and see what they are. Single step would work too.

[rpeare]

try:

Datediff('d',Date(),cdate(mid(tblinfo.attachments. filename,instrrev(tblinfo.attachments.filename," ")+1,instrrev(tblinfo.attachments.filename,"." )-instrrev(tblinfo.attachments.filename," ")-1)))

[rpeare]

You could potentially also do this:

assuming this statement gets you the correct date string

mid(tblinfo.attachments. filename,instrrev(tblinfo.attachments.filename," ")+1,instrrev(tblinfo.attachments.filename,"." )-instrrev(tblinfo.attachments.filename," ")-1)

substitute it into a date converter

dateserial(yearpart, monthpart, daypart)

where yearpart would be

left(mid(tblinfo.attachments. filename,instrrev(tblinfo.attachments.filename," ")+1,instrrev(tblinfo.attachments.filename,"." )-instrrev(tblinfo.attachments.filename," ")-1), 4)

monthpart would be

mid(mid(tblinfo.attachments. filename,instrrev(tblinfo.attachments.filename," ")+1,instrrev(tblinfo.attachments.filename,"." )-instrrev(tblinfo.attachments.filename," ")-1),6,2)

daypart would be

right(mid(tblinfo.attachments. filename,instrrev(tblinfo.attachments.filename," ")+1,instrrev(tblinfo.attachments.filename,"." )-instrrev(tblinfo.attachments.filename," ")-1))

then substitute the dateserial statmeent into your calculation

datediff("d", date(), <EXPRESSION HERE>)

[Sgt_Utz]

Simplify your code by making it multiple steps. Move the value you need into a variable first and then use the variable in your DateDiff() function. You can then MsgBox or Debug.Print the values and see what they are. Single step would work too.

How do I do this? is this putting all that code "as" something?

[Sgt_Utz]

Thanks for the help. The issue was that I was putting it under criteria instead of as an expression. Sorry I'm a noob.

Now I'm having the problem that when I do >365 (datediff is days) and try to run the query I get data type mismatch. Without the criteria it gives me a number like I want.

Anyone know what I'm doing wrong?

[RuralGuy]

Are you using this in a query? If so then create a Public Function and invoke it in your query. Your function will look something like:

Public Function Foo(InVal As String) As Integer
'-- returns the number of days between incoming date string
'-- in the format Year-Month-Day and today.

Dim MyArray() As String
Dim MyDate

MyArray = Split(InVal, "-")

MyDate = DateSerial(MyArray(0), MyArray(1), MyArray(2))

Foo = DateDiff("d", Date, MyDate)

End Function

[Sgt_Utz]

Thank you I'll try this. It helps to understand what is happening to understand why this doesn't work. Why can't I just use the expression as I did above? It returns a number so why can't I simply say ">365"?

[RuralGuy]

Does it return a numerical value or a text string that contain the numbers?

[Sgt_Utz]

not sure. The number column is a result of using datediff which works, so I would think it would return a number and not text. I'm guessing since it is not working however that it's returning text.

Is there a way to turn the result from the datediff in the expression into a number? Will wrapping the datediff in "Val" fix this?

[June7]

Result from DateDiff is a number. Rural Guy's function returns a number. But it is returning a negative because MyDate is older than current Date. If that is same in your case, use:

Foo = DateDiff("d", MyDate, Date)

And criteria >365 works for me.

[Sgt_Utz]

Ok I got some progress... I think.

There's one line missing from the function above, which is the mid function to split the date from the rest of the filename, but other than that it's the same:

Public Function Foo(InVal As String) As IntegerDim MyArray() As String
Dim MyDate
Dim FileDate
FileDate = Mid(InVal, InStrRev(InVal, " ") + 1, InStrRev(InVal, ".") - InStrRev(InVal, " ") - 1)
MyArray = Split(FileDate, "-")
MyDate = DateSerial(MyArray(0), MyArray(1), MyArray(2))
Foo = DateDiff("d", MyDate, Date)
End Function

When going back to design view for the query, I have "Expr1: Foo([Attachments.FileName])". I'm getting the exact same output. I get numbers, but when I put ">2" under Criteria I get a data type mismatch. What am I missing?

[June7]

Function returns a number value except when the field is empty then it returns #Error because the function expects a string input and string variables cannot hold Null. That causes datatype mismatch error for the criteria. So, handle Null:

Public Function Foo(InVal As Variant) As Integer
...
If Not IsNull(InVal) Then
...
End If
End Function

[Sgt_Utz]

Function returns a number value except when the field is empty then it returns #Error because the function expects a string input and string variables cannot hold Null. That causes datatype mismatch error for the criteria. So, handle Null:

Public Function Foo(InVal As Variant) As Integer
...
If Not IsNull(InVal) Then
...
End If
End Function

Well sir you found the issue, however your code isn't working. I created dummy documents to ensure their were no null records and the function worked, however as soon as I deleted the only record on a user (creating a null record) I got the data type mismatch, and this is using your "isnull" code. Why wouldn't that work on it?

[June7]

Then something else is going on because it works for me. I tested with text type field as well as attachment type.

If you want to provide db for analysis, follow instructions at bottom of my post.