Thanks Tom, His db is not working on my machine with the same db setup as
mine just different data
Funny basDues works fine on his db when compiling one client only when you
ask it to compile all Clients , that's when the error comes up
I will try your testing.......Thanks Bob
My Windows: XP Home edition Ver 5.1 Service Pack 3
This is basDues Module:
Function MonthsDue(date1 As Date) As Integer
Dim rptDate As Date
rptDate = Date 'Reporting date
MonthsDue = DateDiff("m", date1, rptDate)
End Function
Function Dues(tb3Months0 As Double, tb2Months0 As Currency, tb1Month0 As
Currency, tbCurrent0 As Currency, months As Integer) As Currency
Dim tb3Months As Currency, tb2Months As Currency, tb1Month As Currency,
tbCurrent As Currency
Dim diff As Double
diff = 0
tb3Months = tb3Months0
tb2Months = tb2Months0
tb1Month = tb1Month0
tbCurrent = tbCurrent0
If tb3Months > 0 Then
If tb2Months < 0 Then
diff = tb3Months + tb2Months
tb3Months = IIf(diff < 0, 0, diff)
tb2Months = IIf(diff < 0, diff, 0)
End If
ElseIf tb3Months < 0 Then
If tb2Months > 0 Then
diff = tb3Months + tb2Months
tb2Months = IIf(diff < 0, 0, diff)
tb3Months = IIf(diff < 0, diff, 0)
End If
End If
If tb3Months > 0 Then
If tb1Month < 0 Then
diff = tb3Months + tb1Month
tb3Months = IIf(diff < 0, 0, diff)
tb1Month = IIf(diff < 0, diff, 0)
End If
ElseIf tb3Months < 0 Then
If tb1Month > 0 Then
diff = tb3Months + tb1Month
tb1Month = IIf(diff < 0, 0, diff)
tb3Months = IIf(diff < 0, diff, 0)
End If
End If
If tb3Months > 0 Then
If tbCurrent < 0 Then
diff = tb3Months + tbCurrent
tb3Months = IIf(diff < 0, 0, diff)
tbCurrent = IIf(diff < 0, diff, 0)
End If
ElseIf tb3Months < 0 Then
If tbCurrent > 0 Then
diff = tb3Months + tbCurrent
tbCurrent = IIf(diff < 0, 0, diff)
tb3Months = IIf(diff < 0, diff, 0)
End If
End If
If tb2Months > 0 Then
If tb1Month < 0 Then
diff = tb2Months + tb1Month
tb2Months = IIf(diff < 0, 0, diff)
tb1Month = IIf(diff < 0, diff, 0)
End If
ElseIf tb2Months < 0 Then
If tb1Month > 0 Then
diff = tb2Months + tb1Month
tb1Month = IIf(diff < 0, 0, diff)
tb2Months = IIf(diff < 0, diff, 0)
End If
End If
If tb2Months > 0 Then
If tbCurrent < 0 Then
diff = tb2Months + tbCurrent
tb2Months = IIf(diff < 0, 0, diff)
tbCurrent = IIf(diff < 0, diff, 0)
End If
ElseIf tb2Months < 0 Then
If tbCurrent > 0 Then
diff = tb2Months + tbCurrent
tbCurrent = IIf(diff < 0, 0, diff)
tb2Months = IIf(diff < 0, diff, 0)
End If
End If
If tb1Month > 0 Then
If tbCurrent < 0 Then
diff = tb1Month + tbCurrent
tb1Month = IIf(diff < 0, 0, diff)
tbCurrent = IIf(diff < 0, diff, 0)
End If
ElseIf tb1Month < 0 Then
If tbCurrent > 0 Then
diff = tb1Month + tbCurrent
tbCurrent = IIf(diff < 0, 0, diff)
tb1Month = IIf(diff < 0, diff, 0)
End If
End If
Select Case months
Case 3: Dues = tb3Months
Case 2: Dues = tb2Months
Case 1: Dues = tb1Month
Case 0: Dues = tbCurrent
End Select
'Debug.Print tb3Months, tb2Months, tb1Month, tbCurrent
'Test: Dues(50,-10,20,-45,3)'0,0,15,0
End Function
Tom Wickerath said:
Hi Bob,
There's likely something else going on (ie. not just the difference in
recordcount).
Perhaps try solving this problem by a process of elimination. First, does
the query itself fail with the same error on your friend's computer, or is
the error only occuring when your friend attempts to open the report that
is
based on this query? If the report's rowsource is one long SQL statement,
try
saving it as a querydef, so that it shows up in the list of queries
available. Then try running it by itself, with the report closed.
Make a copy of this query, naming it something like 1Temp. Then start
simplifying it by removing the references to the Dues function. Something
like this:
SELECT tblOwnerInfo.OwnerID, [OwnerLastName] & ", " & [OwnerFirstName] AS
OwnerName, nz([3],0) AS tb3Months0, nz([2],0) AS tb2Months0, nz([1],0) AS
tb1Month0, nz([0],0) AS tbCurrent0,
qPayableTotalForPaymentwithTotal.Payable
FROM (tblOwnerInfo INNER JOIN qPayableTotalForPaymentwithTotal ON
tblOwnerInfo.OwnerID = qPayableTotalForPaymentwithTotal.OwnerID) INNER
JOIN
qOverDueRep ON qPayableTotalForPaymentwithTotal.OwnerID =
qOverDueRep.OwnerID
ORDER BY [OwnerLastName] & ", " & [OwnerFirstName];
Does it still error for your friend? If the answer is no, then you need to
look into your Dues function. If the answer is yes, then keep simplifying
the
query. For example, you might remove the qPayableTotalForPaymentwithTotal
query from this query. Alternatively, if you'd like to test the
*possibility*
of 50 records versus 10 being the cause of the problem (which I really
don't
think it is), you should be able to apply additional criteria to reduce
the
number of records selected.
You haven't shown us what the Dues function is that it getting called
here,
but it might be instructive to set a break point at the start of this
function, and step through the code one line at a time, using the F8 key.
You've told us some of your operating environment (WindowsXP..MS Access
2007), although you left out service pack information. Use Start | Run and
enter the command WinVer to learn the service pack for your WindowsXP.
Hopefully, you've already upgraded Access 2007 to SP-1 by now. What
operating
system/office version (with service packs on each) is your friend running?
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
Bob Vance said:
My database is the same as my friends but he has 50 Clients I have 10
,when
I open my report with this query everything is Fine, but when he tries
with
his db he is getting this error "Data type Mismatch in criteria in
expression" is that just because the machine cant compile it?.....Thanks
Bob
SELECT tblOwnerInfo.OwnerID, [OwnerLastName] & ", " & [OwnerFirstName] AS
OwnerName, nz([3],0) AS tb3Months0, nz([2],0) AS tb2Months0, nz([1],0) AS
tb1Month0, nz([0],0) AS tbCurrent0,
Dues([tb3Months0],[tb2Months0],[tb1Month0],[tbCurrent0],3) AS tb3Months,
Dues([tb3Months0],[tb2Months0],[tb1Month0],[tbCurrent0],2) AS tb2Months,
Dues([tb3Months0],[tb2Months0],[tb1Month0],[tbCurrent0],1) AS tb1Month,
Dues([tb3Months0],[tb2Months0],[tb1Month0],[tbCurrent0],0) AS tbCurrent,
qPayableTotalForPaymentwithTotal.Payable
FROM (tblOwnerInfo INNER JOIN qPayableTotalForPaymentwithTotal ON
tblOwnerInfo.OwnerID = qPayableTotalForPaymentwithTotal.OwnerID) INNER
JOIN
qOverDueRep ON qPayableTotalForPaymentwithTotal.OwnerID =
qOverDueRep.OwnerID
ORDER BY [OwnerLastName] & ", " & [OwnerFirstName];
