Is there limit on Calcuations through Expression in a Query? 1

[khwaja]

I am trying to calculate a score based on combination of criteria.

IIf([DevType] Like "*Refurb*" And [Loc Type] Like "*FS*" And [Type]="Supermarket",9 And IIf([DevType] Like "*Refurb*" And [Loc Type] Like "*FS*" And [Type]="Foodstore",8, 0))))

The above statment is a shortened version of the statement I am using. In order to take into account all criteria, I still need to add more expression but when I try to save, I get the message "The String returned by Builder was too long. The Result will be truncated".

If there is a limt,l then what is the alternative way to get results. I am not confident writing code.

Regards

 

[tlbroadbent]

In reality, the VB code in a module would probably be easier to write and understand than a complex IIF function.

Public Function DetermineScore (DevType As String, _
LocType As String, Type As String) As Integer

If Instr(DevType,"Refurb&quot>0 And _
Instr(LocType,"FS&quot>0 And _
Type="Supermarket" Then
DetermeineScore=9
ElseIf Instr(DevType,"Refurb&quot>0 And _
Instr(LocType,"FS&quot>0 And _
Type="Foodstore" Then
DetermeineScore=8
.
.
.
EndIf

You can probably simplify the code and reduce the number of lines greatly in a VB module. Once created, you can use the function in code, on forms, in reports, or in queries.

Query example:

Select
DevType, LocType, Type, ...,
DetermineScore(DevType, LocType, Type) As Score
From TableName Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[khwaja]

Thanks for your help. I am encouraged to try out VB based on your response. As you can tell I am a novice, so to make things easier I copied your code to module which starts with Option Compare Database and Option Explicit statement. I copied the code after this and before I could modify it, I noticed that all lines went red. This I understand is signalling that something is wrong. Sorry for asking you to hand hold but could you please let me know where did I make a mistake. I added an End Function at the bottom to get rid of red lines but to no avail.

PS: I have created a query to use this function.

As always, very much appreciate your help.

Regards

AK

 

[tlbroadbent]

Type is a keyword in Access. Change the references to sType or some other non-keyword name.

Public Function DetermineScore(DevType As String, _
LocType As String, sType As String) As Integer

If InStr(DevType, "Refurb&quot > 0 And _
InStr(LocType, "FS&quot > 0 And _
sType = "Supermarket" Then
DetermeineScore = 9
ElseIf InStr(DevType, "Refurb&quot > 0 And _
InStr(LocType, "FS&quot > 0 And _
sType = "Foodstore" Then
DetermeineScore = 8
End If

End Function
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[khwaja]

Much appreciate your guidance. It works fine and looks like I had my initiation to VB. Will take a while to fully understand though. One problem is how to return a Null value in case the DevType is Null. I tried using the following statement as the first command so that when there is no dev type shown, it returns a null and terminates the procedure. However, it instead returns #Error for blank records. .

If InStr(DevType, Null) Then
DetermineScore1 = 0

I am sure I am not using the right syntax. I will appreciate a little help. Secondly, is the use of Instr equivalent to applying wild card?


Cheers

 

[tlbroadbent]

To determine if a column or value is null use the IsNull function.

If Isnull(DevType) Then
DetermineScore1 = 0

Instr finds a string inside of another string or column.

Where Instr(ColX,"refurb&quot>0 is equivalent to
Where ColX Like "*refurb*"

In the VB module, I recommend using INSTR rather than LIKE. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[khwaja]

Thanks . I figured out the sanme solution after sending my note but this still returns error. Any ideas why?

Cheers

 

[tlbroadbent]

Without seeing the entire function, I can only guess. You should check for null first because other functions such as Instr return null if the a field is null and the numeric compare may fail. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[khwaja]

Thanks. This may have something to do with the later test wherby we are asking to check if it is greater than 0. I am using the following code.

Public Function DetermineScore1(DevType As String, _
LocType As String, sType As String) As Integer

If IsNull(DevType) Then
DetermineScore1 = 0
ElseIf InStr(DevType, "New&quot > 0 And _
InStr(LocType, "FS&quot > 0 And _
sType = "Supermarket" Then
DetermineScore1 = 10
ElseIf InStr(DevType, "New&quot > 0 And _
InStr(LocType, "FS&quot > 0 And _
sType = "Foodstore" Then
DetermineScore1 = 8
End If

End Function

 

[khwaja]

Terry

Did have a chance to look at the above problem?

Regards

 

[tlbroadbent]

Try this. It will handle NULL and empty strings.

If Nz(DevType,"&quot="" Then
DetermineScore1 = 0

Or use this if NZ doesn't work.

If IsNull(DevType) Or DevType="" Then
DetermineScore1 = 0 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[khwaja]

Thanks Terry. Unfortunately none of these solutions are helping to get rid of #error appearing in my query for records where Dev Type has not been indicated. Though within module, these both codes were fine.

Could this be happening due to problem in data type of resulting score?

Cheers

 

[khwaja]

Terry, I am copying the SQL code in case this might be of help.

SELECT CompetitorDetail.CompDetailID, CompetitorDetail.LocNo, CompetitorDetail.Competitor, CompetitorDetail.SInfo, CompetitorDetail.sType, CompetitorDetail.Centre, CompetitorDetail.Address, CompetitorDetail.DevType, CompetitorDetail.DevDate, CompetitorDetail.LeaseConditions, DetermineScore1([DevType],[LocType],[sType]) AS Score, [Stores DB].LocType
FROM [Stores DB] LEFT JOIN CompetitorDetail ON [Stores DB].LocNo = CompetitorDetail.LocNo;

Cheers

 

[tlbroadbent]

Change the function to accept Variant parameters rather than String parameters.

Function DetermineScore1(DevType, LocType, sType) As Integer
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.