Invalid argument in a query (1 Viewer)

[mh123]

Hi

I have a db and when I put an unbound text box with a formula in it on the 'main' form as such and it calculates all as intended, but when I put this formula into a query and go to run the query it just says 'Invalid Argument'.

I've done some looking around and my db isn't over 2gb and I don't believe it to be corrupt (I can make queries etc but when I try getting this to work in a query, it gives the error)

=IIf([eventOneOffFee]>0,[eventOneOffFee],IIf([[Quoted Minimum Numbers]=0,([finalCLientNumbers]*[Full Price])*(IIf([courseDays]=1 Or [courseDays]=3,1,2)),IIf([finalClientNumbers]>[Tier1Delegates] And [Tier1Delegates]>0,(([Quoted Minimum Numbers]*[Full Price])*(IIf([courseDays]=1 Or [courseDays]=3,1,2)))+(([Tier 1 Discount]*([Tier1Delegates]-[Quoted Minimum Numbers]))*(IIf([courseDays]=1 Or [courseDays]=3,1,2)))+(([Tier 2 Discount]*([finalClientNumbers]-[Tier1Delegates]))*(IIf([courseDays]=1 Or [courseDays]=3,1,2))),(([Quoted Minimum Numbers]*[Full Price])*(IIf([courseDays]=1 Or [courseDays]=3,1,2)))+(([Tier 1 Discount]*([finalClientNumbers]-[Quoted Minimum Numbers]))*(IIf([courseDays]=1 Or [courseDays]=3,1,2))))))

The above is what I have and it works perfectly (in the unbound text box) but I want it to be in a query and not an unbound text box, when I put that into a query with all the relevant fields selected it gives me invalid argument.

Would anyone be able to shed some light or let me know if i'm doing something totally wrong or if I may even be going the wrong way about things?

Thanks for looking

 

[pr2-eugin]

Wow, I got confused after the second IIF. Could you not do this with a function? If you explain the various scenarios then me/someone better than me could help you write the function.

 

[mh123]

Hello

Multiple IFs indeed which I could explain but it's basically working out the final amount to invoice a client for, based on a few things. Sorry if i'm being really silly here but what do you mean by with a function? Do you mean to put it in a module and write it in VBA so it gets worked out that way?

My thought was that as it works correctly in the unbound text box that it should also work in a query?

Thanks for taking a look at least, hoping I can get it sorted with a bit of guidance

 

[pr2-eugin]

Do you mean to put it in a module and write it in VBA so it gets worked out that way?

Yes, that is exactly what I mean, create a function in a module and then feed the fields you want to compare against.

My thought was that as it works correctly in the unbound text box that it should also work in a query?

Not really, you need to "adapt" it to act in a Query, for example if you use Me.controlName in a Form, then in a Query you should use Forms!FormName!controlName. Anything other will say invalid, because it is. Me. is not recognized in Queries. Or any controls if they are not included in the Query, but are in the Form. So a careful mapping is needed !

Thanks for taking a look at least, hoping I can get it sorted with a bit of guidance

Happy to help ! Good Luck !

 

[mh123]

Hey

On the topic of adapting it for a query, I haven't referenced any specific controls from a form just the field names from the query that the form is based on, are there any other rules per say that should be followed when trying to get it to work in a query?

I'll give the VBA bit a crack after my meeting and let you know how I get on - one quick Q how would one get the function into my query as that's one thing I haven't done before

Thanks again!

 

[pr2-eugin]

Create a New module and enter your function there, Save and Compile then in your Query you can use the function name as you would normally use any other function in a Query.

 

[mh123]

Hi back at my desk, putting it into a function with the calculcations I want based on the IFs i'm getting a 424 with object not defined, I have a feeling I am doing something wrong it being the first time I've made me a function - would it be possible to get 2c more from you?

Thus far;

Function eventTotalFee()
Dim Days As Integer
If courseDays = 1 Or courseDays = 3 Then
    Days = 1
    Else
    Days = 2
End If
If eventOneOffFee > 0 Then
    eventTotalFee = eventOneOffFee
ElseIf [Quoted minimum numbers] = 0 Then
    eventTotalFee = ([finalClientNumbers] * [Full Price]) * Days
ElseIf [Quoted minimum numbers] > 0 And [Tier1Delegates] = 0 Then
    eventTotalFee = (([Quoted minimum numbers] * [Full Price]) * Days) + (([Tier1discount] * _
    ([finalClientNumbers] - [Quoted minimum numbers])) * Days)
ElseIf [finalClientNumbers] > [Tier1Delegates] And [Tier1Delegates] > 0 Then
    eventTotalFee = (([Quoted minimum numbers] * [Full Price]) * Days) + (([Tier1discount] * _
    ([Tier1Delegates] - [Quoted minimum numbers])) * Days) + _
    (([Tier 2 Discount] * ([finalClientNumbers] - [Tier1Delegates])) * Days)
ElseIf [finalClientNumbers] < [Tier1Delegates] And [Tier1Delegates] > 0 Then
    eventTotalFee = (([Quoted minimum numbers] * [Full Price]) * Days) + _
    (([Tier1discount] * ([finalClientNumbers] - [Quoted minimum numbers])) * Days)
End If
End Function

Any input appreciated on what i'm doing wrong, if this needs to go in a different forum now also let me know!

Thanks again

 

[pr2-eugin]

Try this function.

Public Function calculateMode(feeOne As Long, minNo As Long, _
                              cliNo As Long, fPrice As Double, _
                              courseDays As Integer, t1Del As Long, _
                              t1Disc As Long, t2Disc As Long) As Double
[COLOR=Green]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]
    Dim mulVal As Integer                          
    If courseDays = 1 Or courseDays = 3 Then
        mulVal = 1
    Else
        mulVal = 2
    End If
    
    If feeOne > 0 Then
        calculateMode = feeOne
    Else
        If minNo = 0 Then
            calculateMode = cliNo * fPrice * mulVal
        Else
            If t1Del > 0 And cliNo > 0 Then
                calculateMode = (minNo * fPrice * mulVal) + ((t1Disc * (t1Del - minNo)) * mulVal) _
                                                          + ((t2Disc * (cliNo - t1Del)) * mulVal)
            Else
                calculateMode = (minNo * fPrice * mulVal) + ((t1Disc * (t1Del - minNo)) * mulVal)
            End If
        End If
    End If
