Results 1 to 9 of 9
  1. #1
    Girraffa is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    32

    Question SQL string to modify recordsource including a Nz expression keeps returning errors

    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!!!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What does the debug show the SQL to be? I'd use single quotes inside the DateDiff().
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Your DateDiff formula is incorrect. Use "d" instead of "day"
    Suggest you also use Date instead of Now as you don't need the time of day for this purpose
    Agree with Paul about using single quotes

    Code:
    Nz(Round(DateDiff('d',[BirthDate],Date())/365.25,1),'missing birthdate') AS AgeC
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    Girraffa is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    32
    That worked! Changed day to d and changed both sets of quotes within DateDiff to single quotes. I kept finding references online that listed multiple ways to refer to "day" being the unit and wasn't sure how to know which one(s) to use. Thank you so much!

  5. #5
    Girraffa is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    32
    Quote Originally Posted by pbaldy View Post
    What does the debug show the SQL to be? I'd use single quotes inside the DateDiff().
    I don't think I knew how to use the debug correctly...I put it in there following an example I found but it didn't seem to show me anything different, I probably need to research that more.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You're welcome

    You may find W3Schools a useful reference guide for many Access functions: https://www.w3schools.com/sql/func_m...s_datediff.asp
    One advantage over other sites is the 'try it yourself' feature

    Use Debug.Print to display the output in the VBE Immediate window. Also very useful for testing purposes
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Girraffa View Post
    I don't think I knew how to use the debug correctly...I put it in there following an example I found but it didn't seem to show me anything different, I probably need to research that more.
    For future reference, here's how to use it:

    http://www.baldyweb.com/ImmediateWindow.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Another site that might be useful/helpful

    Chip Pearson's site: Debugging VBA Code

  9. #9
    Girraffa is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    32
    Thank you so much folks!!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query returning all values instead of specified string
    By School Boy Error in forum Queries
    Replies: 19
    Last Post: 10-21-2017, 07:51 AM
  2. Replies: 1
    Last Post: 09-06-2017, 09:12 PM
  3. Replies: 2
    Last Post: 07-27-2015, 08:26 AM
  4. Returning part of a string
    By dr4ke in forum Queries
    Replies: 7
    Last Post: 01-15-2013, 11:21 AM
  5. Replies: 9
    Last Post: 08-27-2011, 11:20 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums