Discussion on:

10 tips for working with dates in Microsoft Access

Have you sometimes run into problems with unexpected or unwanted results involving dates in Access? What other tips/shortcuts have you found useful when it comes to working with dates?

I run a queryr which returns a computer inventory, but I need it to return the inventory for the current month. I did it this way:

Format(Date(), "mmmm")

Cool. Now I know to use that event. I've been using On Change. But On Dirty simplifies things a bit.

I have been using Access since Version 2 was released. Dates are the one area MS has a lot to fix up across all its suite of applications. This is the one area I hide the sledge hammer and take extended time out to rethink a workaround.

I think it's very useful, but it's not all what i need.

Bonjour, je dois saisir 2 fois la m?me date sur un formulaire de saisie Access.
Existe-t-il une formule pour que la date exp?dition = date r?ception sans la saisir une 2?fois?
Celle-ci devrait s'imputer directement dans la table des enregistrements.
Merci d'avance

Hi,

SELECT e.EmpCode, e.DesiCode, e.EmpName, e.EmpAdd, e.BasicSalary, d.DesiName, e.JoiningDate, Format (e.JoiningDate, 'MM/yyyy'),format(e.RetierDate,"mm/yy"),e.RetierDate, Format (e.RetierDate, 'MM/yyyy'), (SELECT SUM(a.Amount) FROM pay_emp_master as e1 INNER JOIN pay_emp_allowances as a ON e1.EmpCode=a.EmpCode WHERE a.EmpCode=e.EmpCode GROUP BY e1.EmpCode,a.AllowCode) AS SumAllow, IIF([SumAllow],[SumAllow],0) AS SumAllow1, (SELECT SUM(d.Amount) FROM pay_emp_master as e1 INNER JOIN pay_emp_deductions as d ON e1.EmpCode=d.EmpCode WHERE d.EmpCode=e.EmpCode GROUP BY e1.EmpCode,d.DeductionCode) AS SumDeduct, IIF([SumDeduct],[SumDeduct],0) AS SumDeduct1
FROM pay_emp_master AS e INNER JOIN pay_desi_master AS d ON e.DesiCode=d.DesiCode
WHERE (e.EmpCode=17) And ((year(#02/2002#) between year(e.JoiningDate) and year(e.RetierDate)) AND (month(#02/2002#) >= month(e.JoiningDate) and month(#02/2002#)= month(e.RetierDate)));

As per above query I can check whether years of dates as well as months of dates is between the two dates or not.
But if my first date is '01/2002' (it is in the month/year format) & second date is '01,2004' (it is in the month/year format) & I wish to check whether '02/2002'is between '01/2002' & '01,2004' then above query is not fetching any records......so plz plz plz help me with this...it's urgent!!!!

how to calculate how many days in each month for a duration which includes more than one month. For example if start date is 12/20/2011 and end date is 1/25/2012 then I need a formula which can output 11 in MonthCoulmn December and 25 in Month Column January

I am looking for a way to create an expression or a custom function for displaying and calculating specific dates for "business days" only ie, excluding Saturday or Sunday or Holidays....Is that possible.

Try re-posting in the Q&A
section.The 'Discussion' forum is for matters of general discussion, not specific problems in search of a solution. The 'Water Cooler' is for non-technical discussions. You can submit a question to 'Q&A' here:

http://www.techrepublic.com/forum/questions/post?tag=mantle_skin;content

There are TR members who specifically seek out problems in need of a solution. Although there is some overlap between the forums, you'll find more of those members in 'Q&A' than in 'Discussions' or 'Water Cooler'.

Be sure to use the voting buttons to provide your feedback. Voting a '+' does not necessarily mean that a given response contained the complete solution to your problem, but that it served to guide you toward it. This is intended to serve as an aid to those who may in the future have a problem similar to yours. If they have a ready source of reference available, perhaps won't need to repeat questions previously asked and answered. If a post did contain the solution to your problem, you can also close the question by marking the helpful post as "The Answer". . .