ACCESS FEED: 2024

Jumat, 26 April 2024

Re: [MSAccessProfessionals] Sandbox environment

Hi Jane

It sounds like they are blowing smoke. I'd give them an empty database and let them enter their own test data which is exactly what you've done. Keep us posted as to the outcome.

Regards,

Bill Mosca

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Jane via groups.io <jcross=crossproducts.plus.com@groups.io>
Sent: Friday, April 26, 2024 6:56:01 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Sandbox environment

Thanks Bill.
It seems the company doing the asking don't really know what they're doing! They just wanted a copy of the database set up so they could play & not risk altering real data. So I've done that. They were throwing around a lot of terminology which meant nothing to me - trying to show off/bamboozle bosses, I suspect :-(

Re: [MSAccessProfessionals] HELLO AND HELP!

Hey Jane,

Thank-you very much for your reply!

Fingers-crossed...

Phil

Re: [MSAccessProfessionals] Sandbox environment

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Re: [MSAccessProfessionals] HELLO AND HELP!

Hi Phil
When you have the report open in design view, you should see 'Group & sort' on the ribbon, or if not, you can right click into a blank area of the report & you should get the right click menu items 'Sort & Group'. Then beneath your report you'll see <Add a Group> or <Add a Sort>. Click the <Add a Sort> & then select which fields you want sorted (Title) & in which order (probably A-Z ).
Best regards
Jane

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Kamis, 25 April 2024

[MSAccessProfessionals] HELLO AND HELP!

Gday to all,

MicroSoft often leaves me with the words of Mark Twain:

UNDER CERTAIN CIRCUMSTANCES, PROFANITY PROVIDES A RELIEF DENIED IN PRAYER

So it is, yet again. Im running WIN11 with Office 365, ACCESS ver. 2007-2016 [paid for by the year]

I know how to get a REPORT started... all I need to die happy is, learn how to make a REPORT, "report" my data in alphabetical order — as it appears in the table — HOW ACCESS decides on the order of each record, slays me — and how to make the Report Wizard NOT include "TITLE", each time a record is printed. I tried & errored myself into figuring column width, number of columns, etc.

I want to print-out a list of movie titles, in what MS used to call snaking columns, having finally caught-on-to the new nomenclature "Columnar" despite the "look" of TABULAR, in the wizard, representing what I want.

My ACCESS Dummies book has been less than useful, especially since, ripping-out pages in wild, maniacal frenzy, then hurtling the book against a wall. As Woody Leonhard — beta tester since 1.1 — has assured us, the best we can do, is think of ourselves as victims.

Thanks for all your time & guidance.

Phil

Selasa, 23 April 2024

Essential Guide To Safe Well Water Maintenance

If your home relies on well water, ensuring its safety and quality is your direct responsibility. Contaminated well water can harbor harmful bacteria, viruses, and chemicals that pose serious health risks. This comprehensive guide will arm you with the knowledge and best practices to maintain your well and ensure your family's drinking water is safe.

Why Well Water Maintenance Matters

Health Protection: Well water must be free from contaminants that can cause illness, particularly for vulnerable populations like infants, the elderly, or those with compromised immune systems.
Long-Term Well Functionality: Preventative maintenance prolongs the life of your well system, reducing costly repairs and replacements.
Peace of Mind: Staying on top of well inspection and management allows you to trust the water you drink and use.

Key Steps for Maintaining Your Well

1. Well Construction and Location

Professional Installation: Hire licensed well drillers to ensure proper construction, preventing surface water contamination.
Safe Site: The well should be situated away from potential contamination sources like livestock areas, septic systems, or chemical storage.
Uphill Placement: Wells installed on a slope should be uphill from contamination sources.

2. Regular Well Inspections

Annual Checkups: Schedule yearly inspections with a qualified well contractor to assess your system's overall condition.
Look for Changes: Monitor your well for any signs of damage, including cracks in the casing, a damaged or missing well cap, or pooling water near the wellhead.
Professional Assistance: If you detect any issues, contact a well professional immediately for repair.

3. Water Testing

Annual Testing (Minimum): Test your water for bacteria and nitrates at least once a year with a certified lab.
Contamination Suspicions: Immediately test your water if you notice changes in taste, odor, or color.
Nearby Activities: Test more frequently if activities near your well increase the risk of contamination (e.g., agriculture, new construction).

4. Maintaining the Wellhead

