Hi,

I have the following expression in my db

Client Cost: IIf(Bookings!Currency="CPM",IIf([SumOfImpressions]/Bookings!Amount>1,Bookings![Client Cost (Euro)],[SumOfImpressions]/1000*Bookings![Rate (Euro)]*(1-Bookings![Media Owner Commission]+0.0675)+[SumOfImpressions]/1000*0.12),IIf(Bookings!Currency="CPC",[SumOfClicks]*Bookings![Rate (Euro)]*(1-Bookings![Media Owner Commission]+0.0675)+[SumOfClicks]*0.02,IIf(Bookings!Currency="Tenancy",IIf([end_date]>Bookings![End Date],Bookings![Client Cost (Euro)]*1/DateDiff("d",Bookings![Start Date],Bookings![End Date]),DateDiff("d",[start_date],[end_date])/DateDiff("d",Bookings![Start Date],Bookings![End Date])*Bookings![Client Cost (Euro)]*1/DateDiff("d",Bookings![Start Date],Bookings![End Date])),0)))

This runs up till a certain date and then begins to fail when there are new bookings running. Access says to simplify it. How can I do this?

Also, why would it fail?

I know it is this expression that is the problem as when I remove it the query runs. It has worked fine up till now and nothing has changed other than new records being added to both tables (ALL MARKETS and Bookings)

Any help is appreciated.

Lets first make this monster (more) readable!

IIf(Bookings!Currency="CPM"
,IIf([SumOfImpressions]/Bookings!Amount>1
,Bookings![Client Cost (Euro)]
,[SumOfImpressions]/1000*Bookings![Rate (Euro)]*(1-Bookings![Media Owner Commission]+0.0675)+[SumOfImpressions]/1000*0.12
)
,IIf(Bookings!Currency="CPC"
,[SumOfClicks]*Bookings![Rate (Euro)]*(1-Bookings![Media Owner Commission]+0.0675)+[SumOfClicks]*0.02
,IIf(Bookings!Currency="Tenancy"
,IIf([end_date]>Bookings![End Date]
,Bookings![Client Cost (Euro)]*1/DateDiff("d",Bookings![Start Date],Bookings![End Date])
,DateDiff("d",[start_date],[end_date])/DateDiff("d",Bookings![Start Date],Bookings![End Date]) * Bookings![Client Cost (Euro)]*1/DateDiff("d",Bookings![Start Date],Bookings![End Date])
)
,0
)
)
)

since its probably an excisting thing, I am not even going to start about table structure or column names... but boy....

I would pick one of the following solutions:
1)
Create a function that calculates this

2)
Query on query.... Create a query to calculate each of these here formula's into columns.
Then make a query that will have a simular IIF structure but not have the formula's there. Instead just showing different columns.

Good luck, these "too complex" errors are always a pain.

How do I make a function? Is it like writing a UDF in excel in VBA?

I am new to Access and have taught myself thus far.

The column headings are a result of what comes out of the reporting system. I just feed the data directly into Access.

Yes very much like a UDF in excel VBA, a little different but very much alike.

Hey,

Next time it screws up I'll bear that in mind but for now I have fixed it.

I had text values in my [Rate (Euro)] field which was screwing the whole thing up.

Thanks for the advice though! I'm glad to see there is a site like MrExcel but for Access.

Well no accounting for data problems...

This forum has an excel part as well as a Access one... Just that the Access one is slightly bigger because, well frankly access is so much more than excel.

to coin a fraze
Once you go Access you never go back!

'Client Cost: IIf(Bookings!Currency="CPM",IIf([SumOfImpressions]/Bookings!Amount>1,Bookings![Client Cost (Euro)],[SumOfImpressions]/1000*Bookings![Rate (Euro)]*(1-Bookings![Media Owner Commission]+0.0675)+[SumOfImpressions]/1000*0.12),IIf(Bookings!Currency="CPC",[SumOfClicks]*Bookings![Rate (Euro)]*(1-Bookings![Media Owner Commission]+0.0675)+[SumOfClicks]*0.02,IIf(Bookings!Currency="Tenancy",IIf([end_date]>Bookings![End Date],Bookings![Client Cost (Euro)]*1/DateDiff("d",Bookings![Start Date],Bookings![End Date]),DateDiff("d",[start_date],[end_date])/DateDiff("d",Bookings![Start Date],Bookings![End Date])*Bookings![Client Cost (Euro)]*1/DateDiff("d",Bookings![Start Date],Bookings![End Date])),0)))

