Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Microsoft Access query

Posted on 2016-09-23
9
Medium Priority
?
66 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 22

Accepted Solution

by:
crystal (strive4peace) - Microsoft MVP, Access earned 2000 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 22
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:J.R. Sitman
ID: 41813206
sorry, this is above my skill level
0
 
LVL 22
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 22
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 22
ID: 41813321
awesome !!! you're welcome ~ happy to help
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
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…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

670 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