Secure and Sanitary Well Cap: Ensure your well cap is tightly sealed and free of cracks. It should have a vermin-proof screen over the vent.
Ground Slope: The ground around the well should slope away to prevent water pooling.
Keep it Clear: Maintain a clutter-free area around the well to limit pest habitats and simplify inspection.

5. Protecting Your Well from Contamination

Safe Distances: Keep known contamination sources (septic systems, livestock, fertilizer storage) well away from your well, following your local regulations for distances.
Mindful Chemical Use: Avoid using pesticides, herbicides, or fertilizers near the well.
Proper Waste Disposal: Never dispose of hazardous, chemical, or medical waste near your well.

Additional Tips

Well Records: Keep detailed records of well construction, testing results, and any repairs or maintenance.
Treatment Systems: If water tests indicate contamination, consider installing a suitable water treatment system.
Unused Wells: Decommission abandoned wells according to professional guidelines to protect groundwater.

The Importance of Professional Well Contractors

Always seek the expertise of licensed well contractors and pump installers for construction, repairs, and in-depth inspections. They possess the knowledge and tools to keep your well system operating effectively and safely.

By diligently following these guidelines, you'll significantly safeguard your family's health and ensure the longevity of your well water supply.

--
You received this message because you are subscribed to the Google Groups "Broadcaster" group.
To unsubscribe from this group and stop receiving emails from it, send an email to broadcaster-news+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/broadcaster-news/85802019-0992-4bdb-b3f6-3fd8632554f1n%40googlegroups.com.

Senin, 22 April 2024

Re: [MSAccessProfessionals] Sandbox environment

Hi Jane

Coming up with a test database after a live database is in use can be very tricky.

First off, you will need data that is not live. That is something you will have to deal with. My applications always start out with test data as I develop so I can easily present that data to testers if required, but having to create new test data on the fly is tedious at best. Can the live data be altered enough to point primary keys to fake entities?

Secondly, you will need to build a front end that is linked to the test database and restricted to users doing the testing.

I hope this gives you some idea as to where to start.

Regards,
Bill Mosca,
Founder, MS_Access_Professionals

MS Access MVP 2006-2016
My Nothing-to-do-with-Access blog

https://wrmosca.wordpress.com

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Jane via groups.io
Sent: Monday, April 22, 2024 11:51 AM
To: MSAccessProfessionals@groups.io
Subject: [MSAccessProfessionals] Sandbox environment

Hi all
I've been asked to create a sandbox environment for an existing database, so that another company can go through it & try out some scenarios, not on live data.
I understood that you usually create a Windows sandbox on your own PC for development work, but that it's not something that you share. Am I hopelessly out of date / my depth? :-)
Any advice/help gratefully accepted.
Thanks
Jane

[MSAccessProfessionals] Sandbox environment

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Rabu, 17 April 2024

How To Get Rid of iron In Well Water

If you're struggling with rusty-looking stains on your sinks, a metallic taste in your water, or concerns about the safety of your well, the culprit is likely excess iron. Several proven methods exist to remove iron from your well water, including filtration, aeration, and oxidation. Understanding the causes of iron in your well and the various removal solutions empowers you to make the best decision for your home.

Iron naturally occurs in soil and rocks. As rainwater percolates through the ground, it can dissolve iron minerals, carrying them into your well water. This issue is particularly common in areas with naturally iron-rich geology or if you have older well components that may be corroding.

How Does Iron Get Into My Well Water?

The primary way iron enters well water is through natural processes. Groundwater seeps through iron-rich soil and rock formations, dissolving iron minerals along the way. Additionally, older well casings, pipes, or pumps made of iron-based materials can gradually rust, contributing to elevated iron levels in your well water.

What Problems Does Iron in Well Water Cause?

Staining: Iron in well water is notorious for leaving unsightly reddish-brown stains on your plumbing fixtures, sinks, tubs, laundry, and even dishes.
Metallic Taste and Odor: You may notice an unpleasant metallic taste or even a rusty odor in your drinking water and in beverages made with it.
Appliance Damage: Over time, iron buildup can clog pipes and reduce the lifespan of water heaters, dishwashers, and other appliances.
Potential Health Concerns: While iron is an essential nutrient, the EPA notes that excessive long-term consumption could pose health risks, primarily for individuals with certain medical conditions.
Iron Bacteria: Iron in well water can promote the growth of iron bacteria, leading to slimy buildups and a worsening of taste, odor, and clogging issues.

