G
Guest
How do I pass the result from a user-defined function into my append query?
thanks
thanks
Install the app
How to install the app on iOS
Follow along with the video below to see how to install our site as a web app on your home screen.
Note: This feature may not be available in some browsers.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
K
kingston via AccessMonster.com
You'd use it just like you would any other built-in function. Just make sure
that the function is Public.
that the function is Public.
G
Guest
I made it public but when I run the query it says it can't find the function.
D
Douglas J. Steele
Where is the function? It must be in a stand-alone module, not a Class
Module, nor a module associated with a form.
Module, nor a module associated with a form.
K
kingston via AccessMonster.com
Please post the function and the query if possible. Does the function work
as expected? In the results pane in the VB window (bottom), what happens
when you enter: ?FunctionName(Input)
as expected? In the results pane in the VB window (bottom), what happens
when you enter: ?FunctionName(Input)
G
Guest
It's in a module by itself and no form is included.
G
Guest
Public Function GetStatus(result)
Dim rec As Recordset
Dim db As Database
Set db = CurrentDb
Set rec = db.OpenRecordset("dbo_application")
Do Until rec.EOF
If rec![active_sw] = "C" Then
result = "CANCELLED"
rec.MoveNext
ElseIf rec![active_sw] = "W" Then
result = "WITHDREW"
rec.MoveNext
ElseIf rec![active_sw] = "A" And Not IsNull(rec![admit_dec_code]) Then
result = ""
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
rec![deferred_sw] = "A" Then
result = "ALTERNATE"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
rec![deferred_sw] = "D" Then
result = "DEFERRED"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
IsNull(rec![deferred_sw]) And Not IsNull(rec![referred_dept]) Then
result = "REFERRED"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
IsNull(rec![deferred_sw]) And IsNull(rec![referred_dept]) And
rec![complete_sw] = "I" Then
result = "INCOMPLETE"
rec.MoveNext
Else
End If
Loop
rec.Close
Set db = Nothing
End Function
Query--->status: GetStatus([result])
Dim rec As Recordset
Dim db As Database
Set db = CurrentDb
Set rec = db.OpenRecordset("dbo_application")
Do Until rec.EOF
If rec![active_sw] = "C" Then
result = "CANCELLED"
rec.MoveNext
ElseIf rec![active_sw] = "W" Then
result = "WITHDREW"
rec.MoveNext
ElseIf rec![active_sw] = "A" And Not IsNull(rec![admit_dec_code]) Then
result = ""
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
rec![deferred_sw] = "A" Then
result = "ALTERNATE"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
rec![deferred_sw] = "D" Then
result = "DEFERRED"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
IsNull(rec![deferred_sw]) And Not IsNull(rec![referred_dept]) Then
result = "REFERRED"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
IsNull(rec![deferred_sw]) And IsNull(rec![referred_dept]) And
rec![complete_sw] = "I" Then
result = "INCOMPLETE"
rec.MoveNext
Else
End If
Loop
rec.Close
Set db = Nothing
End Function
Query--->status: GetStatus([result])
D
Douglas J. Steele
First error is that you don't set the value of GetStatus anywhere (which is
how you get a function to return a value).
You should probably change your function to:
Public Function GetStatus() As String
Dim rec As DAO.Recordset
Dim db As DAO.Database
Dim result As String
Set db = CurrentDb
Set rec = db.OpenRecordset("dbo_application")
Do Until rec.EOF
If rec![active_sw] = "C" Then
result = "CANCELLED"
rec.MoveNext
ElseIf rec![active_sw] = "W" Then
result = "WITHDREW"
rec.MoveNext
ElseIf rec![active_sw] = "A" And Not IsNull(rec![admit_dec_code]) Then
result = ""
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
rec![deferred_sw] = "A" Then
result = "ALTERNATE"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
rec![deferred_sw] = "D" Then
result = "DEFERRED"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
IsNull(rec![deferred_sw]) And Not IsNull(rec![referred_dept]) Then
result = "REFERRED"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
IsNull(rec![deferred_sw]) And IsNull(rec![referred_dept]) And
rec![complete_sw] = "I" Then
result = "INCOMPLETE"
rec.MoveNext
Else
End If
Loop
rec.Close
Set db = Nothing
GetStatus = result
End Function
I won't pretend to understand what your function's supposed to be doing. If
you're using it in a query, it's going to loop through the recordset for
each row in your query, and always return the same value: whatever result
was set to for the last record in the recordset (and since you've specified
no ORDER BY clause, you have no way of knowing which entry in the table will
be the last record in the recordset).
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
how you get a function to return a value).
You should probably change your function to:
Public Function GetStatus() As String
Dim rec As DAO.Recordset
Dim db As DAO.Database
Dim result As String
Set db = CurrentDb
Set rec = db.OpenRecordset("dbo_application")
Do Until rec.EOF
If rec![active_sw] = "C" Then
result = "CANCELLED"
rec.MoveNext
ElseIf rec![active_sw] = "W" Then
result = "WITHDREW"
rec.MoveNext
ElseIf rec![active_sw] = "A" And Not IsNull(rec![admit_dec_code]) Then
result = ""
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
rec![deferred_sw] = "A" Then
result = "ALTERNATE"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
rec![deferred_sw] = "D" Then
result = "DEFERRED"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
IsNull(rec![deferred_sw]) And Not IsNull(rec![referred_dept]) Then
result = "REFERRED"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
IsNull(rec![deferred_sw]) And IsNull(rec![referred_dept]) And
rec![complete_sw] = "I" Then
result = "INCOMPLETE"
rec.MoveNext
Else
End If
Loop
rec.Close
Set db = Nothing
GetStatus = result
End Function
I won't pretend to understand what your function's supposed to be doing. If
you're using it in a query, it's going to loop through the recordset for
each row in your query, and always return the same value: whatever result
was set to for the last record in the recordset (and since you've specified
no ORDER BY clause, you have no way of knowing which entry in the table will
be the last record in the recordset).
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Mike said:Public Function GetStatus(result)
Dim rec As Recordset
Dim db As Database
Set db = CurrentDb
Set rec = db.OpenRecordset("dbo_application")
Do Until rec.EOF
If rec![active_sw] = "C" Then
result = "CANCELLED"
rec.MoveNext
ElseIf rec![active_sw] = "W" Then
result = "WITHDREW"
rec.MoveNext
ElseIf rec![active_sw] = "A" And Not IsNull(rec![admit_dec_code]) Then
result = ""
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
rec![deferred_sw] = "A" Then
result = "ALTERNATE"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
rec![deferred_sw] = "D" Then
result = "DEFERRED"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
IsNull(rec![deferred_sw]) And Not IsNull(rec![referred_dept]) Then
result = "REFERRED"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
IsNull(rec![deferred_sw]) And IsNull(rec![referred_dept]) And
rec![complete_sw] = "I" Then
result = "INCOMPLETE"
rec.MoveNext
Else
End If
Loop
rec.Close
Set db = Nothing
End Function
Query--->status: GetStatus([result])
kingston via AccessMonster.com said:Please post the function and the query if possible. Does the function
work
as expected? In the results pane in the VB window (bottom), what happens
when you enter: ?FunctionName(Input)
K
kingston via AccessMonster.com
Actually, functions work more like this:
Public Function GetStatus(Input) as Integer
If Input = "xyz" then
GetStatus = 123
Else
GetStatus = 456
End If
End Function
Then, when you GetStatus in the results pane, or a query, or a form:
GetStatus("abc") will give you 456
GetStatus("xyz") = 123
What you've got is a procedure that doesn't return anything (a subroutine
rather than a function). So, GetStatus(...) is not equal to anything, and
calling it won't return a result; it will do something but will not give a
value back to the call statement. What is the logic behind trying to run it
in a query? Are you trying to do something recursively or iteratively?
Public Function GetStatus(Input) as Integer
If Input = "xyz" then
GetStatus = 123
Else
GetStatus = 456
End If
End Function
Then, when you GetStatus in the results pane, or a query, or a form:
GetStatus("abc") will give you 456
GetStatus("xyz") = 123
What you've got is a procedure that doesn't return anything (a subroutine
rather than a function). So, GetStatus(...) is not equal to anything, and
calling it won't return a result; it will do something but will not give a
value back to the call statement. What is the logic behind trying to run it
in a query? Are you trying to do something recursively or iteratively?
Public Function GetStatus(result)
Dim rec As Recordset
Dim db As Database
Set db = CurrentDb
Set rec = db.OpenRecordset("dbo_application")
Do Until rec.EOF
If rec![active_sw] = "C" Then
result = "CANCELLED"
rec.MoveNext
ElseIf rec![active_sw] = "W" Then
result = "WITHDREW"
rec.MoveNext
ElseIf rec![active_sw] = "A" And Not IsNull(rec![admit_dec_code]) Then
result = ""
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
rec![deferred_sw] = "A" Then
result = "ALTERNATE"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
rec![deferred_sw] = "D" Then
result = "DEFERRED"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
IsNull(rec![deferred_sw]) And Not IsNull(rec![referred_dept]) Then
result = "REFERRED"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
IsNull(rec![deferred_sw]) And IsNull(rec![referred_dept]) And
rec![complete_sw] = "I" Then
result = "INCOMPLETE"
rec.MoveNext
Else
End If
Loop
rec.Close
Set db = Nothing
End Function
Query--->status: GetStatus([result])
[quoted text clipped - 8 lines]Please post the function and the query if possible. Does the function work
as expected? In the results pane in the VB window (bottom), what happens
G
Guest
I'm trying to return a value back and append it to a table. I tried using an
IIf statement but it was to long and messy. I thought I could write a
functiont that would pass the result back to the query and append it to the
table but I don't want the same value for each record in my table.
IIf statement but it was to long and messy. I thought I could write a
functiont that would pass the result back to the query and append it to the
table but I don't want the same value for each record in my table.
kingston via AccessMonster.com said:Actually, functions work more like this:
Public Function GetStatus(Input) as Integer
If Input = "xyz" then
GetStatus = 123
Else
GetStatus = 456
End If
End Function
Then, when you GetStatus in the results pane, or a query, or a form:
GetStatus("abc") will give you 456
GetStatus("xyz") = 123
What you've got is a procedure that doesn't return anything (a subroutine
rather than a function). So, GetStatus(...) is not equal to anything, and
calling it won't return a result; it will do something but will not give a
value back to the call statement. What is the logic behind trying to run it
in a query? Are you trying to do something recursively or iteratively?
Public Function GetStatus(result)
Dim rec As Recordset
Dim db As Database
Set db = CurrentDb
Set rec = db.OpenRecordset("dbo_application")
Do Until rec.EOF
If rec![active_sw] = "C" Then
result = "CANCELLED"
rec.MoveNext
ElseIf rec![active_sw] = "W" Then
result = "WITHDREW"
rec.MoveNext
ElseIf rec![active_sw] = "A" And Not IsNull(rec![admit_dec_code]) Then
result = ""
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
rec![deferred_sw] = "A" Then
result = "ALTERNATE"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
rec![deferred_sw] = "D" Then
result = "DEFERRED"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
IsNull(rec![deferred_sw]) And Not IsNull(rec![referred_dept]) Then
result = "REFERRED"
rec.MoveNext
ElseIf rec![active_sw] = "A" And IsNull(rec![admit_dec_code]) And
IsNull(rec![deferred_sw]) And IsNull(rec![referred_dept]) And
rec![complete_sw] = "I" Then
result = "INCOMPLETE"
rec.MoveNext
Else
End If
Loop
rec.Close
Set db = Nothing
End Function
Query--->status: GetStatus([result])
[quoted text clipped - 8 lines]Please post the function and the query if possible. Does the function work
as expected? In the results pane in the VB window (bottom), what happensthanks
K
kingston via AccessMonster.com
That sounds perfectly feasible. You won't necessarily get the same value for
each record in the table. What is the calculation you want to perform? What
is the value you want to set in the table? Actually, it seems like you were
on the right track with an embedded IIF statement in the query. Let's say
your table has 4 fields, and you want the 4th field to be the result of the
first two based on the state of the 3rd one. Then, you'd have an update
query where the 4th field would have an Update To: block that looks like
IIF([Field3]="Canceled",[Field1]-10,IIF([Field3]="Active",[Field1]*[Field2],-
1)
So the default value is -1. If [Field3]="Active" then [Field4]=[Field1]*
[Field2] (won't yield the same value for each record unless [Field1] and
[Field2] coincidentally result in the same product). Last, if [Field3]
="Canceled" [Field4]=[Field1]-10 (again, not the same value for each record
unless [Field1] is the same for every record). If the IIF statement gets too
long, paste it into a text editor and construct it correctly first. Then
paste it into the query. HTH
each record in the table. What is the calculation you want to perform? What
is the value you want to set in the table? Actually, it seems like you were
on the right track with an embedded IIF statement in the query. Let's say
your table has 4 fields, and you want the 4th field to be the result of the
first two based on the state of the 3rd one. Then, you'd have an update
query where the 4th field would have an Update To: block that looks like
IIF([Field3]="Canceled",[Field1]-10,IIF([Field3]="Active",[Field1]*[Field2],-
1)
So the default value is -1. If [Field3]="Active" then [Field4]=[Field1]*
[Field2] (won't yield the same value for each record unless [Field1] and
[Field2] coincidentally result in the same product). Last, if [Field3]
="Canceled" [Field4]=[Field1]-10 (again, not the same value for each record
unless [Field1] is the same for every record). If the IIF statement gets too
long, paste it into a text editor and construct it correctly first. Then
paste it into the query. HTH
I'm trying to return a value back and append it to a table. I tried using an
IIf statement but it was to long and messy. I thought I could write a
functiont that would pass the result back to the query and append it to the
table but I don't want the same value for each record in my table.
[quoted text clipped - 70 lines]Actually, functions work more like this:
G
Guest
I went ahead with the IIF statement and it works:
status:
IIf([active_sw]="C","CANCELLED",IIf([active_sw]="W","WITHDREW",IIf([active_sw]="A"
And Not IsNull([dbo_application.admit_dec_code]),"",IIf([active_sw]="A" And
IsNull([dbo_application.admit_dec_code]) And
[deferred_sw]="A","ALTERNATE",IIf([active_sw]="A" And
IsNull([dbo_application.admit_dec_code]) And
[deferred_sw]="D","DEFERRED",IIf([active_sw]="A" And
IsNull([dbo_application.admit_dec_code]) And IsNull([deferred_sw]) And Not
IsNull([referred_dept]),"REFERRED",IIf([active_sw]="A" And
IsNull([dbo_application.admit_dec_code]) And IsNull([deferred_sw]) And
IsNull([referred_dept]) And [complete_sw]="I","INCOMPLETE","")))))))
Mike
status:
IIf([active_sw]="C","CANCELLED",IIf([active_sw]="W","WITHDREW",IIf([active_sw]="A"
And Not IsNull([dbo_application.admit_dec_code]),"",IIf([active_sw]="A" And
IsNull([dbo_application.admit_dec_code]) And
[deferred_sw]="A","ALTERNATE",IIf([active_sw]="A" And
IsNull([dbo_application.admit_dec_code]) And
[deferred_sw]="D","DEFERRED",IIf([active_sw]="A" And
IsNull([dbo_application.admit_dec_code]) And IsNull([deferred_sw]) And Not
IsNull([referred_dept]),"REFERRED",IIf([active_sw]="A" And
IsNull([dbo_application.admit_dec_code]) And IsNull([deferred_sw]) And
IsNull([referred_dept]) And [complete_sw]="I","INCOMPLETE","")))))))
Mike
kingston via AccessMonster.com said:That sounds perfectly feasible. You won't necessarily get the same value for
each record in the table. What is the calculation you want to perform? What
is the value you want to set in the table? Actually, it seems like you were
on the right track with an embedded IIF statement in the query. Let's say
your table has 4 fields, and you want the 4th field to be the result of the
first two based on the state of the 3rd one. Then, you'd have an update
query where the 4th field would have an Update To: block that looks like
IIF([Field3]="Canceled",[Field1]-10,IIF([Field3]="Active",[Field1]*[Field2],-
1)
So the default value is -1. If [Field3]="Active" then [Field4]=[Field1]*
[Field2] (won't yield the same value for each record unless [Field1] and
[Field2] coincidentally result in the same product). Last, if [Field3]
="Canceled" [Field4]=[Field1]-10 (again, not the same value for each record
unless [Field1] is the same for every record). If the IIF statement gets too
long, paste it into a text editor and construct it correctly first. Then
paste it into the query. HTH
I'm trying to return a value back and append it to a table. I tried using an
IIf statement but it was to long and messy. I thought I could write a
functiont that would pass the result back to the query and append it to the
table but I don't want the same value for each record in my table.
[quoted text clipped - 70 lines]Actually, functions work more like this:thanks
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.