Please Help: Data Type Mismatch in criteria expression

[AppStaff]

UActive = Val(Nz(DSum("ST", "qrySELUpgradeBonus", "BuildID=forms!frmBuildsNew!BuildID and StartTime > '" & Eupgrade & "'&quot)) - Val(Nz((DSum("ET", "qrySELUpgradeBonus", "BuildID=forms!frmBuildsNew!BuildID and StartTime > '" & Eupgrade & "' and ActivityID='06'&quot))) * 60

Can anyone help me resolve this error. I've spent a good couple hours trying to figure out why I get a data type mismatch for this and no clue. Seems to make perfect sense to me, what am I missing?

dim Uactive as double
dim Eupgrade as double

Eupgrade= 8.5
ST is stored in fixed decimal format as is
ET and
StartTime

 

[Darrylles]

Hi,

What error?

Split the code and assign results to variables to try to isolate the error.



Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[VBslammer]

UActive = Val(Nz(DSum("ST", "qrySELUpgradeBonus", "BuildID=" & Forms!frmBuildsNew!BuildID & " AND StartTime > '" & Eupgrade & "'"))) - Val(Nz((DSum("ET", "qrySELUpgradeBonus", "BuildID=" & Forms!frmBuildsNew!BuildID & " AND StartTime > '" & Eupgrade & "' AND ActivityID='06'")))) * 60

VBSlammer

Unemployed in Houston, Texas

 

[AppStaff]

Error is in the topic. Data type mismatch. Thanks for response but the problem seems to be in the greater than line and not the forms reference.

 

[VBslammer]

Why are you using single quotes around number values?

UActive = Val(Nz(DSum("ST", "qrySELUpgradeBonus", "BuildID=" & Forms!frmBuildsNew!BuildID & " AND StartTime > " & Eupgrade ))) - Val(Nz((DSum("ET", "qrySELUpgradeBonus", "BuildID=" & Forms!frmBuildsNew!BuildID & " AND StartTime > " & Eupgrade & " AND ActivityID= 06")))) * 60

VBSlammer

Unemployed in Houston, Texas

 

[AppStaff]

I believe the single quotes are required when using quotations within a function that has quotations around the parameters. Its the only way Access can differentiate between the inherint quotations in the function and the quoatations within the criteria. I have tried the syntax as many different ways as I can think with the same error as a result.

When I tried to recreate this expression using the graphical query tool, not surprisingly I get the same error.

 

[Escolar]

It seems to me that this part:
"BuildID=" & Forms!frmBuildsNew!BuildID & " AND

should be
"BuildID= '" & Forms!frmBuildsNew!BuildID & "' AND
or
"BuildID= val('" & Forms!frmBuildsNew!BuildID & "') AND

Otherwise, I agree that the query should be broken down by moving some of the parameters into defined variable first.

Escolar