How Do I Test for Iron in My Well Water?

DIY Kits: Simple home test kits provide a basic indication of iron presence, but they may not be accurate enough for precise decision-making.
Professional Lab Testing: For comprehensive results and critical information about your exact iron levels and other water quality parameters, send a water sample to a certified lab. Local health departments or water treatment companies can often help you find testing services.

What Are the Best Ways to Remove Iron from Well Water?

Filtration Systems
- Sediment Filters: These basic filters can remove some larger iron particles, but may be insufficient for higher iron levels.
- Iron-Specific Filters: Specialized filters with various media types trap and remove iron effectively, making them a popular treatment solution.
- Water Softeners: Traditional water softeners can remove small amounts of iron through ion exchange, but are more focused on addressing water hardness.
- Maintenance: Regardless of the filter type, regular replacement according to the manufacturer's instructions and monitoring your water quality with testing will ensure continued effectiveness.
Aeration
- Process: Aeration injects air into the water, oxidizing soluble ferrous iron into insoluble ferric iron, making it filterable.
- Best for: Aeration is ideal for high iron levels and is often combined with filtration for optimal results.
Chemical Oxidation (Chlorination)
- Process: Chlorine injection oxidizes iron and disinfects the water. Filtration then removes the oxidized particles.
- Considerations: Chlorine can affect water taste, requiring additional filtration steps, and needs careful handling due to its potential hazards.
Other Methods
- Reverse Osmosis: This removes a wide range of contaminants, including iron, but may be less cost-effective for targeting iron specifically.
- Distillation: Similar to reverse osmosis, it's very effective but may not be practical as a primary solution for whole-house iron removal.

How Much Does Iron Removal Cost?

The cost to remove iron varies greatly depending on:

Iron Level: Higher iron concentrations may require more complex treatment systems.
Water Usage: The size of your household and water demand impact system capacity needs
Chosen Method: Simple filters are less expensive, while aeration or chlorination systems have higher upfront and potential maintenance costs.
DIY vs. Professional: Professional installation adds expense but is often recommended for complex setups.

Can I Remove Iron from My Well Water Myself?

The possibility of DIY installation depends on your skill level and the chosen method. Installing basic sediment filters or under-sink iron filters can be within a homeowner's capability. For whole-house systems, aeration, or chlorination, professional consultation and installation are usually the safest and most reliable route.

Resources:

--
You received this message because you are subscribed to the Google Groups "Broadcaster" group.
To unsubscribe from this group and stop receiving emails from it, send an email to broadcaster-news+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/broadcaster-news/0ee47ecb-31f3-490b-9ba1-7e2b3732fdd6n%40googlegroups.com.

Senin, 08 April 2024

Well Water Safety: Testing, Contaminants, and Treatment Solutions

If you rely on a private well for your water supply, well water testing is essential to ensure the safety of your drinking water, as well as for overall household use. Unlike municipal water sources, which undergo regular treatment and monitoring, private wells are the homeowner's responsibility. This means that potential contaminants – whether from natural sources, human activities, or well system malfunctions – may not be detected without proactive testing.

Well water can harbor various contaminants that might affect your health. Bacteria, like E. coli, can cause gastrointestinal illnesses, while nitrates pose a particular risk for infants. Heavy metals like lead and arsenic have links to developmental problems and long-term health risks. Other contaminants, including minerals and chemicals, might not cause immediate illness but can affect the taste, odor, or appearance of your water.

Regular well water testing is the only way to proactively identify these potential issues. By understanding what's in your water, you can make informed decisions about treatment options and protect your household's health.

Why Well Water Testing Matters

Common Contaminants and Health Risks

It's crucial to understand that even clear, pleasant-tasting well water can contain harmful contaminants. Here are some of the most concerning categories:

Bacteria: Bacteria like coliform and E. coli can originate from septic systems or animal waste. They typically cause gastrointestinal issues like diarrhea, nausea, and vomiting.
Nitrates: Nitrates often leach into groundwater from fertilizers, septic systems, or livestock operations. They're particularly dangerous for infants, leading to "blue baby syndrome" (methemoglobinemia), a condition that reduces the blood's ability to carry oxygen.
Lead: Lead in drinking water often comes from corroded plumbing materials. Lead exposure can cause developmental delays and learning difficulties in children, and health problems in adults, including high blood pressure and kidney issues.
Arsenic: Arsenic is a naturally occurring element found in some rock formations. Long-term arsenic exposure increases the risk of various cancers, including skin, bladder, and lung cancer.
Radon: Radon is a radioactive gas that can seep into wells from underground sources. Exposure to radon is the second leading cause of lung cancer after smoking.
Other Contaminants: Well water can also contain excessive minerals (hardness, iron), pesticides, industrial chemicals, and other contaminants with varying health effects.

