Syntax Problem with Variable 1

[holgi123]

Hi there,

I have the following problem which I do not get to work:

I need to calculate commission and I dim a variable which I want to set to a table column, however this does not work. BUT the entire calculation works, if I declare the variable but do not reference a table column but rather a simple number:
=============================
=============================

THIS works (part of code only)
==============================
strsratebelow60 = "0" '=
strsrate60to80 = "0.02" '=
strsrate80to100 = "0.04" '=
strsrateabove100 = "0.06" '=
'===========================
strservice = "Iif(MonthlyPerformance < 0.6, " & strsratebelow60 & ", " _
& "Iif(MonthlyPerformance < 0.8, (MonthlyServiceRevenue* " & strsrate60to80 & " ), " _
& "Iif(MonthlyPerformance < 1,(MonthlyServiceRevenue* " & strsrate80to100 & " ), " _
& "Iif(MonthlyPerformance >= 1,(MonthlyServiceRevenue* " & strsrateabove100 & " ), " & strsratebelow60 & " ))))"


THIS DOES NOT works (part of code only)
=======================================
strsratebelow60 = "tblServiceRates.Below60Rate" '=
strsrate60to80 = "tblServiceRates.60to80Rate" '=
strsrate80to100 = "tblServiceRates.80to100Rate" '=
strsrateabove100 = "tblServiceRates.Above100Rate" '=
'===========================
strservice = "Iif(MonthlyPerformance < 0.6, " & strsratebelow60 & ", " _
& "Iif(MonthlyPerformance < 0.8, (MonthlyServiceRevenue* " & strsrate60to80 & " ), " _
& "Iif(MonthlyPerformance < 1,(MonthlyServiceRevenue* " & strsrate80to100 & " ), " _
& "Iif(MonthlyPerformance >= 1,(MonthlyServiceRevenue* " & strsrateabove100 & " ), " & strsratebelow60 & " ))))"

=======================
=======================

I tried endless combinations of '&' and '"' around the variables but do not get it to work.

Any help would be greatly appreciated.

Many thanks

 

[SkipVought]

Hi,

Use Debug.Print strservice

to observe the value of the string you are building.

Skip,

_[red][/red]

 

[TheAceMan1]

how are ya holgi123 . . .

Where are you using [blue]strservice?[/blue]

My guess is a query or SQL. If I'm correct [blue]post the SQL[/blue].

See Ya! . . . . . .

 

[TheAceMan1]

Hit submitt too soon . . .

For the part that doesn't work you have to [blue]pass the table values to the code![/blue]

See Ya! . . . . . .

 

[holgi123]

Hi AceMan1,

thank you for picking this thread up. Here is the SQL:
==================
Set db = CurrentDb
strsql = " SELECT tblmarketingfinal.*, " & strservice & " as ServicePay " into tblcommissionresult FROM tblmarketingfinal "
With db
Set qryNew = db.CreateQueryDef("QueryMaster", strsql)
End With
DoCmd.OpenQuery "QueryMaster"
DoCmd.DeleteObject acQuery, "QueryMaster"
=================

As I said, it works if I put simple numbers into the strservice but not if I want to reference the table column.

I tried adding ' beofre the " sign but did not work either....

Any help would be appreciated.


Many thanks

 

[TheAceMan1]

Roger That holgi123 . . .

So its a make table.

Your problem is with [blue]tblServiceRates[/blue]. Its not a part of the SQL so is therefore external to [blue]tblmarketingfinal[/blue]. You need some way to access [blue]tblServiceRates[/blue] and a [blue]recordset[/blue] would do well here.

My overall Idea is to pass the[blue]MonthlyServiceRevenue[/blue] to your function [blue]strService[/blue] (this occurs per rcord) and return the actual calculated value.

Note: the following code assumes [blue]tblServiceRates[/blue] is a single record table. My guess here is that the rates can change according to criteria you know of. So here we go . . .

Woooooooooooooaaa . . . I started the code and noticed [blue]tblServiceRates[/blue] has fields starting with numbers! Fields shouldn't start this way, first character should be a letter! You havn't run into this yet because you havn't used [blue]tblServiceRates[/blue] in a recordset yet.

You need to change this and let me know the new names. I hope this won't turn out to be a big job . . .

Also what are the datatypes of the rate fields? . . .

See Ya! . . . . . .

 

[holgi123]

Thank You so much for your help TheAceMan1!

Luckily I started to redo the entire commission database last week only, so haven't yet used the table column a lot. Here are the new names:
=================
strsratebelow60 = "tblServiceRates.Below60Rate"
strsrate60to80 = "tblServiceRates.From60to80Rate"
strsrate80to100 = "tblServiceRates.From80to100Rate"
strsrateabove100 = "tblServiceRates.FromAbove100Rate"
=================
BTW field type is number in each column.

Thank you again for your help.

Regards

 

[TheAceMan1]

holgi123 . . .

Finished the code but need to verify datatypes of the following (in the table select the fieldname and at the bottom post back the [blue]Field Size[/blue]):

MonthlyPerformance
MonthlyServiceRevenue
Below60Rate
From60to80Rate
From80to100Rate
FromAbove100Rate

See Ya! . . . . . .

 

[holgi123]

Hi TheAceMan1,

all fields are
Data Type: Number
Field size: Double

Many thanks for youe further help on this, much appreciated.

