Dear sweet friends,
I have been banging my head over this for a few hours now and I figured it was time to turn it over to the experts. I'm pretty new to SQL and I'm sure I'm doing something very silly. What's going on here is I have a subform (AnimalDetails) that gets used in a few different forms, so I modify the recordsource as needed in a Load event in the master form. For example, in a form to enter data on a procedure performed on an animal this subform's function is to display basic data about the animal (this data comes from AnimalTable). The animal is chosen based on a combobox (cboAnimalID) on the form as you will see in the code below. I want to add a field in the subform (and call it AgeC) that calculates age by using DateDiff then rounding, and in addition give it a statement of "missing birthdate" to put in there if there is no birthdate to calculate with.
Here's where I am:
Code:
Private Sub Form_Load()
Dim strSql As String
strSql = "SELECT AnimalTable.AnimalAutoID, AnimalTable.Species, AnimalTable.CommonName," & _
" AnimalTable.Sex, AnimalTable.AcquisitionDate, AnimalTable.AgeAtAcquisition," & _
" AnimalTable.BirthDate, AnimalTable.BirthDateExact, AnimalTable.AnimalTag," & _
" AnimalTable.Diet, AnimalTable.Medications, AnimalTable.PhysicalDescription, AnimalTable.History, AnimalTable.FacilityAnimalID," & _
" Nz(Round((DateDiff(""day"",BirthDate,Now())/365.25),1),""missing birthdate"") AS AgeC" & _
" FROM AnimalTable" & _
" WHERE (((AnimalTable.AnimalAutoID)=[Forms]![ProcedureForm]![cboAnimalID]));"
Debug.Print strSql
Me.subAnimalDetails.Form.RecordSource = strSql
End Sub
I have gotten a series of different error dialogs regarding syntax problems that I have managed to fix, and now the code will run without an error dialog but instead gives #Func! in the textbox that is supposed to display AgeC.
I recently added the double quotes around "day" and "missing birthdate" but they did not fix the problem.
Any help MUCH appreciated. Thank you!!!