Long-Term Health Considerations

Some contaminants may not cause immediate illness, but their effects can accumulate with long-term exposure. Regular well water testing is the best way to monitor these potential risks and take action to keep your water safe. Additionally, well water testing gives you a baseline so you can detect any changes in your water quality over time.

Peace of Mind

Knowing what's in your well water offers peace of mind. When you're sure your water is safe, you can confidently use it for drinking, cooking, bathing, and other household activities, ensuring your family's health and well-being.

When to Test Your Well Water

Recommended Testing Frequency

The Centers for Disease Control and Prevention (CDC) recommends testing your well water at least annually for total coliform bacteria, nitrates, total dissolved solids, and pH levels. More frequent testing might be necessary depending on your area's risk factors or if you notice changes in your water.

Situations Requiring Immediate Testing

In addition to routine testing, schedule water testing immediately if you experience any of the following:

New Well or Major Repairs: If you have a newly constructed well or have undergone significant repairs, testing ensures it's producing safe water.
Changes in Water Quality: Changes in taste, odor, or appearance (cloudiness, unusual colors) can indicate contamination.
Gastrointestinal Illness: If household members experience unexplained stomach issues, test for bacterial contamination.
Infants or Pregnancy: Infants and pregnant women are more susceptible to waterborne contaminants, so extra precaution is vital.
Malfunctioning Septic Systems: Issues with your septic system increase the risk of groundwater contamination near your well.
Changes in Land Use: If new agricultural operations, industrial facilities, or other potential sources of contamination move near your property, prompt testing is warranted.

Staying Proactive

Remember, well water contamination isn't always obvious. Regular testing is a crucial part of responsible well ownership and ensures a safe drinking water supply for your household.

How to Get Your Well Water Tested

Types of Tests

DIY Test Kits: Home testing kits provide a basic analysis for some common contaminants like bacteria, nitrates, and pH. These kits offer limited accuracy and shouldn't replace thorough professional testing.
Professional Laboratories: Accredited laboratories offer comprehensive water testing services that analyze a wide range of contaminants, including bacteria, heavy metals, pesticides, and volatile organic compounds (VOCs). They provide detailed reports and guidance on interpreting results.

Finding Reputable Labs

Choosing a certified laboratory is critical for accurate results and reliable recommendations. Here's how to find one:

State Health or Environmental Departments: Contact your state health department or the equivalent environmental agency for a list of accredited water testing labs in your area.
Centers for Disease Control and Prevention (CDC): The CDC provides resources for finding certified laboratories.
Environmental Protection Agency (EPA): The EPA maintains a list of certified drinking water laboratories by state.

Understanding Your Test Results

Labs provide a detailed analysis of your water, including the levels of detected contaminants. Compare the results to the EPA's drinking water standards to determine if any contaminants exceed safe limits. Resources from agencies like the CDC and state health departments can help you interpret your results and determine the appropriate course of action.

What to Do If Your Well Water is Contaminated

Don't Panic

While concerning, most well water contamination issues are treatable. The first step is to understand the specific contaminants present, their potential health effects, and the appropriate remediation options.

Immediate Actions

Stop Drinking the Water: If contaminants exceed safe levels, immediately stop drinking and using your well water for cooking or brushing teeth.
Use Bottled Water: Use a reliable source of bottled water for drinking, cooking, and other essential needs until you implement a solution.

Seek Professional Guidance

Contact Your Health Department: Consult your state or local health department for guidance tailored to the specific contaminant(s) found in your water. They can help you understand health risks and recommend next steps.
Consult a Well Water Specialist: A certified well water contractor can assess your well system, identify potential contamination sources, and recommend treatment options.

Treatment Options

The best treatment solution depends on the type and level of contaminant(s). Some common options include:

Filtration Systems: Various filters target specific contaminants like bacteria, nitrates, heavy metals, or minerals.
Distillation: Distillation removes minerals, some metals, and effectively eliminates bacteria and viruses.
Chlorination: Chlorination effectively kills bacteria and viruses but may not address other contaminants.
Other Technologies: Technologies like reverse osmosis or ultraviolet (UV) light treatment offer solutions for a range of contaminants.

