Solved

Microsoft Access query

Posted on 2016-09-23
9
57 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 20

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 20
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
Independent Software Vendors: 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!

 

Author Comment

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

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

734 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