End Function

To use in the Query, just use

SELECT theOtherFields, 
calculateMode([eventOneOffFee], [Quoted Minimum Numbers], [finalCLientNumbers], [Full Price], [courseDays], [Tier1Delegates], [Tier 2 Discount]) As theValueYouWant
FROM theTable;

You might have to change the data types in the function argument list.

 

[mh123]

this looks like a good starting point and I am kinda following what's going on which is nice! Thanks a bunch good sir will let you know how this turns out tomorrow or later

 

[mh123]

Hey

Ok I have the code in how I think I'd want it - I Nz'd everything to give it a 0 value incase something comes back null to stop that cocking things up if it would, but in the query it's returning #error. Would this be because of the function types or would it be something with the code?

Here's where i'm at so far;

Function eventTotalFee(feeOne As Long, minNo As Long, _
                        cliNo As Long, fullPrice As Double, courseDays As Long, _
                        t1del As Long, t1Disc As Long, t2Disc As Long) As Long
Dim Days As Long
If courseDays = 1 Or courseDays = 3 Then
    Days = 1
    Else
    Days = 2
End If
If Nz([feeOne], 0) > 0 Then
    eventTotalFee = Nz([feeOne], 0)
ElseIf Nz([minNo], 0) = 0 Then
    eventTotalFee = Nz([cliNo], 0) * Nz([fullPrice], 0) * Days
ElseIf Nz([minNo], 0) > 0 And Nz([t1Disc], 0) = 0 Then
    eventTotalFee = Nz([cliNo], 0) * Nz([fullPrice], 0) * Days
ElseIf Nz([minNo], 0) > 0 And Nz([t1del], 0) = 0 Then
    eventTotalFee = ((minNo * Nz([fullPrice], 0)) * Days) + ((Nz([t1Disc], 0) * (Nz([cliNo], 0) - Nz([minNo], 0))) * Days)
ElseIf Nz([cliNo], 0) > Nz([t1del], 0) And Nz([t1del], 0) > 0 Then
    eventTotalFee = ((Nz([minNo], 0) * Nz([fullPrice], 0)) * Days) + ((Nz([t1Disc], 0) * (Nz([t1del], 0) - Nz([minNo], 0))) * Days) + _
    ((Nz([t2Disc], 0) * (Nz([cliNo], 0) - Nz([t1del], 0))) * Days)
ElseIf Nz([cliNo], 0) < Nz([t1del], 0) And Nz([t1del], 0) > 0 Then
    eventTotalFee = ((Nz([minNo], 0) * Nz([fullPrice], 0)) * Days) + ((Nz([t1Disc], 0) * (Nz([cliNo], 0) - Nz([minNo], 0))) * Days)
End If
End Function

and the query sql containts

eventTotalFee([eventOneOffFee],[Quoted Minimum Numbers],[finalCLientNumbers],[Full Price],[courseDays],[Tier1Delegates],[Tier 1 Discount],[Tier 2 Discount]) AS totalFee

Any more help would be appreciated - quite enjoying getting my head around this function shindig thus far!

Thanks

 

[pr2-eugin]

Okay a few things,

1. You need Nz in the Query not in the function !
2. Are you sure the t1Disc and t2Disc are Long? Normally discounts are percentages, which means it would be double values. So my question - Have you check the corresponding data types to be ported in the function?
3. Go with the structure I have shown, although they mean the same, my code is easier to read so fixing error would be simpler. ElseIf is just too crowded.

 

[mh123]

t1disc and t2disc aren't percentages they're values - set up that way so it's faster for people to answer queries I think on the phone.

I put the Nz in the query as you suggested and it's working as intended, so thank you very much for pointing me in the right direction with your know how!

One problem solved... many more to go I shall imagine!

Thanks again

 

[pr2-eugin]

No problem ! Good Luck !