Choosing the Right Solution

Always consult with professionals to determine the most appropriate treatment system based on your test results, well characteristics, and household needs.

Well Maintenance for Safe Water

Alongside testing, regular well maintenance is crucial for protecting your water supply and extending the lifespan of your well system. Here's why it matters:

Preventing Contamination: Well maintenance helps identify and address potential points of contamination before they affect your water quality.
Ensuring System Functionality: Regular inspections catch mechanical issues early, preventing malfunctions that could compromise water safety or lead to costly repairs.
Proactive Protection: Well maintenance empowers you to take proactive steps safeguarding your drinking water, rather than just reacting to problems as they arise.

Key Maintenance Practices

Annual Inspections: Schedule yearly checkups with a qualified well contractor to inspect your well's casing, cap, and mechanical components.
Proper Location and Construction: Follow guidelines for well placement at a safe distance from potential contamination sources (septic systems, livestock pens, etc.). Ensure your well is constructed according to standards, with a sanitary seal preventing surface runoff from entering.
Protecting Your Wellhead: Maintain a clear area around your wellhead, keep it free of debris, and ensure the well cap is securely in place.
Monitoring for Changes: Be observant. Report any changes in your water, unusual activity around your well, or damage to the well.
Septic System Maintenance: Regularly pump and inspect your septic system to prevent malfunctions that could contaminate your well.

Safeguarding Your Well Water: Key Takeaways and Next Steps

Well water testing is an essential responsibility for private well owners. Regular testing identifies potential contaminants that may pose health risks, allowing you to make informed decisions about water treatment for safe consumption and household use. Understanding common contaminants, when to test, how to choose a lab, and simple maintenance practices significantly reduces risks and ensures ongoing access to safe drinking water.

Key Takeaways

Well water, unlike municipal water, isn't regularly treated or monitored.
Bacteria, nitrates, lead, arsenic, and other contaminants can jeopardize the safety of your water supply.
Test your well at least annually, and more often if risk factors are present or you notice changes in your water.
Professional water testing laboratories provide the most comprehensive analysis and guidance.
Prioritize regular well inspections and maintenance to prevent contamination and protect your investment.

Don't take chances with your family's health. If you rely on a private well:

Locate a certified water testing lab near you. Contact your state health department for resources as a starting point.
Schedule your initial well water test. Discuss the details of the testing process and expected timeframe for results with your chosen lab.
Consult a well water professional. Have a qualified contractor inspect your well system and offer advice on maintenance best practices.

By following these steps, you'll gain the peace of mind that comes from knowing your well water is safe and protecting the well-being of your household.

#wellwatertesting #wellwater #wellwatersafety #drinkingwater #groundwater #waterquality #homeowner #watercontamination #ruralliving #privatewell #health #bacteria #nitrates #arsenic #lead #homewater #watertreatment #cleanwater #watertest #watertestingkit

Resources:

How To Test For Well Water Contaminants - https://youtu.be/xctG-eAec2U
Why You Should Test Your Well Water Annually - https://youtu.be/YGEpCzoGu8M
How To Choose The Best Well Water Treatment System - https://youtu.be/tF_dXmbwQYs
Is Your Well Water Safe? Essential Testing and What the Results Mean

--
You received this message because you are subscribed to the Google Groups "Broadcaster" group.
To unsubscribe from this group and stop receiving emails from it, send an email to broadcaster-news+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/broadcaster-news/20a3e919-b05c-4838-adaa-9b3f24f85da4n%40googlegroups.com.

Jumat, 29 Maret 2024

Re: [MSAccessProfessionals] #Name? question

I deleted the control on the report

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Duane Hookom <duanehookom@hotmail.com>
Sent: Friday, March 29, 2024 7:38 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] #Name? question

Did you delete the field from the table or delete the control on the report?

Duane

On Mar 29, 2024, at 4:38 PM, Jim Wagner <josephwagner2@outlook.com> wrote:

Hello all,

I have a report that has Emplid as one of the fields on the report. When I run the report, the first Emplid has #Name? as the value, but the rest of the Emplid's are populated correctly. I am not sure what to do.

Thank You For any suggestions.

Jim Wagner

Re: [MSAccessProfessionals] #Name? question

Did you delete the field from the table or delete the control on the report?

Duane

