query too complex - need help creating function

[folkie]

I'm kinda new at this and I've been trying a lot, but I can't seem to figure this out. I have a query which says it's too complex when I run it. I've tried many ways to divide it into smaller queries, but I still can't get the whole thing running.

I saw a post where someone else had a similar problem. A suggestion to that user was to use a function to determine the value as opposed to using "Iif" in the query. I tried to adapt my fields to the sample function in the reply, but I keep getting errors. Here's my sample (I've run this several times commenting and uncommenting lines):

Option Compare Database
'Option Explicit

Public Function AugDefComp(dcjulff, fm) As Long
Dim xValue As Long

'Dim [fy 2002 employees eff date] As Database
'Set [fy 2002 employees eff date] = CurrentDb()

If dcjulff < 600 Then
'If [dc jul-ff] < 600 Then
'If fm = "01" Then xValue = (3*federal) 'value=0
If fm = "01" Then xValue = (3 * ([fy 2002 employees eff date].federal)) ' external name not defined
' If fm = "01" Then xValue = (3 * [federal]) ' external name not defined
' If fm = "01" Then xValue = (3 * Me.federal)' not right
ElseIf fm >= "02" Then xValue = 0
End If

AugDefComp = xValue

End Function

In the query, I've created a field which is defined as "AugDefComp([dc jul-ff],[fm])."

The only time I don't get an error is the line, above, where the comment says I get a value of 0. However, the value should not be 0 (if I were coding this correct). Basically, it doesn't know that 'federal' is a field in a table called 'fy 2002 employees eff date' because I keep getting the message "extrernal name not defined" while it highlights the word "federal."

Thank you very much,

~Mark

ps: as i'm trying to learn more about Visual Basic, what is the name of the coding used in Access - Visual Basic, or Visual Basic for Applications, or VBA? any good books out there for whatever this coding is called?

 

[RichMorrison]

At first glance it looks like this:
1) you have defined a "database" variable
2) you have set the variable = CurrentDB()

That's fine.

Now you need to define and set a variable that represents a TABLE in your database.

HTH,
RichM

 

[cogent1]

How do you call this function? You are sending this function two arguments. So presumably you generate the values of the arguments by some code elsewhere.

Can you send the value of [federal] as a third argument? Or even run this function behind a form based on your query so you don't have to make a call to a standard module? I don't quite see why you're doing it this way....

 

[folkie]

Thanks for the replies, but I think I’m still lost. I don’t think I understand your questions and comments. Rich, I looked in help and found something like the following:

Dim dbs As Database
Set dbs = CurrentDb()
Dim tdf As TableDef
Set tdf = dbs.CreateTableDef("fy 2002 employees eff date")

Assuming that’s correct, I still can’t figure out how to “link” the field called “federal” to the table called “fy 2002 employees eff date."


In answer to cogent1’s question (if I understand it), I call this function from a query (a field defined as "AugDefComp([dc jul-ff],[fm])"). The values come from other real (from a table) or calculated fields (from a query). I don’t think I can do what you suggest in your 2nd paragraph and/or I don’t think I understand your 2nd paragraph.

Any more advice would be appreciated.

Thanks,

~Mark

 

[cogent1]

You seem to be approaching this problem from the wrong direction. First, you set a database variable. But you don't use it in your function, so this action is redundant. Second, you call the function with two arguments (the variables in brackets). Somewhere in your database you have to define these variables for each time you want to run the function. The function can't pick up the arguments from a query in the way you describe. A piece of code has to say (in effect) "run this query, get two values, execute the function using the values". The function manipulates the values(arguments) and returns a result (a long Integer in your case) to the code that calls the function. Then that code does something to the result to achieve the desired outcome.
This is a simplification of course, but substantially what happens.

I think you can get what you want by a more direct process. What is involved? What are dcjulff and fm? Are they in a table? What is AugDefComp? What is federal? Is it possible you can frame a query which incorporates all the fields you want to be involved in the calculation?
This looks like a quite simple matter to resolve, but I need to know when this function is run - on data entry, when something changes, once a week? And what you use to trigger the change - clicking a button, selecting a combo box value, changing a field on a form?

 

[RichMorrison]

I am assuming that
[fy 2002 employees eff date]
is the name of an existing table in your database.

I don't know where you found
<<
Dim dbs As Database
Set dbs = CurrentDb()
Dim tdf As TableDef
Set tdf = dbs.CreateTableDef("fy 2002 employees eff date")
>>
but that is not what you want if
[fy 2002 employees eff date]
is in fact a table.

You want:

Dim dbs As Database
'
Dim myRst As Recordset
' =========
'
Set dbs = CurrentDb()
Set myRst = dbs.OpenRecordset("fy 2002 employees eff date")

I am not positive about the syntax since I'm at home without Access installed.

Look in Access Help for "OpenRecordset" for a better example.

RichM

 

[raskew]

A couple of things:
(1) Using fields and variables that include spaces , e.g. [fy 2002 employees eff date] adds an unnecessary level of complexity. In the following example I've cleaned them up.
(2) Nothing in your code shows the action which should occur if dcjulff >= 600.

