How can I call a function in one querie?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a function in a module in access 2002. How I can call this
function in one querie? Thank you
 
I created a function called Get_Age which accepts the DOB field as a
parameter to return a persons age. In a query simply type it in with a column
identifier.

HowOld: Get_Age([DOB])
 
Tanks Dennis,
My function:
Public Function ano_anterior(strData As Date)
Dim dataB As Date 'data base para calcular a diferença de dias
Dim dataC As Date 'data de hoje
Dim dataA As Date 'dia 31-12 do ano anterior
Dim DIAS As Double 'nº de dias de diferença entre datas
Dim strData As Date
Dim i As Integer
dataB = #12/31/2004#
dataC = Date
DIAS = DateDiff("y", dataB, dataC)
Do While DIAS > 365 'ciclo para calcular a diferença de dias para dia
31-12 do ano anterior
DIAS = DIAS - 365
Loop
dataA = dataC - DIAS
dataB = dataA
For i = 0 To 1 Step 0 'ciclo para adicionar dias para evitar o erro dos
anos bicestos
DIAS = DateDiff("yyyy", dataB, dataA)
If DIAS = 0 Then
dataA = dataA + 1
Else
dataA = dataA - 1
Exit For
End If
Next
'MsgBox (dataA)
strData = dataA
End Function

it always returns 31-12 from the previous year to that we are. I already
placed ano_findo: ano_anterior([strData])on the querie but gives error
Enter Parameters Value [strData]


"Dennis" escreveu:
I created a function called Get_Age which accepts the DOB field as a
parameter to return a persons age. In a query simply type it in with a column
identifier.

HowOld: Get_Age([DOB])

Fernanda Pires said:
I created a function in a module in access 2002. How I can call this
function in one querie? Thank you
Click to expand...
Click to expand...
 
If you are calling it from a query, where is the field strData defined ?
I noticed in your function you are accepting the value strData as the
parameter for your function but then you are redefining it in the 5th line.
The date value should be a field from the table that your query is based on.

Fernanda Pires said:
Tanks Dennis,
My function:
Public Function ano_anterior(strData As Date)
Dim dataB As Date 'data base para calcular a diferença de dias
Dim dataC As Date 'data de hoje
Dim dataA As Date 'dia 31-12 do ano anterior
Dim DIAS As Double 'nº de dias de diferença entre datas
Dim strData As Date
Dim i As Integer
dataB = #12/31/2004#
dataC = Date
DIAS = DateDiff("y", dataB, dataC)
Do While DIAS > 365 'ciclo para calcular a diferença de dias para dia
31-12 do ano anterior
DIAS = DIAS - 365
Loop
dataA = dataC - DIAS
dataB = dataA
For i = 0 To 1 Step 0 'ciclo para adicionar dias para evitar o erro dos
anos bicestos
DIAS = DateDiff("yyyy", dataB, dataA)
If DIAS = 0 Then
dataA = dataA + 1
Else
dataA = dataA - 1
Exit For
End If
Next
'MsgBox (dataA)
strData = dataA
End Function

it always returns 31-12 from the previous year to that we are. I already
placed ano_findo: ano_anterior([strData])on the querie but gives error
Enter Parameters Value [strData]


"Dennis" escreveu:
I created a function called Get_Age which accepts the DOB field as a
parameter to return a persons age. In a query simply type it in with a column
identifier.

HowOld: Get_Age([DOB])

Fernanda Pires said:
I created a function in a module in access 2002. How I can call this
function in one querie? Thank you
Click to expand...
Click to expand...
Click to expand...
 
Tanks Dennis for the patience,

I modified the function and I created a public variable
Public ano_findo As Date

Public Function ano_anterior(dataA As Date)
Dim dataB As Date 'data base para calcular a diferença de dias
Dim dataC As Date 'data de hoje
'Dim dataA As Date 'dia 31-12 do ano anterior
Dim DIAS As Double 'nº de dias de diferença entre datas
Dim i As Integer
dataB = #12/31/2004#
dataC = Date
DIAS = DateDiff("y", dataB, dataC)
Do While DIAS > 365 'ciclo para calcular a diferença de dias para dia
31-12 do ano anterior
DIAS = DIAS - 365
Loop
dataA = dataC - DIAS
dataB = dataA
For i = 0 To 1 Step 0 'ciclo para adicionar dias para evitar o erro dos
anos bicestos
DIAS = DateDiff("yyyy", dataB, dataA)
If DIAS = 0 Then
dataA = dataA + 1
Else
dataA = dataA - 1
Exit For
End If
Next
ano_findo = dataA
'MsgBox (ano_findo)