On Mar 29, 2024, at 4:38 PM, Jim Wagner <josephwagner2@outlook.com> wrote:

Hello all,

I have a report that has Emplid as one of the fields on the report. When I run the report, the first Emplid has #Name? as the value, but the rest of the Emplid's are populated correctly. I am not sure what to do.

Thank You For any suggestions.

Jim Wagner

Re: [MSAccessProfessionals] #Name question

Hello all,

for kicks and giggles, I deleted the field and created a new one. It worked. I was worried that the other Empid's would then be messed up. You know reverse issue syndrome!

thank you

Jim Wagner

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

[MSAccessProfessionals] #Name? question

Hello all,

I have a report that has Emplid as one of the fields on the report. When I run the report, the first Emplid has #Name? as the value, but the rest of the Emplid's are populated correctly. I am not sure what to do.

Thank You For any suggestions.

Jim Wagner

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_

Minggu, 24 Maret 2024

Re: [MSAccessProfessionals] Date Calculation not giving corrct age

OK people, I solved my problem using a modified version of this code:

Public Function Diff2Dates(Interval As String, Date1 As Variant, Date2 As Variant, _

Optional ShowZero As Boolean = False) As Variant

'Author:    ? Copyright 2001 Pacific Database Pty Limited

'           Graham R Seach MCP MVP gseach@pacificdb.com.au

'           Phone: +61 2 9872 9594  Fax: +61 2 9872 9593

'           This code is freeware. Enjoy...

'           (*) Amendments suggested by Douglas J. Steele MVP

'Description:   This function calculates the number of years,

'               months, days, hours, minutes and seconds between

'               two dates, as elapsed time.

'Inputs:    Interval:   Intervals to be displayed (a string)

'           Date1:      The lower date (see below)

'           Date2:      The higher date (see below)

'           ShowZero:   Boolean to select showing zero elements

'Outputs:   On error: Null

'           On no error: Variant containing the number of years,

'               months, days, hours, minutes & seconds between

'               the two dates, depending on the display interval

'               selected.

'           If Date1 is greater than Date2, the result will

'               be a negative value.

'           The function compensates for the lack of any intervals

'               not listed. For example, if Interval lists "m", but

'               not "y", the function adds the value of the year

'               component to the month component.

'           If ShowZero is True, and an output element is zero, it

'               is displayed. However, if ShowZero is False or

'               omitted, no zero-value elements are displayed.

'               For example, with ShowZero = False, Interval = "ym",

'               elements = 0 & 1 respectively, the output string

'               will be "1 month" - not "0 years 1 month".

On Error GoTo Err_Diff2Dates

   Dim booCalcYears As Boolean

   Dim booCalcMonths As Boolean

   Dim booCalcDays As Boolean

   Dim booCalcHours As Boolean

   Dim booCalcMinutes As Boolean

   Dim booCalcSeconds As Boolean

   Dim booCalcWeeks As Boolean

   Dim booSwapped As Boolean

   Dim dtTemp As Date

   Dim intCounter As Integer

   Dim lngDiffYears As Long

   Dim lngDiffMonths As Long

   Dim lngDiffDays As Long

   Dim lngDiffHours As Long

   Dim lngDiffMinutes As Long

   Dim lngDiffSeconds As Long

   Dim lngDiffWeeks As Long

   Dim varTemp As Variant

   Const INTERVALS As String = "dmyhnsw"

'Check that Interval contains only valid characters

   Interval = LCase$(Interval)

   For intCounter = 1 To Len(Interval)

      If InStr(1, INTERVALS, Mid$(Interval, intCounter, 1)) = 0 Then

         Exit Function

      End If

   Next intCounter

'Check that valid dates have been entered

   If IsNull(Date1) Then Exit Function

   If IsNull(Date2) Then Exit Function

   If Not (IsDate(Date1)) Then Exit Function

   If Not (IsDate(Date2)) Then Exit Function

'If necessary, swap the dates, to ensure that

'Date1 is lower than Date2

   If Date1 > Date2 Then

      dtTemp = Date1

      Date1 = Date2

      Date2 = dtTemp

      booSwapped = True

   End If

   Diff2Dates = Null

   varTemp = Null

