Solved

Microsoft Access query

Posted on 2016-09-23
9
51 Views
Last Modified: 2016-09-23
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
Comment
Question by:J.R. Sitman
  • 5
  • 4
9 Comments
 
LVL 19

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 500 total points
ID: 41813114
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
 

Author Comment

by:J.R. Sitman
ID: 41813197
I have no idea what to add to my query.   Can you add it to my query please?
0
 
LVL 19
ID: 41813204
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:J.R. Sitman
ID: 41813206
sorry, this is above my skill level
0
 
LVL 19
ID: 41813209
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
 

Author Comment

by:J.R. Sitman
ID: 41813212
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
 
LVL 19
ID: 41813214
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
 

Author Closing Comment

by:J.R. Sitman
ID: 41813315
Thanks.   With some help from a friend, I got it to work.
0
 
LVL 19
ID: 41813321
awesome !!! you're welcome ~ happy to help
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question