Question MS Access 2007 Dates (1 Viewer)

[jdraw]

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.

 

[pbrydone]

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?

 

[burrina]

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

 

[pbrydone]

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?

 

[burrina]

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:

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

 

[pbrydone]

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.

 

[burrina]

I use Access 2010. So this is in Access 2000 mdb format.

HTH

 

[Minty]

Tried it but it doesn't work. The date format for the system is set to dd/MM/yyyy.

I think people will get bored trying to help you if you don't show us what you have tried!
We can't second guess what you have tried to help guide you.

And tbh you haven't looked very hard; These threads show examples
http://www.access-programmers.co.uk/forums/showthread.php?t=278127&highlight=between+two+dates

 

[jdraw]

There is a thread discussion and sample database here that deals with queries using 2 dates.
See if it helps. If not, come back to us with more specific info.

 

[pbrydone]

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.

 

[namliam]

What about a query like this

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#));

 

[pbrydone]

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?

 

[namliam]

Walk first, run later.

Does this query return the results you desire?
Assuming
Startdate = 1 jan 2015
Enddate = 15 may 2015

 

[pbrydone]

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.

 

[namliam]

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

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?

 

[pbrydone]

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!

 

[namliam]

Then why does the query return data for me?

 

[pbrydone]

Perhaps you're smarter than me . 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.

 

[Minty]

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.

 

[namliam]

In the query that you have written, it looks like the dates are the other way around. EndDate is where BeginDate etc.

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.