Adding a Field to a Function

[Bob V]

This function gives the ,father,mother.age,sex to a horse if he has no name
or if named shows his Name, I want to add one field to both Names the field
is called "Extra"
Thanks for any help ..................Bob

Function funGetHorseName(lngInvoiceID As Long, lngHorseID As Long) As String

Dim recHorseName As New ADODB.Recordset, strAge As String, strName As String

recHorseName.Open "SELECT * FROM tblInvoice WHERE InvoiceID=" _
& lngInvoiceID & " AND HorseID=" & lngHorseID,
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If recHorseName.EOF = True And recHorseName.BOF = True Then
Set recHorseName = Nothing
funGetHorseName = ""
Exit Function
End If

If IsNull(recHorseName.Fields("DateOfBirth")) Or
recHorseName.Fields("DateOfBirth") = "" Then
strAge = "0yo"
Else
strAge = funCalcAge(Format("01-Aug-" &
Year(recHorseName.Fields("DateOfBirth")), "dd-mmm-yyyy"), Format(Now(),
"dd-mmm-yyyy"), 1)
End If

strName = Nz(recHorseName.Fields("FatherName"), "") & "--" &
Nz(recHorseName.Fields("MotherName"), "") _
& "--" & strAge & "-" & Nz(recHorseName.Fields("Sex"), "")

Set recHorseName = Nothing
Debug.Print strName

funGetHorseName = strName

End Function

 

[Tom van Stiphout]

On Wed, 28 Nov 2007 19:45:56 +1300, "Bob V" <[email protected]>
wrote:

There are many things I don't like about this routine, but to answer
your direct question: change one of the last lines to:
strName = Nz(recHorseName.Fields("FatherName"), "") & "--" &
Nz(recHorseName.Fields("MotherName"), "") _
& "--" & strAge & "-" & Nz(recHorseName.Fields("Sex"), "") _
& "-" & Nz(recHorseName.Fields("Extra"), "")

-Tom.

 

[Bob V]

Thanks Tom, What if I create a query qryHorseExtra, 2 fields [HorseID]
(AutoNumber) and [Extra] (Text)
How do I filter the HorseID so as when I open the Invoice I will see what
that HorseID Extra field has in it?
Thanks for helping Bob

 

[Bob V]

Tom I would have put a text box on my Invoice form, How would I code/Filter
it so as it shows the same data that is in tblHorseInfo..............Thanks
Bob

Thanks Tom, What if I create a query qryHorseExtra, 2 fields [HorseID]
(AutoNumber) and [Extra] (Text)
How do I filter the HorseID so as when I open the Invoice I will see what
that HorseID Extra field has in it?
Thanks for helping Bob

There are many things I don't like about this routine, but to answer
your direct question: change one of the last lines to:
strName = Nz(recHorseName.Fields("FatherName"), "") & "--" &
Nz(recHorseName.Fields("MotherName"), "") _
& "--" & strAge & "-" & Nz(recHorseName.Fields("Sex"), "") _
& "-" & Nz(recHorseName.Fields("Extra"), "")

-Tom.

 

[Tom van Stiphout]

You bring the original query in design view, and add qryHorseExtra to
it, and create a join line between the two HorseID fields. Then drag
the Extra field to the grid below.

-Tom.

Thanks Tom, What if I create a query qryHorseExtra, 2 fields [HorseID]
(AutoNumber) and [Extra] (Text)
How do I filter the HorseID so as when I open the Invoice I will see what
that HorseID Extra field has in it?
Thanks for helping Bob

There are many things I don't like about this routine, but to answer
your direct question: change one of the last lines to:
strName = Nz(recHorseName.Fields("FatherName"), "") & "--" &
Nz(recHorseName.Fields("MotherName"), "") _
& "--" & strAge & "-" & Nz(recHorseName.Fields("Sex"), "") _
& "-" & Nz(recHorseName.Fields("Extra"), "")

-Tom.

 

[Bob V]

Thanks Tom, BRILLIANT!!!!

You bring the original query in design view, and add qryHorseExtra to
it, and create a join line between the two HorseID fields. Then drag
the Extra field to the grid below.

-Tom.

Thanks Tom, What if I create a query qryHorseExtra, 2 fields [HorseID]
(AutoNumber) and [Extra] (Text)
How do I filter the HorseID so as when I open the Invoice I will see what
that HorseID Extra field has in it?
Thanks for helping Bob

There are many things I don't like about this routine, but to answer
your direct question: change one of the last lines to:
strName = Nz(recHorseName.Fields("FatherName"), "") & "--" &
Nz(recHorseName.Fields("MotherName"), "") _
& "--" & strAge & "-" & Nz(recHorseName.Fields("Sex"), "") _
& "-" & Nz(recHorseName.Fields("Extra"), "")

-Tom.



This function gives the ,father,mother.age,sex to a horse if he has no
name
or if named shows his Name, I want to add one field to both Names the
field
is called "Extra"
Thanks for any help ..................Bob

Function funGetHorseName(lngInvoiceID As Long, lngHorseID As Long) As
String

Dim recHorseName As New ADODB.Recordset, strAge As String, strName As
String

recHorseName.Open "SELECT * FROM tblInvoice WHERE InvoiceID=" _
& lngInvoiceID & " AND HorseID=" & lngHorseID,
CurrentProject.Connection, adOpenDynamic, adLockOptimistic
If recHorseName.EOF = True And recHorseName.BOF = True Then
Set recHorseName = Nothing
funGetHorseName = ""
Exit Function
End If

If IsNull(recHorseName.Fields("DateOfBirth")) Or
recHorseName.Fields("DateOfBirth") = "" Then
strAge = "0yo"
Else
strAge = funCalcAge(Format("01-Aug-" &
Year(recHorseName.Fields("DateOfBirth")), "dd-mmm-yyyy"), Format(Now(),
"dd-mmm-yyyy"), 1)
End If

strName = Nz(recHorseName.Fields("FatherName"), "") & "--" &
Nz(recHorseName.Fields("MotherName"), "") _
& "--" & strAge & "-" & Nz(recHorseName.Fields("Sex"), "")

Set recHorseName = Nothing
Debug.Print strName

funGetHorseName = strName

End Function