Cheers

 

[TheAceMan1]

OK holgi123 . . . lets do this!

[ol][li]In a [blue]module[/blue] in the [blue]modules[/blue] window, copy/paste the following function:

[blue]Public Function Svc(MP, MSR)
   [green]'MP is MonthlyPerformance, MSR is MonthlyServiceRevenue[/green]
   Dim db As DAO.Database, rst As DAO.Recordset
   
   Set db = CurrentDb
   Set rst = db.OpenRecordset("tblServiceRates", dbOpenDynaset)
   
   If MP < 0.6 Then
      Svc = rst!Below60Rate
   ElseIf MP < 0.8 Then
      Svc = MSR * rst!From60to80Rate
   ElseIf MP < 1 Then
      Svc = MSR * rst!From80to100Rate
   Else [green]'MP >= 1[/green]
      Svc = MSR * rst!FromAbove100Rate
   End If
   
   Set rst = Nothing
   Set db = Nothing
   
End Function[/blue]

[/li]
[li]Next . . . change your strSQL assignment to:

[blue]strsql = "SELECT tblMarketingFinal.*, " & _
                 [purple][b]Svc([MonthlyPerformance], MonthlyServiceRevenue)[/b][/purple] & " as ServicePay " & _
         "INTO tblCommissionResult FROM tblMarketingFinal;"[/blue]

[/li][/ol]
Thats it . . . give it a whirl and let me know . . .

BTW . . . I did find an error in your origional sql. Its an extra [blue]double quote[/blue], below in red:

[blue]strsql = " SELECT tblmarketingfinal.*, " & strservice & " as ServicePay [COLOR=yellow red]" [/color]into tblcommissionresult FROM tblmarketingfinal "[/blue]

See Ya! . . . . . .

 

[holgi123]

TheAceMan1,

OmG! Thank You so much for helping me on this. Yes it does work (...and I even deployed it already in another area, where I had a similar challange).

Again, much appreciated your support on this.

Cheers

 

[holgi123]

Sorry TheAceMan1,

false alarm. I still used the old backup database when I looked at the results and did not initially realised I did not test the code.

When I run your code I get an eror message saying "Compile Error: External name not defined" and the code highlights the word MonthlyPerformance.

I tried:

# replace [ braket with (
# use no [ braket
# placed function within same module

All attempts failed :-(

Any idea how I could fix this as it feels I'm close to getting there....

Thank You again.

 

[TheAceMan1]

holgi123 . . .

Where does [blue]MonthlyPerformance[/blue] reside? . . . I thought it was a member of [blue]tblMarketingFinal[/blue]

See Ya! . . . . . .

 

[holgi123]

Hi again,

I do not know why but now it runs without an error, after I removed the [ braket, which is good. The problem is that it somehow does not update the table column. I run the function in the immediate window and it calculates correctly. I changed the table set-up so that it only deals with service revenues and commission to extract the problem and her is the current set-up:

=========
Public Function Svc(MP, MSR)
'MP is MonthlyPerformance, MSR is MonthlyServiceRevenue
Dim db As DAO.Database, rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblServiceRates", dbOpenDynaset)

If MP < 0.6 Then
Svc = rst!Below60Rate
ElseIf MP < 0.8 Then
Svc = MSR * rst!From60to80Rate
ElseIf MP < 1 Then
Svc = MSR * rst!From80to100Rate
ElseIf MP >= 1 Then
Svc = MSR * rst!FromAbove100Rate
End If

Set rst = Nothing
Set db = Nothing

End Function
===============

Public Function Leasing(MLP, MLR)
'MLP is MonthlyPerformance, MLR is MonthlyLeasingRevenue
Dim db As DAO.Database, rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tblLeasingRates", dbOpenDynaset)

If MLP < 0.6 Then
Leasing = rst!NotAchievedRate
ElseIf MLP >= 0.6 Then
Leasing = MLR * rst!AchievedRate
End If

Set rst = Nothing
Set db = Nothing

End Function


===============
Public Sub ServiceCalculation()
Dim db As Database
Dim qryNew As QueryDef
Dim strsql As String

Set db = CurrentDb
strsql = " SELECT tblservicefinal.*, " & Svc(MonthlyPerformance, MonthlyServiceRevenue) & " as ServicePay," & Leasing(MonthlyPerformance, MonthlyLeaseRevenue) & " as LeasingPay into tblserviceresult FROM tblservicefinal "
With db
Set qryNew = db.CreateQueryDef("QueryMaster", strsql)
End With
DoCmd.OpenQuery "QueryMaster"
DoCmd.DeleteObject acQuery, "QueryMaster"
End Sub
==============

As you can see, I basically copied the function for leasing calculation as well. Same situation, in the immeidate window it shows correct results but ont in table.


Thank you again for your tremendous support on this.

Cheers

 

[PHV]

And what about this ?
strsql = "SELECT *, Svc(MonthlyPerformance,MonthlyServiceRevenue) AS ServicePay, Leasing(MonthlyPerformance,MonthlyLeaseRevenue) AS LeasingPay INTO tblserviceresult FROM tblservicefinal;"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[holgi123]

TheAceMan1 & PHV,

Thank You soooooooo much to both of you for helping me in getting this done. It finally works and calculates everything correctly.

It will also help me in getting my code much shorter and less confusing.

Again, thank you very much.

Regards