Public Function AugDefComp(dcjulff As Integer, fm As String) As Long
Dim db As DATABASE
Dim rs As Recordset
Dim xValue As Long
'
Set db = CurrentDb
Set rs = db.OpenRecordset("fy2022EmployeesEffDate")
'
xValue = IIf(dcjulff < 600, Switch(fm = "01", 3 * rs!federal, fm = "01", 0), 0)
'
AugDefComp = xValue
'
rs.Close
db.Close
Set db = Nothing
End Function

[This message has been edited by raskew (edited 06-01-2002).]

 

[folkie]

Thank you all for your suggestions. From some research, this is what I’ve come up with:

Public Function AugDefComp(dcjulff, fm) As Long
Dim xValue As Long
Dim db As DAO.Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Select federal, deffcomp From [fy 2002 employees eff date];")

If dcjulff < 600 Then
If fm = "01" Then
xValue = 3 * rs!federal * rs!deffcomp
ElseIf fm >= "02" Then
xValue = 0
Else
xValue = 0
End If
Else
xValue = 0
End If

AugDefComp = xValue
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Function

What’s happening is, for this test I’m running, there should be 2 records output from the query. For the first record, AugDefComp, should be 0 (because deffcomp is 0). However, I realised, after playing with the values in the table, the value for AugDefComp is the same for both records – whatever the value is for the 1st record, becomes the value for the 2nd record. However, that’s not what I’m trying to do.

Cogent1, one of the 2 variables in the function is defined in the query which runs this function. The other variable comes from another query (which is used in this query). I’m trying to create a value for AugDefComp because when I used similar (& correct) logic in the query to create a calculated field, it works, but I need to create 11 more similar, but not the same, fields for the 12 months of the year. When I do this (and more for other types of data), I get the message saying the query is too complex. That’s why I’m trying to do this using a function. The function will be run when a user runs a query – which would be periodically.

Raskew, when I use your code (Iif and Switch instead of if...then...else), I have the same problem as above. Both records show the same value for AugDefComp, but the 2 records should be different.

If you have anymore suggestions, I’d appreciate it.

Thanks,

~Mark

 

[cogent1]

OK. Ignoring the way you call the function for the moment..

Let's look at your logic. It seems to be too complex. Is it the case that if dcjulff is less than 600 and FM is "01", the xvalue must be set to 3*(rs!federal*rs!deffcomp). Any other condition means it must be set to 0. Is that right? If so, forget all the elseifs. the code should be

If dcjulff<600 AND FM="01"then
xvalue=3*(rs!Federal*rs!deffcomp)
Else:xvalue=0
Endif
This covers DCjulff being greater than 600, or FM being any other value, it doesn't have to be explicit.

Test for the values by stepping through the code and seeing how the values change as each line is executed. You can read the values by putting the cursor in the variable name, and it appears as a tooltip. What values are being sent to the function in the arguments? Are they what they should be?

And post the SQL for the query you use to call the function...

[This message has been edited by cogent1 (edited 06-04-2002).]

[This message has been edited by cogent1 (edited 06-04-2002).]

 

[folkie]

Thanks for the suggestion to step through the code. When I did, it shows the value of rs!federal and rs!deffcomp for the first record. I don't know how to step through the code to read other records, which is basically the problem the query is having with this function.

I took out some of the fields in the query to make it easier to look at, but here's the SQL for the query:

SELECT [fy 2002 expenditures eff date].pin,
[fy 2002 expenditures eff date].payroll,
[ytd 1 eff date].fm,
[deffcomp]*[federal]*2 AS [dc jul-ff],
IIf([dc jul-ff]<600,IIf([fm]='01',(([deffcomp])*[federal])*3,IIf([fm]>='02',0))) AS [dc aug-ff],
AugDefComp([dc jul-ff],[fm]) AS [new aug],
IIf(([dc jul-ff]+[dc aug-ff])<600,IIf([fm]<='02',(([deffcomp])*[federal])*2,IIf([fm]>='03',0))) AS [dc sep-ff]

FROM ([fy 2002 employees eff date]
INNER JOIN [fy 2002 expenditures eff date] ON [fy 2002 employees eff date].pin = [fy 2002 expenditures eff date].pin)
INNER JOIN [ytd 1 eff date] ON [fy 2002 employees eff date].pin = [ytd 1 eff date].pin

WHERE ((([fy 2002 expenditures eff date].payroll)=[MaxOfpayroll1]));

The field called [dc aug-ff] is correct. The function I'm trying to create (AugDefComp) should be giving me the same values as [dc aug-ff].

Ultimately, I'll need a similar (and bigger) field/function for each month of the year in this query.

Thanks,

~Mark

 

[cogent1]

Mark
Please explain how you call this function. By this I mean how you cause it to be executed. As far as I know this can't be done from a query.

Your function, as it stands, can only return one value because you open a table-type recordset containing the values you want to work on, but you don't loop through it. If you want to refer to all the records in your table, you have to step to each row in turn and reiterate the lines of code that do the business.

Also, as the function is currently framed, the values of dcjulff and fm would be the same for all rows in your table, because the arguments are fixed for the duration of the function.

It seems to me that you should be running this function by stepping through two recordsets, updating the first according to values in the second, instead of sending arguments to it.

Where is AugDefComp ultimately stored?

Would it be possible to email me with your database and sample data or is it confidential?