2 dbs one gets error

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" <AOS168b AT comcast DOT net> wrote in message
news:3BC5653E-2088-4929-9AEC-EDD8E7F2E4EE@microsoft.com...
> 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" wrote:
>
>> 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];
>>
>>
>> --
>> Thanks in advance for any help with this......Bob
>> WindowsXP..MS Access 2007
 >> Stay informed about: 2 dbs one gets error 

Hi Bob,

Any missing (ie. nulls present) in his data, where the function is expecting
a value? Any chance of the wrong data type being fed to the function, such as
a string when the function might be expecting a currency (numeric) data type?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

"Bob Vance" wrote:

> 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

<snip>
 >> Stay informed about: 2 dbs one gets error