Data Type Mismatch Error

[akn112]

Hey all, I have an interesting error that's very unusual. attatched is a test mdb which recreates the error im getting in my db. When u click on the "test" button located on Form1, u'll get an error "data type mismatch etc..". But, if u delete the entry where tbluseraccess.useraccessid = 21 u'll notice the error goes away. Is there anyway to remedy this?

[icthus123]

maybe i'm missing your point, but just a very quick glance, isn't that just because you had null values in [IT Email Sent] and [IT Call Closed]?

[akn112]

the command button is not pulling from the table though. It's pulling from the query and the query actually filters all the null values. If u look in the query, u'll see that the nulls should have been handled properly.

[icthus123]

the command button is not pulling from the table though. It's pulling from the query and the query actually filters all the null values. If u look in the query, u'll see that the nulls should have been handled properly.

I'd probably use something like this:


Private Sub Command0_Click()
Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "SELECT Sum(IIf(CompareDate([ua].[IT Call Closed],[ua].[IT Email Sent])>=0," _
& "CompareDate([ua].[IT Call Closed],[ua].[IT Email Sent]),0)) AS SumIT "
strSQL = strSQL & "FROM tblUserAccess AS ua "
strSQL = strSQL & "WHERE (((ua.[IT Email Sent]) Is Not Null) AND ((ua.[IT Call Closed]) Is Not Null));"

Set rs = CurrentDb.OpenRecordset(strSQL)

MsgBox rs![SumIT]

End Sub

[alimcpill]

Access must be doing something differently under the hood when it is executing the query through the front-end and when it is called implicitly from DSum.

Obviously the Nulls are confusing it, one way around it is to change your CompareDate function, so the inputs are declared as Variant rather than Date.

i.e. change

Function CompareDate(ToDate As Date, FromDate As Date) As Integer

To

Function CompareDate(ToDate As Variant, FromDate As Variant) As Integer

and it should work (does on mine at least, gets 248 as the result). I know no-one likes declaring things as variants but sometimes it's impossible to know what's happening behind the scenes, and variants seem to be able to handle the null values better.

Maybe someone can shed some more light on the issue? I would be interested to know.

[akn112]

I'd probably use something like this:


Private Sub Command0_Click()
Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "SELECT Sum(IIf(CompareDate([ua].[IT Call Closed],[ua].[IT Email Sent])>=0," _
& "CompareDate([ua].[IT Call Closed],[ua].[IT Email Sent]),0)) AS SumIT "
strSQL = strSQL & "FROM tblUserAccess AS ua "
strSQL = strSQL & "WHERE (((ua.[IT Email Sent]) Is Not Null) AND ((ua.[IT Call Closed]) Is Not Null));"

Set rs = CurrentDb.OpenRecordset(strSQL)

MsgBox rs![SumIT]

End Sub


this way works! interesting. this completely eliminates the need for the middle query. However, my database is doing this type of calculation on a much larger basis so it will have to recreate the recordset many times, would this be practical on a production copy?

[tebule]

Just out of curiosity why are you passing three veriables and then only use two in the procedure? Also, it looks like the code is stuck in a loop. Where is it pulling from the query? I can't seem to find it.

[icthus123]

this way works! interesting. this completely eliminates the need for the middle query. However, my database is doing this type of calculation on a much larger basis so it will have to recreate the recordset many times, would this be practical on a production copy?

I wouldn't have thought it would be a problem. It depends on exactly what you're going to do with it though.

[akn112]

Since im working on a reporting feature, there are many calculations that go with this query. That's why i felt the need for a static record i could work with. There are averages (dsum/dcount), dmax, dmin filtered by date parameters and null parameters which are all done 3 times using this query. The recordsource (table) is about 400 entries large but we have only just started using this tool for the last month so it will probably grow quite large.

[tebule]

Is your attachment your whole DB?

[akn112]

tebule: Here is a more complete version of the form im trying to create. The problem is still with the null entry. If u delete the last entry in tblUserAccess then the error will go away. This is also a toned down version however, because it'd be lots of work to sanitize the database to include it all.