Solved

Microsoft Access query

Posted on 2016-09-23
9
49 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:jrsitman
  • 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:jrsitman
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
 

Author Comment

by:jrsitman
ID: 41813206
sorry, this is above my skill level
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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:jrsitman
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:jrsitman
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
join 2 views with 5 conditions 3 44
Access Runtime 2010 Error 17 30
Optimizing a query 3 34
ORA-00923: FROM keyword not found where expected 3 30
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

911 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now