Running queries by selecting a date range

Running queries by selecting a date range

Post by Kath via A » Sat, 05 Feb 2005 06:11:09


I have the need to run reports for employees however the key is the date
has to exclude the year. The date format in the field is mm/dd/yy
(shortdate) and I need to be able run the query just with beg date of mm/dd
and end date of mm/dd. If anyone can help with this I would be greatly
appreciative!

Thanks in advance!

--
Message posted via http://www.yqcomputer.com/
 
 
 

Running queries by selecting a date range

Post by TWFydGluIE » Sat, 05 Feb 2005 06:49:02

Does that mean it defaults to the current year? If not what happens if they
enter 12/7 to 2/7? the form must stop this or you are going to get garbage.
You can program the format of the field in the format property to mm/dd and
input mask should be 99/99;0;_. However you'll need to program on to it the
year in the query something like between
DateSerial(Year(Date()),Month([forms]![test]![text0]),Day([forms]![test]![text0])) and ...

HTH
Martin J

 
 
 

Running queries by selecting a date range

Post by Kath via A » Sat, 05 Feb 2005 23:00:07

I need the whole date there so I have the format as mm dd, however input
mask is still having to be 99/99/00;0 Would it be just as easy to separate
the dates into separate fields then if needed to display as one I can just
combine by query? The problem is this is an employee database that will be
'read only' for all our staff, but a few individuals will be using it for
other HR related functions. (Yes..I know..HR? well our org does not have $$
to spend on an HR package right now and the data is ALL over the
place...long story.) Anyway, I need to be able to collect both age yet,
run reports based on months of birthdates and anniversaries (hire dates).
The hire dates will have to calculate in years how long they have worked
here.

--
Message posted via http://www.yqcomputer.com/
 
 
 

Running queries by selecting a date range

Post by TWFydGluIE » Sun, 06 Feb 2005 01:37:10

What are you trying to do? figure out an age? Get birthdays regardless of
year born? Maybe run a report for all person born in a month or hired in a
month and have there age and years of service? Be specific.

Martin J
 
 
 

Running queries by selecting a date range

Post by Kath via A » Sun, 06 Feb 2005 01:51:31

What I am trying to do is run a report - based on Month (IE: the user will
want all people who have a b-day in the month of March.)

The next one that I want to run is the anniversary list. This will be
based on Month (who has an anniversary this month). It will also be based
on years - what year are they celebrating here?

I need to ensure that I capture the year for the birthday - but that I do
not display it to the 'general' user.

Does that explain it better? :)

--
Message posted via http://www.yqcomputer.com/
 
 
 

Running queries by selecting a date range

Post by TWFydGluIE » Sun, 06 Feb 2005 02:33:01

1)On the form make a combo box with a field list like 1;january;2;february...
list columns 2 width should be 0;1 bound cloumn should be 1. then in the
query it should be alias:month([bday]) criteria forms![...]![combobox] no
need to show it. If want a range than you need to make 2 combo boxes and use
between in the query

2) same as before except use anniv. date instead of bday. to get how many
years is the same as getting age. It's been posted 100's of times.

3) if it depends on user in the on open event in the report do something like
If currentuser()="kathy" then
me![bdate].visible=false
else
me[bdate].visible=true
end if
If you dont use access security then everyone is admin and this wont work.
Using Windows network login is much more difficult. It can be done but I'm
not the 1 to explain it to you. Repost if that is what u want.

HTH
Martin J