Question MS Access 2007 Dates

Tried it but it doesn't work.
doesn't help readers.
Did you get an error? If so, what was the err.number and/or message?

Where are you entering these BegDate and EndDate?
Where are you located? Perhaps your regional settings are confusing the issue???

I'm in Canada and my dates are DD/MM/YYYY, but Access uses MM/DD/YYYY.

Readers are having difficulty in understanding exactly where you are having problems. Minty has made another guess, and we are awaiting a response from you.
 
Thanks guys but I fear that we are going around in circles with this one. Let me start again and I'll try to explain better. I have an Access 2007 database that is to be used for keeping track of people who are on vacation. I have two date fields [Begindate] and [EndDate]. These fields should allow us to generate a report of people who are on vacation between the [BeginDate] and [EndDate]. How can I do this?
 
PHP: 
Between [Forms]![frmSomeForm]![BegDate] And [Forms]![frmSomeForm]![EndDate]

in your query grid.

Base your Report on this query. Don't know what other criteria or fields you may have that would need to be addressed!

HTH
 
Where in the query would you suggest I put the string? I have BeginDate and EndDate or should I create a new field in the query?
 
I don't know you table or query structure. Example:
If you had a form for Jobs and the query for that form had jobdte as a date field in it.

Then you could use something like this:

PHP: 
Between [Forms]![frmJobs]![StartDate] And [Forms]![frmJobs]![EndDate]


Or use a popup form as previously suggested and then set the report's OnLoadEvent to open the form for the date criteria. Again, I use a Main form for all of my date criteria.

HTH
 
I'm afraid it's still not working for me. It must be me. Do you happen to have a sample database that I can download? I can relate better to examples. Thank you.
 
I use Access 2010. So this is in Access 2000 mdb format.

HTH
 

Attachments

Last edited:
Sorry folks. I was away from my computer for a couple of days. burrina - thank you for the sample database that you posted for me. Unfortunately, it interrogates only one date field. I have recreated my database on a stand alone that I' going to try and upload (if I can get it right!). You will see from the tblLeaveSchedule table that there are two separate date fields which account for the date that leave begins and the other is for when the leave ends. What I am looking for is some sort of a Report Date Range form that allows me to enter the BeginDate and then the EndDate and I am able to generate a list (Report) all of all staff who are on leave between those dates. Unfortunately, only one date field wouldn't work because it would pull out dates when leave begins but not when it ends. Grateful for any help that you can provide - also, if you see any ways of improving the structure or appearance, I would certainly appreciate it.
 

Attachments

What about a query like this
Code: 
SELECT tblLeaveSchedule.ID, tblLeaveSchedule.LeaveID, tblLeaveSchedule.PersonID, tblLeaveSchedule.BeginDate, tblLeaveSchedule.EndDate, tblLeaveSchedule.Status
FROM tblLeaveSchedule
WHERE (((tblLeaveSchedule.BeginDate)<=#5/15/2015#) 
  AND ((tblLeaveSchedule.EndDate)>=#1/1/2015#));
 
Thank you. I'm having a bit of a slow Monday so please accept my apologies. Will this be in a query or in the actual report? Also, is there a way where the dates can be interactive?
 
Walk first, run later.

Does this query return the results you desire?
Assuming
Startdate = 1 jan 2015
Enddate = 15 may 2015
 
I'm afraid not. My default date settings are DD/mm/yyyy. If I enter the dates as you have provided, I don't get anything back.
 
"my date settings" are irrelevant, SQL requires US date formats, I choose these dates with a particular reason

Given your uploaded DB, it returns this data:
Code: 
PersonID	BeginDate	EndDate	Status
1	01-04-2015	06-04-2015	At Post
34	06-05-2015	15-05-2015	At Post
1	01-05-2015	10-05-2015	At Post
2	28-04-2015	05-05-2015	Working (elsewhere) in country
36	01-04-2015	20-04-2015	
36	10-05-2015	12-05-2015	
37	01-03-2015	20-04-2015
Which leads me to, did you even (really) try this?
 
I'm sorry, I don't understanding what you are trying to imply by saying "Which leads me to, did you even (really) try this?". I'm asking for help and I think that comment is a bit offensive. And yes, I did try it and gave you the results. Consider your knowledge a blessing, not a weapon!
 
Then why does the query return data for me?
 
Perhaps you're smarter than me :D. Really, I have tried exactly as you have stated. A silly question (coming from me!). In the query that you have written, it looks like the dates are the other way around. EndDate is where BeginDate etc.
 
I don't normally do this (not enough time) however - see the attached.
My original post gave you the basics of what you where trying to achieve, I've included a sample of how to use named queries in a viewer. Check that the dates are correctly pulling the information you want.
 

Attachments

pbrydone said:
In the query that you have written, it looks like the dates are the other way around. EndDate is where BeginDate etc.
Click to expand...
Why would you say that?

when working with periods this is the most logical way of doing this.

Try to Copy/paste the sql into a new query without touching it... see if the results are what you require or invision.

If not, give us something like... I want to see these records when I put in these dates.
 
You must log in or register to reply here.

Similar threads

A
Solved MS Access data base for brewery
Replies
17
Views
343
Pat Hartman
P
S
Should I Learn MS Access in the AI Era?
2 3
Replies
56
Views
1,855
SachAccess
S
N
Solved MS Access Switch Board Menu
Replies
5
Views
415
Pat Hartman
P
T
Interesting development...
Replies
9
Views
881
Pat Hartman
P
H
Efficient Deployment of MS Access Applications with MS SQL Server
Replies
10
Views
610
Isaac
Isaac

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom