Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Function - Group By

Status
Not open for further replies.
sugherello

sugherello

Technical User
Jul 29, 2005
36
IT
Hi all,
thi is my first access function and It seems to work but I need your precious support!

I use my function 'Provacondotte' into a query 'A' whitout problem...now if I call my query 'A' into query 'B' and I use group by (into query 'B') tab my function function doesn't work in query 'A'...why!

this is my code:

Option Explicit

Dim avaloredt As Long
Dim wtim As String
'Dim avaloredt As Date
Function Provacondotte(Azione As Variant, Dataaz As Variant) As Long


If Azione = "'status' changed from 'Active' to 'Waiting for customer'" Then avaloredt = Dataaz
If Azione = "'status' changed from 'Waiting for customer' to 'Active'" Then Provacondotte = avaloredt
If Azione = "'status' changed from 'Customer Action Ready' to 'Active'" Then Provacondotte = avaloredt

If Azione = "'status' changed from 'Active' to 'Alert'" Then avaloredt = Dataaz
If Azione = "'status' changed from 'Alert' to 'Active'" Then Provacondotte = avaloredt

If Azione = "'status' changed from 'Waiting for customer' to 'Active'" Then Provacondotte = avaloredt
If Azione = "'status' changed from 'Customer Action Ready' to 'Active'" Then Provacondotte = avaloredt


If Azione = "'status' changed from 'Active' to 'Escalated 3d party'" Then avaloredt = Dataaz
If Azione = "'status' changed from 'Escalated 3d party' to 'Active'" Then Provacondotte = avaloredt

If Azione = "'status' changed from 'Active' to 'Closed'" Then avaloredt = Dataaz
If Azione = "'status' changed from 'Closed' to 'Active'" Then Provacondotte = avaloredt
If Azione = "'status' changed from 'Closed' to 'Closed satisfaction verified'" Then Provacondotte = avaloredt

End Function
 
Sort by date Sort by votes
Two things jump out at me. The first is you don't seem to be setting avaloredt anywhere. If you are, you should go ahead and make it a global explicitly (global keyword instead of dim).

Secondly, using a Select Case statement is a much better way to go... I changed your first two If statements to one Select case statement so you could get a feel for it. You should read the help topic. You can use a list in the case seperated by commas.

Code: 
Select Case Azione

Case "'status' changed from 'Active' to 'Waiting for customer'" 
      avaloredt = Dataaz
Case "'status' changed from 'Waiting for customer' to 'Active'" 
     Provacondotte = avaloredt
End Select

Beyond that, can you post the SQL for your queries and be more specific on what the error and problem is?
 
Upvote 0 Downvote
Hi Iamed,

first of all thank you for your attention.

My problem is:
This is my first query:

------
SELECT USD_ITIL_call_req.ref_num, Provacondotte(USD_ITIL_act_log!action_desc,USD_ITIL_act_log!system_time) AS Espr1, USD_ITIL_act_log.system_time, USD_ITIL_act_log.persid, USD_ITIL_call_req.open_date, USD_ITIL_call_req.status, USD_ITIL_act_log.type, USD_ITIL_act_log.action_desc, PDM_Abs2Work(USD_ITIL_call_req!open_date,USD_ITIL_call_req!resolve_date,"Mon - Fri { 8:00 am - 7:00 pm }")\60\60 AS ResolvedDate, PDM_Abs2Work(USD_ITIL_call_req!open_date,USD_ITIL_call_req!close_date,"Mon - Fri { 8:00 am - 7:00 pm }")\60\60 AS ClosedDate
FROM USD_ITIL_act_log INNER JOIN USD_ITIL_call_req ON USD_ITIL_act_log.call_req_id = USD_ITIL_call_req.persid
WHERE (((USD_ITIL_call_req.open_date)>CvrtToUnixTime('01/03/2009 00.00.01') And (USD_ITIL_call_req.open_date)<CvrtToUnixTime('01/04/2009 00.00.01')) AND ((USD_ITIL_call_req.status)="CSV") AND ((USD_ITIL_act_log.type)="ST" Or (USD_ITIL_act_log.type)="CL") AND ((USD_ITIL_act_log.action_desc) Like "*'Waiting for customer'*" Or (USD_ITIL_act_log.action_desc) Like "*'alert'*" Or (USD_ITIL_act_log.action_desc) Like "*'closed*'*" Or (USD_ITIL_act_log.action_desc) Like "*'escalated*'*" Or (USD_ITIL_act_log.action_desc) Like "*'Customer act*'*"))
ORDER BY USD_ITIL_call_req.ref_num, USD_ITIL_act_log.persid;
------------

this is my second query:

SELECT ciao.ref_num, Calctot(ciao!type,ciao!Espr1,ciao!ClosedDate) AS Espr2, ciao.type, ciao.ResolvedDate, ciao.ClosedDate
FROM ciao;

Now I try to suu the result of second query I receive wrong value? Why?

Thank you
 
Upvote 0 Downvote
Is Espr1 in the first query correct?

What is the code for Calctot?

What are espr1 and ClosedDate for the record (row) that Espr2 is wrong (assuming that is the problem)?
 
Upvote 0 Downvote
Hi,
Espr1 in the first query seems correct because it shows correct value...but if I sum that vale into second query it doesn't work!

This is calcot code:

Option Explicit

Dim avaloredt As Long
Dim wtim As String
Dim valorenum As Integer
Dim total As Integer
Dim temp As Integer
Dim temp1 As Integer


'Dim avaloredt As Date
Function Provacondotte(Azione As Variant, Dataaz As Variant) As Long
Dim worktime As String

worktime = "Mon - Fri { 8:00 am - 7:00 pm }"
If Azione = "'status' changed from 'Active' to 'Waiting for customer'" Then
avaloredt = Dataaz
Provacondotte = 0
End If
If Azione = "'status' changed from 'Waiting for customer' to 'Active'" Then
'Provacondotte = avaloredt
Provacondotte = PDM_Abs2Work(avaloredt, Dataaz, worktime) \ 60 \ 60
End If
If Azione = "'status' changed from 'Customer Action Ready' to 'Active'" Then
'Provacondotte = avaloredt
Provacondotte = PDM_Abs2Work(avaloredt, Dataaz, worktime) \ 60 \ 60
End If

If Azione = "'status' changed from 'Active' to 'Alert'" Then
avaloredt = Dataaz
Provacondotte = 0
End If
If Azione = "'status' changed from 'Alert' to 'Active'" Then
'Provacondotte = avaloredt
Provacondotte = PDM_Abs2Work(avaloredt, Dataaz, worktime) \ 60 \ 60
End If

If Azione = "'status' changed from 'Waiting for customer' to 'Active'" Then
Provacondotte = PDM_Abs2Work(avaloredt, Dataaz, worktime) \ 60 \ 60
End If

If Azione = "'status' changed from 'Customer Action Ready' to 'Active'" Then
'Provacondotte = avaloredt
Provacondotte = PDM_Abs2Work(avaloredt, Dataaz, worktime) \ 60 \ 60
End If


If Azione = "'status' changed from 'Active' to 'Escalated 3d party'" Then
avaloredt = Dataaz
Provacondotte = 0
End If
If Azione = "'status' changed from 'Escalated 3d party' to 'Active'" Then
'Provacondotte = avaloredt
Provacondotte = PDM_Abs2Work(avaloredt, Dataaz, worktime) \ 60 \ 60
End If

If Azione = "'status' changed from 'Active' to 'Closed'" Then
avaloredt = Dataaz
Provacondotte = 0
End If
If Azione = "'status' changed from 'Closed' to 'Active'" Then
' Provacondotte = avaloredt
Provacondotte = PDM_Abs2Work(avaloredt, Dataaz, worktime) \ 60 \ 60
End If
If Azione = "'status' changed from 'Closed' to 'Closed satisfaction verified'" Then
'Provacondotte = avaloredt
Provacondotte = PDM_Abs2Work(avaloredt, Dataaz, worktime) \ 60 \ 60
End If

End Function


Function Provacondottewt(Azionew As Variant) As String


If Azionew = "'status' changed from 'Active' to 'Waiting for customer'" Then wtim = "WFU"
If Azionew = "'status' changed from 'Waiting for customer' to 'Active'" Then Provacondottewt = wtim
If Azionew = "'status' changed from 'Customer Action Ready' to 'Active'" Then Provacondottewt = wtim


If Azionew = "'status' changed from 'Active' to 'Alert'" Then wtim = "ALERT"
If Azionew = "'status' changed from 'Alert' to 'Active'" Then Provacondottewt = wtim

If Azionew = "'status' changed from 'Waiting for customer' to 'Active'" Then Provacondottewt = wtim
If Azionew = "'status' changed from 'Customer Action Ready' to 'Active'" Then Provacondottewt = wtim


If Azionew = "'status' changed from 'Active' to 'Escalated 3d party'" Then wtim = "ESC3D"
If Azionew = "'status' changed from 'Escalated 3d party' to 'Active'" Then Provacondottewt = wtim

If Azionew = "'status' changed from 'Active' to 'Closed'" Then wtim = "SATISF"
If Azionew = "'status' changed from 'Closed' to 'Active'" Then Provacondottewt = wtim
If Azionew = "'status' changed from 'Closed' to 'Closed satisfaction verified'" Then Provacondottewt = wtim



End Function

Function GestTempi(inizi As Variant) As Long
valorenum = inizi
GestTempi = valorenum

End Function

Function Calctot(az As Variant, wt As Variant, clt As Variant) As Long

'MsgBox (temp)
If az = "ST" Then
temp = temp1 + wt
temp1 = temp
'MsgBox (temp)
End If
If az = "CL" Then
total = temp + wt
Calctot = total
'MsgBox ("CL")
'MsgBox (total)
temp = 0
temp1 = 0
total = 0
End If


End Function
where is my error? :(

Thank youuuu!!
 
Upvote 0 Downvote
What are espr1 and ClosedDate for the record (row) that Espr2 is wrong (assuming that is the problem)?
Click to expand...

Also, what is the desired result and what result are you getting?
 
Upvote 0 Downvote
Your question is a good question?
I'd like to create a query to substitute any string value to numeric value, than I'd like to create a second query to sum numeric value from first query.

It's very hard!
 
Upvote 0 Downvote
You mean instead of

Calctot(ciao!type,ciao!Espr1,ciao!ClosedDate)

Sum (ciao!Espr1)


?
 
Upvote 0 Downvote
Hi Lameid,
I fixed my problem using a query to create a new table!

Thank you for your support.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bobbyer
  • Locked
  • Question
ms accesss option group on report
Replies
4
Views
144
bobbyer
bobbyer
AJAY0618
  • Locked
  • Question
How to pass a variable in VBA code by opening a form and storing the variable
Replies
3
Views
155
AJAY0618
AJAY0618
Moss100
  • Locked
  • Question
Hiding or resizing Access Application so my pop up form is all that is seen by the user
Replies
2
Views
125
jedraw
jedraw
qqp
  • Locked
  • Question
Recall last settings function 2
Replies
8
Views
192
qqp
qqp
Moss100
  • Locked
  • Question
Function to strip all other than numbers from a field 1
Replies
5
Views
135
Moss100
Moss100

Part and Inventory Search

Sponsor

Back
Top