'What intervals are supplied

   booCalcYears = (InStr(1, Interval, "y") > 0)

   booCalcMonths = (InStr(1, Interval, "m") > 0)

   booCalcDays = (InStr(1, Interval, "d") > 0)

   booCalcHours = (InStr(1, Interval, "h") > 0)

   booCalcMinutes = (InStr(1, Interval, "n") > 0)

   booCalcSeconds = (InStr(1, Interval, "s") > 0)

   booCalcWeeks = (InStr(1, Interval, "w") > 0)

'Get the cumulative differences

   If booCalcYears Then

      lngDiffYears = Abs(DateDiff("yyyy", Date1, Date2)) - _

              IIf(Format$(Date1, "mmddhhnnss") <= Format$(Date2, "mmddhhnnss"), 0, 1)

      Date1 = DateAdd("yyyy", lngDiffYears, Date1)

   End If

   If booCalcMonths Then

      lngDiffMonths = Abs(DateDiff("m", Date1, Date2)) - _

              IIf(Format$(Date1, "ddhhnnss") <= Format$(Date2, "ddhhnnss"), 0, 1)

      Date1 = DateAdd("m", lngDiffMonths, Date1)

   End If

   If booCalcWeeks Then

      lngDiffWeeks = Abs(DateDiff("w", Date1, Date2)) - _

              IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)

      Date1 = DateAdd("ww", lngDiffWeeks, Date1)

   End If

   If booCalcDays Then

      lngDiffDays = Abs(DateDiff("d", Date1, Date2)) - _

              IIf(Format$(Date1, "hhnnss") <= Format$(Date2, "hhnnss"), 0, 1)

      Date1 = DateAdd("d", lngDiffDays, Date1)

   End If

   If booCalcHours Then

      lngDiffHours = Abs(DateDiff("h", Date1, Date2)) - _

              IIf(Format$(Date1, "nnss") <= Format$(Date2, "nnss"), 0, 1)

      Date1 = DateAdd("h", lngDiffHours, Date1)

   End If

   If booCalcMinutes Then

      lngDiffMinutes = Abs(DateDiff("n", Date1, Date2)) - _

              IIf(Format$(Date1, "ss") <= Format$(Date2, "ss"), 0, 1)

      Date1 = DateAdd("n", lngDiffMinutes, Date1)

   End If

   If booCalcSeconds Then

      lngDiffSeconds = Abs(DateDiff("s", Date1, Date2))

      Date1 = DateAdd("s", lngDiffSeconds, Date1)

   End If

   If booCalcYears And (lngDiffYears > 0 Or ShowZero) Then

      varTemp = lngDiffYears & IIf(lngDiffYears <> 1, " years", " year")

   End If

   If booCalcMonths And (lngDiffMonths > 0 Or ShowZero) Then

      If booCalcMonths Then

         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _

                   lngDiffMonths & IIf(lngDiffMonths <> 1, " months", " month")

      End If

   End If

   If booCalcWeeks And (lngDiffWeeks > 0 Or ShowZero) Then

      If booCalcWeeks Then

         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _

                   lngDiffWeeks & IIf(lngDiffWeeks <> 1, " weeks", " week")

      End If

   End If

   If booCalcDays And (lngDiffDays > 0 Or ShowZero) Then

      If booCalcDays Then

         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _

                   lngDiffDays & IIf(lngDiffDays <> 1, " days", " day")

      End If

   End If

   If booCalcHours And (lngDiffHours > 0 Or ShowZero) Then

      If booCalcHours Then

         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _

                   lngDiffHours & IIf(lngDiffHours <> 1, " hours", " hour")

      End If

   End If

   If booCalcMinutes And (lngDiffMinutes > 0 Or ShowZero) Then

      If booCalcMinutes Then

         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _

                   lngDiffMinutes & IIf(lngDiffMinutes <> 1, " minutes", " minute")

      End If

   End If

   If booCalcSeconds And (lngDiffSeconds > 0 Or ShowZero) Then

      If booCalcSeconds Then

         varTemp = varTemp & IIf(IsNull(varTemp), Null, " ") & _

                   lngDiffSeconds & IIf(lngDiffSeconds <> 1, " seconds", " second")

      End If

   End If

   If booSwapped Then

      varTemp = "-" & varTemp

   End If

   Diff2Dates = Trim$(varTemp)

End_Diff2Dates:

   Exit Function

Err_Diff2Dates:

   Resume End_Diff2Dates

End Function

------------------------------------------------------

example:

diff2dates("ymd",#10/4/1923#,#5/25/2021#,True)

result:

97 years 7 months 21 days

Regards,
Lou

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

_._,_._,_