Module/query problem

[swift]

Hi all

I have a problem with a query/module which I’m stumbling around a bit with, looking for some assistance if possible!!

I have the following module (wWhere AorB is a numeric field):

Public Function MPF(AorB As Single) As String

If AorB = 0 Then
    MPF = "[MPStart] + [Fr]"
    
ElseIf AorB = 1 Then
    MPF = "[MPStart] - [Fr]"
    
Else
    MPF = "ERROR"

End If

End Function

Basically I’m trying to get my query to calculate the results of:

[MPStart] + [Fr] where AorB is equal to 0

Or

[MPStart] – [Fr] where AorB is equal to 1

The fields [MPStart] and [Fr] are both included in the query.
For example for any given record, [MPStart] could be 24.940, [Fr] could be 0.1. Depending upon the status of AorB (let’s say B in this instance), the result returned could be 24.940 -0.1 = 24.840.

At the moment the query is returning the result exactly as shown in the module (as a consequence of the quotation marks I guess). I’ve tried removing the quotation marks and get the message : Compile Error: External Name not defined.


Can anybody please offer any advice/assistance (I’m using A2007 btw)?

Thanks in advance.

Swift

 

[spikepl]

You are attempting something impossible: In two cases you have a numerical value and in the third case a string. You cannot stuff that into the same field.

Make a calculated field CF1: [MPStart] + (1- 2* [AorB])*[Fr] that covers the first two. And a second text field, where you can have your strings : CF2: Iif([AorB]=0 or [AorB=1], Cstr[CF1],"Error")
I am not sure what AorB is. If that is a logical expression the numerical value that corresponds to True is -1 , and not 1.

 

[swift]

Okay, now I'm getting it. However, the second part isn't working for me.

CF2: Iif([AorB]=0 or [AorB]=1], Cstr[CF1],"Error")

I've tried removing the extra ] thus:

CF2: Iif([AorB]=0 or [AorB]=1, Cstr[CF1],"Error")

but it keeps coming back to invalid syntax error???

Thanks for the help so far!

 

[spikepl]

CF2: Iif([AorB]=0 or [AorB]=1, Cstr[COLOR=Red]([/COLOR][CF1][COLOR=Red])[/COLOR],"Error")

 

[swift]

Thats solved it, thanks spikepl!