Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • Last Modified:

Microsoft Access query

How do I have the "animalage" display as months and days?

SELECT ss.Location, ss.OriginalName, s.Species, ss.SoftSlip, b.Breed, ss.Sex, ss.DOB, Placements.Kennel, Placements.PlacementEndDate, AnimalAge([DOB]) AS Expr1, ss.ImpoundCode
FROM (((SELECT ss1.* FROM SoftSlips AS ss1 LEFT JOIN (SELECT SoftSlip FROM MedicalMedications AS m WHERE (m.medicationsID = 60 OR m.medicationsID = 116 OR m.medicationsID = 118 OR m.medicationsID =173 OR m.medicationsID =204 OR m.medicationsID =308) AND m.SoftSlip  IS NOT NULL GROUP BY SoftSlip)  AS q ON ss1.SoftSlip = q.SoftSlip WHERE q.SoftSlip IS NULL)  AS ss LEFT JOIN Breeds AS b ON ss.BreedID = b.BreedID) LEFT JOIN Species AS s ON ss.SpeciesID = s.SpeciesID) INNER JOIN Placements ON ss.SoftSlip = Placements.SoftSlip
WHERE (((ss.Location)=[Enter Location]) AND ((s.Species)="Dog" Or (s.Species)="Cat") AND ((ss.DOB)<=DateAdd("m",-3,Date())) AND ((Placements.Kennel) Not In ("lf","sf")) AND ((Placements.PlacementEndDate) Is Null) AND ((ss.ImpoundCode) Not In ("rtn")) AND ((ss.Action) Is Null))
ORDER BY s.Species DESC , ss.SoftSlip;
0
J.R. Sitman
Asked:
J.R. Sitman
  • 5
  • 4
1 Solution
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
here is code you can send DOB to calculate age in months and days:
Function GetAgeMonthsDays(pDOB As Date _
   , Optional pDate As Date = 0) As String
' Crystal (strive4peace)
' 160923
   'get age in monahts and days
   'if pDate is not specified, the current date is used
   
   Dim nMonths As Long _
      , nDays As Long _
      , nDate As Date
   
   If pDate = 0 Or Not IsDate(pDate) Then pDate = Date

   nMonths = DateDiff("m", pDOB, pDate)
   nDate = DateAdd("m", nMonths, pDOB)
   
   If Day(nDate) > Day(pDate) Then
      nMonths = nMonths - 1
      nDate = DateAdd("m", -1, nDate)
   End If
   
   nDays = DateDiff("d", nDate, pDate)
   
   GetAgeMonthsDays = Format(nMonths, "#,##0") & " month" _
      & IIf(nMonths <> 1, "s", "") & ", " _
      & Format(nDays, "0") & " day" _
      & IIf(nDays <> 1, "s", "")
End Function

Open in new window

0
 
J.R. SitmanAuthor Commented:
I have no idea what to add to my query.   Can you add it to my query please?
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
the query is currently using a function called AnimalAge.  Instead of that function, I am giving you a different one. This code gets pasted into a standard module
**********************************************************
*** How to Create a Standard (General) Module ***

Press Alt-F11 to go to the VBE (Visual Basic Editor)

From the menu in a the Microsoft Visual Basic window:
Insert --> Module

once the code is in the module sheet, from the menu, do -->
Debug,Compile

if there are no syntax/reference errors, nothing will appear to happen -- in this case, nothing happening is good <g>

Make sure to give the module a good name when you save it.  You can have several procedures (Subs and Functions) in a module, which gives you a way to categorize them ... ie: basic procedures that would be useful in any database; procedures that are specific to a particular database; procedures for converting data; etc

~~~~~ also be sure to use Option Explicit at the top of each module so variables that are not declared or are misspelled will be picked up
Option Explicit ' require variable declaration

Open in new window

once the function is available, then you can change the query to use it instead.
AnimalAge([DOB]) AS Expr1 -->
 GetAgeMonthsDays([DOB]) as Age_MoDays

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
J.R. SitmanAuthor Commented:
sorry, this is above my skill level
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
did you see the end of my comment? I told you exactly how to change the SQL statement.  If you post the database, I will put it in there for you.

I gave you each step.  I realize VBA can be daunting -- but it doesn't have to be.  ... try it!  After you compile the code, be sure to save -- there is a diskette icon on the toolbar you can click. You will be prompted for a name -- call it something like: mod_Functions

where it says, "once the code is in the module sheet" -- paste the code I gave you :)
0
 
J.R. SitmanAuthor Commented:
The database is too large and is confidential.  I saw your comment GetAgeMonthsDays([DOB]) as Age_MoDays,.  If that's what you're referring to it didn't work.  I guess because I need to create the module.  Sorry.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
try to do it -- and if you have problems, just ask ...

here is a ridiculous equation -- and it is not perfect either, but good for an estimate since you want a formula.  It assumes 30.5 days in a month
   (date() - [dob] )\30.5 & " months, " & (datediff("d",  [dob] , date()) - ((date() - [dob] )\30.5)*30.5) & " days"

Open in new window

I'll bet it took me more time to figure this out than it would have taken you to create a module ...
0
 
J.R. SitmanAuthor Commented:
Thanks.   With some help from a friend, I got it to work.
0
 
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
awesome !!! you're welcome ~ happy to help
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now