Converts to :

Public Function ClientCost(nCurrency As Currency, nImpressions As Integer, nAmount As Double, nClientCostEuro As Double, nEuroRate As Double, nCommission As Double, nClicks As Integer, nStartDate1 As Date, nEndDate1 As Date, nStartDate2 As Date, nEndDate2 As Date)
'The parameters nStartDate1 and nEndDate1 represent the
'actual contents from the Bookings table.
'Whereas the nStartDate2 and nEndDate2 are parameters passed to the query
'As all data is sourced from the bookings table thenre is no need to name it

'You may have to play about with the formulae, this is how I interpreted it.
'This is aircode and as such is untested.

'How to use this function:
'In your query add a new column and call this function by passing the fields to the
'function in the order as they appear in the function, such as:

'CostForClient:ClientCost([Currency],[SumOfImpressions],Amount,etc)


Select Case nCurrency
Case "CMP"
If nImpressions / nAmount > 1 Then
ClientCost = nClientCostEuro
Else
ClientCost = ((nImpressions / 1000) * nEuroRate) * (nCommission + 0.0675) + (nClicks * 0.02)
End If
Case "CPC"
ClientCost = (nClicks * nEuroRate) * ((1 - nCommission + 0.0675) + (nClicks * 0.02))
Case "Tenancy"
If nEndDate1 > nEndDate2 Then
ClientCost = nClientCostEuro * (1 / DateDiff("d", nStartDate1, nEndDate1))
Else
ClientCost = (DateDiff("d", nStartDate1, nEndDate1) / DateDiff("d", nStartDate2, nStartDate2) * nClientCostEuro) * (1 / DateDiff("d", nStartDate2, nEndDate2))
End If
Case Else
ClientCost = 0
End Select


End Function

David

Thanks very much David. Unfortunately because of time constraints (I need to get this report out today) I am not going to be able to test your code today but I will at somepoint in the future and let you know if it works.

SumOfImpressions is the sum of the Impressions fields in the ALL MARKETS table

SumOfClicks is the sum of the Clicks field.

Does that change anything?


<<<<<<<<<<<<<<<<<<<<<<<<EDIT>>>>>>>>>>>>>>>>>>>>>>>>

Oh, I see you have passed these sums in. Cheers. And I am going to try and use this now as I hadn't solved the problem after all.

Still getting the errors!

Ok, I have created the function as follows:

Option Compare Database

Public Function Cost(nCurrency As String, nImpressions As Integer, nAmount As Double, nClientCostEuro As Double, nEuroRate As Double, nCommission As Double, nClicks As Integer, nStartDate1 As Date, nEndDate1 As Date, nStartDate2 As Date, nEndDate2 As Date)

Select Case nCurrency
Case "CPM"
If nImpressions / nAmount > 1 Then
ClientCost = nClientCostEuro
Else
ClientCost = ((nImpressions / 1000) * nEuroRate) * (1 - nCommission + 0.0675) + (nImpressions / 1000 * 0.12)
End If
Case "CPC"
ClientCost = (nClicks * nEuroRate) * ((1 - nCommission + 0.0675) + (nClicks * 0.02))
Case "Tenancy"
If nEndDate1 > nEndDate2 Then
ClientCost = nClientCostEuro * (1 / DateDiff("d", nStartDate1, nEndDate1))
Else
ClientCost = (DateDiff("d", nStartDate2, nEndDate2) / DateDiff("d", nStartDate1, nStartDate1) * nClientCostEuro) * (1 / DateDiff("d", nStartDate1, nEndDate1))
End If
Case Else
ClientCost = 0
End Select


End Function

And I have called it in an expression as follows:

Client Cost: Cost([Currency],[SumOfImpressions],[Amount],[Client Cost (Euro)],[Rate (Euro)],[Media Owner Commssion],[SumOfClicks],[Start Date],[End Date],[start_date],[end_date])

However, now I get the follwing error about an undefined function

"Undefined function Cost in expression."

I have tried this:

http://support.microsoft.com/kb/824277

but I do not have a "MISSING REFERENCE" check box in References.

The boxes I have checked in references are as follows:

Visual Basic for Apps
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library


Any ideas guys?


<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<EDIT>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

UPDATE: I added the library reference "acwzlib" and the undefined function stopped coming up.

Now I am getting another "expression too complicated " error though. Driving me up the wall. I'm meant to have this finished in 30mins

I have removed all expressions apart from the ClientCost one and I am still getting this F**king "complicated" error.

This is with the funtion!

Other than a suggestion to check your data and make sure you are not deviding by 0 and stuff like that I am stuck :(

Cost is a reserved word change that.

Have you placed the function in a module that has a different name than the function?

Module:
ModMain

Public Function ClientCost(....) As Double
End Function

Just realised I did not include the "As Double" at the end of the function so it always returned Empty

And like I said you will need to proof read the formulae as you are the author of them,I only translated them.

David

If you still have trouble send me a snipped of the mdb to work on.

David

Hi,

The module is called "Module1"

I have renamed the function "CCost" and all references to it within the code.

I have made sure that all values in the Bookings![Amount] field are greater than 0 to avoid "DIV/0" errors.

I have added "As Double" to the end of the function.

I have proof read the formulae and made a couple of minor changes since your original and these are in the previous version I posted.

All records that are "Tenancies" have dates stored against them so that DateDiff is never equal to 0.

Here is the function and the expression:

ClientCost: CCost([Currency],[SumOfImpressions],[Amount],[Client Cost (Euro)],[Rate (Euro)],[Media Owner Commission],[SumOfClicks],[Start Date],[End Date],[start_date],[end_date])

Public Function CCost(nCurrency As String, nImpressions As Integer, nAmount As Double, nClientCostEuro As Double, nEuroRate As Double, nCommission As Double, nClicks As Integer, nStartDate1 As Date, nEndDate1 As Date, nStartDate2 As Date, nEndDate2 As Date) As Double

Select Case nCurrency

Case "CPM"

If nImpressions / nAmount > 1 Then

CCost = nClientCostEuro
Else

CCost = ((nImpressions / 1000) * nEuroRate) * (1 - nCommission + 0.0675) + (nImpressions / 1000 * 0.12)

End If

Case "CPC"

CCost = (nClicks * nEuroRate) * ((1 - nCommission + 0.0675) + (nClicks * 0.02))

Case "Tenancy"

If nEndDate1 < nEndDate2 Then

CCost = nClientCostEuro * (1 / DateDiff("d", nStartDate1, nEndDate1))
Else

CCost = DateDiff("d", nStartDate2, nEndDate2) / DateDiff("d", nStartDate1, nStartDate1) * (1 / DateDiff("d", nStartDate1, nEndDate1)) * nClientCostEuro

End If

Case Else

CCost = 0

End Select


End Function





I am at a loss. Again.

I have attached a snippet of the database for you to check.

I have had a look at your schema, and the main problem is that your data is not normalised. fields that are in your all bookings table could have been best created in a 1-M table using the PMC as the join key. I could provide a solution to your issue but is is not going to be no small feat. Alternatively I suggest you go down the union query route on your summed fields and introduce them into a new query.

Here is a snippet of code based on your data

Select [Placement Media Code] As PMC, [mscded_StartofRegistration_1 Conversions] As RegStart, 0 as Funded, [mscded_ConfirmationofRegistration_1 Conversions] As Confirmed From [All Markets]
UNION ALL SELECT [Placement Media Code] As PMC,[mscdem_StartofRegistration_1 Conversions] As RegStart, [mscdem_CompletedRegistration_1 Conversions] As Funded, [mscdem_ConfirmationofRegistration_1 Conversions] As Completed From [All Markets]
UNION ALL SELECT [Placement Media Code] As PMC,[mschun_StartofRegistration_1 Conversions] As RegStart,[mschun_CompletedRegistration_1 Conversions] as Funded, [mschun_ConfirmationofRegistration_1 Conversions] As Completed From [All Markets]
UNION ALL SELECT [Placement Media Code] As PMC, [mscpld_StartofRegistration_1 Conversions] As RegStart, 0 as Funded, [mscpld_ConfirmationofRegistrationv1_1 Conversions] As Completed From [All Markets]
UNION ALL SELECT [Placement Media Code] As PMC,[mscpor_StartofRegistration_1 Conversions] As RegStart, [mscpor_CompletedRegistration_1 Conversions] as Funded,[mscpor_ConfirmationofRegistration_1 Conversions] As Completed From [All Markets]
UNION ALL SELECT [Placement Media Code] As PMC,[mscrus_StartofRegistration_1 Conversions] As RegStart, [mscrus_FirstFundedSignUp_1 Conversions] as Funded, [mscrus_Registration_1 Conversions] As Completed From [All Markets]
UNION ALL SELECT [Placement Media Code] As PMC,[mscsbf_StartofRegistration2_1 Conversions] As RegStart, [mscsbf_CompletedRegistration2_1 Conversions] As Funded, [mscsbf_ConfirmationofRegistration_1 Conversions] As Completed From [All Markets]
UNION ALL SELECT [Placement Media Code] As PMC,[mscsdd_StartofRegistration_1 Conversions] As RegStart, [mscsdd_CompletedRegistration_1 Conversions] As Funded, [mscsdd_ConfirmationofRegistration_1 Conversions] As Completed From [All Markets]
UNION ALL SELECT [Placement Media Code] As PMC,[mscsdp_StartofRegistration_1 Conversions] As RegStart, [mscsdp_CompletedRegistration_1 Conversions] As Funded, [mscsdp_ConfirmationofRegistration_1 Conversions] As Completed From [All Markets]
UNION ALL SELECT [Placement Media Code] As PMC,[mscsnd_StartofRegistration_1 Conversions] As RegStart, [mscsnd_CompletedRegistration_1 Conversions] As Funded, [mscsnd_ConfirmationofRegistration_1 Conversions] As Completed From [All Markets]
UNION ALL SELECT [Placement Media Code] As PMC,[mscspc_StartofRegistration_1 Conversions] As RegStart, [mscspc_CompletedRegistration_1 Conversions] As Funded, [mscspc_ConfirmationofRegistration_1 Conversions] As Completed From [All Markets]
UNION ALL SELECT [Placement Media Code] As PMC,[mscspd_MiapuestaStartofRegistration_1 Conversions] As RegStart,[mscspd_Miapuestasuccessfulfirstfundedaccount_1 Conversions] As Funded, [mscspd_MiapuestaCompletedRegistration_1 Conversions] As Completed From [All Markets]
UNION ALL SELECT [Placement Media Code] As PMC,[mscspo_StartofRegistration_1 Conversions] As RegStart, [mscspo_CompletedRegistration_1 Conversions] As Funded,[mscspo_ConfirmationofRegistration_1 Conversions] As Completed From [All Markets]
UNION ALL SELECT [Placement Media Code] As PMC,[msctke_StartofRegistrationv1_1 Conversions] As RegStart, 0 as Funded, 0 as Completed From [All Markets];

Copy this into your mdb and run it to get the gist of what I mean.

David

Hey,

Thanks very much for that. I've been in Berlin, hence the late reply.

I tried the union stuff and it worked, however, when I integrated this with mt query it basically started running and never finished.

I have been reading up about normalisation here http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=88 and I remember it from school somehow.

Basically if I was to keep these fields from the "All Markets" table:

{Client Name, Media Plan, Site, File Name, Stats Date, Impressions, Clicks, PMC and all the Started Confirmed and Completed tags}

Then I would need a Client table, Media Plan table, Site Table, File Name Table, Started Table, Completed Table and Funded Table.

I would do the same for the bookings table except link the relevant info to the tables I had already created.

Then all my data like impressions clicks date and Started cOmpleted funded would be in a seperate table.

Is it something along those lines?

If not, then my boss is asking how much you would do the work for?

cheers