in querie I call the function a:ano_anterior(Now())
if it will have msgbox active I see the result but the column a: she is empty



"Dennis" escreveu:
If you are calling it from a query, where is the field strData defined ?
I noticed in your function you are accepting the value strData as the
parameter for your function but then you are redefining it in the 5th line.
The date value should be a field from the table that your query is based on.

Fernanda Pires said:
Tanks Dennis,
My function:
Public Function ano_anterior(strData As Date)
Dim dataB As Date 'data base para calcular a diferença de dias
Dim dataC As Date 'data de hoje
Dim dataA As Date 'dia 31-12 do ano anterior
Dim DIAS As Double 'nº de dias de diferença entre datas
Dim strData As Date
Dim i As Integer
dataB = #12/31/2004#
dataC = Date
DIAS = DateDiff("y", dataB, dataC)
Do While DIAS > 365 'ciclo para calcular a diferença de dias para dia
31-12 do ano anterior
DIAS = DIAS - 365
Loop
dataA = dataC - DIAS
dataB = dataA
For i = 0 To 1 Step 0 'ciclo para adicionar dias para evitar o erro dos
anos bicestos
DIAS = DateDiff("yyyy", dataB, dataA)
If DIAS = 0 Then
dataA = dataA + 1
Else
dataA = dataA - 1
Exit For
End If
Next
'MsgBox (dataA)
strData = dataA
End Function

it always returns 31-12 from the previous year to that we are. I already
placed ano_findo: ano_anterior([strData])on the querie but gives error
Enter Parameters Value [strData]


"Dennis" escreveu:
I created a function called Get_Age which accepts the DOB field as a
parameter to return a persons age. In a query simply type it in with a column
identifier.

HowOld: Get_Age([DOB])

:

I created a function in a module in access 2002. How I can call this
function in one querie? Thank you
Click to expand...
Click to expand...
Click to expand...
 
To return a variable or calculation from a function, you must give it the
same name as your function. Therefore the last line of your function should be
ano_anterior = dataA
instead of
ano_findo = dataA

Fernanda Pires said:
Tanks Dennis for the patience,

I modified the function and I created a public variable
Public ano_findo As Date

Public Function ano_anterior(dataA As Date)
Dim dataB As Date 'data base para calcular a diferença de dias
Dim dataC As Date 'data de hoje
'Dim dataA As Date 'dia 31-12 do ano anterior
Dim DIAS As Double 'nº de dias de diferença entre datas
Dim i As Integer
dataB = #12/31/2004#
dataC = Date
DIAS = DateDiff("y", dataB, dataC)
Do While DIAS > 365 'ciclo para calcular a diferença de dias para dia
31-12 do ano anterior
DIAS = DIAS - 365
Loop
dataA = dataC - DIAS
dataB = dataA
For i = 0 To 1 Step 0 'ciclo para adicionar dias para evitar o erro dos
anos bicestos
DIAS = DateDiff("yyyy", dataB, dataA)
If DIAS = 0 Then
dataA = dataA + 1
Else
dataA = dataA - 1
Exit For
End If
Next
ano_findo = dataA
'MsgBox (ano_findo)

in querie I call the function a:ano_anterior(Now())
if it will have msgbox active I see the result but the column a: she is empty



"Dennis" escreveu:
If you are calling it from a query, where is the field strData defined ?
I noticed in your function you are accepting the value strData as the
parameter for your function but then you are redefining it in the 5th line.
The date value should be a field from the table that your query is based on.

Fernanda Pires said:
Tanks Dennis,
My function:
Public Function ano_anterior(strData As Date)
Dim dataB As Date 'data base para calcular a diferença de dias
Dim dataC As Date 'data de hoje
Dim dataA As Date 'dia 31-12 do ano anterior
Dim DIAS As Double 'nº de dias de diferença entre datas
Dim strData As Date
Dim i As Integer
dataB = #12/31/2004#
dataC = Date
DIAS = DateDiff("y", dataB, dataC)
Do While DIAS > 365 'ciclo para calcular a diferença de dias para dia
31-12 do ano anterior
DIAS = DIAS - 365
Loop
dataA = dataC - DIAS
dataB = dataA
For i = 0 To 1 Step 0 'ciclo para adicionar dias para evitar o erro dos
anos bicestos
DIAS = DateDiff("yyyy", dataB, dataA)
If DIAS = 0 Then
dataA = dataA + 1
Else
dataA = dataA - 1
Exit For
End If
Next
'MsgBox (dataA)
strData = dataA
End Function

it always returns 31-12 from the previous year to that we are. I already
placed ano_findo: ano_anterior([strData])on the querie but gives error
Enter Parameters Value [strData]


"Dennis" escreveu:

I created a function called Get_Age which accepts the DOB field as a
parameter to return a persons age. In a query simply type it in with a column
identifier.

HowOld: Get_Age([DOB])

:

I created a function in a module in access 2002. How I can call this
function in one querie? Thank you
Click to expand...
Click to expand...
Click to expand...
 
Dennis,
It worked. Who knows, knows.
Tanks and kisses.

"Dennis" escreveu:
To return a variable or calculation from a function, you must give it the
same name as your function. Therefore the last line of your function should be
ano_anterior = dataA
instead of
ano_findo = dataA

Fernanda Pires said:
Tanks Dennis for the patience,

I modified the function and I created a public variable
Public ano_findo As Date

Public Function ano_anterior(dataA As Date)
Dim dataB As Date 'data base para calcular a diferença de dias
Dim dataC As Date 'data de hoje
'Dim dataA As Date 'dia 31-12 do ano anterior
Dim DIAS As Double 'nº de dias de diferença entre datas
Dim i As Integer
dataB = #12/31/2004#
dataC = Date
DIAS = DateDiff("y", dataB, dataC)
Do While DIAS > 365 'ciclo para calcular a diferença de dias para dia
31-12 do ano anterior
DIAS = DIAS - 365
Loop
dataA = dataC - DIAS
dataB = dataA
For i = 0 To 1 Step 0 'ciclo para adicionar dias para evitar o erro dos
anos bicestos
DIAS = DateDiff("yyyy", dataB, dataA)
If DIAS = 0 Then
dataA = dataA + 1
Else
dataA = dataA - 1
Exit For
End If
Next
ano_findo = dataA
'MsgBox (ano_findo)

in querie I call the function a:ano_anterior(Now())
if it will have msgbox active I see the result but the column a: she is empty



"Dennis" escreveu:
If you are calling it from a query, where is the field strData defined ?
I noticed in your function you are accepting the value strData as the
parameter for your function but then you are redefining it in the 5th line.
The date value should be a field from the table that your query is based on.

:

Tanks Dennis,
My function:
Public Function ano_anterior(strData As Date)
Dim dataB As Date 'data base para calcular a diferença de dias
Dim dataC As Date 'data de hoje
Dim dataA As Date 'dia 31-12 do ano anterior
Dim DIAS As Double 'nº de dias de diferença entre datas
Dim strData As Date
Dim i As Integer
dataB = #12/31/2004#
dataC = Date
DIAS = DateDiff("y", dataB, dataC)
Do While DIAS > 365 'ciclo para calcular a diferença de dias para dia
31-12 do ano anterior
DIAS = DIAS - 365
Loop
dataA = dataC - DIAS
dataB = dataA
For i = 0 To 1 Step 0 'ciclo para adicionar dias para evitar o erro dos
anos bicestos
DIAS = DateDiff("yyyy", dataB, dataA)
If DIAS = 0 Then
dataA = dataA + 1
Else
dataA = dataA - 1
Exit For
End If
Next
'MsgBox (dataA)
strData = dataA
End Function

it always returns 31-12 from the previous year to that we are. I already
placed ano_findo: ano_anterior([strData])on the querie but gives error
Enter Parameters Value [strData]


"Dennis" escreveu:

I created a function called Get_Age which accepts the DOB field as a
parameter to return a persons age. In a query simply type it in with a column
identifier.

HowOld: Get_Age([DOB])

:

I created a function in a module in access 2002. How I can call this
function in one querie? Thank you
Click to expand...
Click to expand...
Click to expand...
 
Back
Top