Question MS Access 2007 Dates (1 Viewer)

[pbrydone]

:banghead: I am using Access 2007. I have a table dealing with vacations with FirstName, LastName, BeginDate and, EndDate. I would like to be able to run a report via a Report Date Range form which will allow me to check who all are on vacation between the BeginDate and the EndDate. I would be very grateful for any help that anyone could give please. what would be even better is a downloadable sample database. Many thanks in anticipation.

 

[namliam]

What have you tried and what are you running into that is causing you problems?

 

[pbrydone]

I am using the standard Report Date Range form but that only picks up dates when you have one date field. I'm having problems because I have two separate date fields in the table. I need the Report Date Range form to pick up dated between two separate fields.

 

[jdraw]

Without seeing your actual code/design, it is difficult to respond.
You may get some ideas from this tutorial by Martin Green.

Please provide a copy of your database in zip format.

 

[pbrydone]

I'm afraid that I can't provide the database because its on a mainframe and we can't transfer anything in or out. The code is standard which is used in most Report Date Range forms that are available for download.

 

[jdraw]

So you have an Access front end for reporting and it is talking to a mainframe based database. What is the database software on the mainframe?

I think readers need more info if they are going to provide focused help.
Can you post a jpg (zip format) of your form and the values you are using?

 

[pbrydone]

At the moment the database is standalone with no FE Be as yet

 

[jdraw]

Where is this database? If it is standalone is it on your PC?
Show us the form that is causing the issue --a jpg before submitting may be helpful.

 

[The_Doc_Man]

"Windows" and "mainframe" in the same sentence AND it isn't a split DB? And you can't transfer anything in or out? I'll say this much - that is probably the most secure system you can have - one where "you can't get there from here."

Oh, well...

The most common error I have seen for this kind of thing is that you have dates in a text format rather than a date (internal) format.

 

[jdraw]

I agree with Doc_Man -- seems it's so secure even you can't tell us anything specific.
We are willing/trying to help, but you have to give us something to work with.

What exactly is the status at the moment?
Please work from the old -who, what, where, when, how much and how often - approach to describe to readers your current situation.

 

[pbrydone]

Guys - thank you so much for taking an interest. I am attaching a Word document with screenshots from the database. Everything else works fine. It's just generating a report selecting dates between two different date fields that is causing me problems. Most Date Range forms / queries that I have worked with interrogate only one date field in a table and return data for dates between the selected date range. This is a first that I'm trying with two separate dates - I need both dates because they represent dates during which staff are away on leave.

Needless to say, thank you very much for any help or advice that you can give.

 

[namliam]

What type of fields are your form dates? Text or Date? Looks like Text fields

What format of date are you entering in those fields? i.e. if you were to enter 1st July 2015, how would you enter that?

 

[pbrydone]

They are both in Date format

 

[jdraw]

Most Date Range forms / queries that I have worked with interrogate only one date field in a table and return data for dates between the selected date range.

How do you get a range from one date field -- unless it is relative to Date()?

You didn't answer namliam's question
if you were to enter 1st July 2015, how would you enter that?

Did you look at the tutorial by Martin Green I mentioned in post #4?

 

[pbrydone]

01/07/2015. The date format is fine

 

[namliam]

Try entering 07/01/2015 as in Jul/01/2015

 

[pbrydone]

Tried it but it doesn't work. The date format for the system is set to dd/MM/yyyy. If this can't work then grateful for any other suggestions on how to create a new database that can do what I am looking for. I have looked around but can't find anything that would fill my needs. Thank you

 

[namliam]

what if you enter 07/01/2015 directly into the query?

 

[burrina]

I generally use a Main form for this with date fields that can be edited. Then refer to those fields in your query. Or you could use a popup form and do the same thing. You can format the date fields as needed.

HTH

 

[Minty]

I think this might simply that the OP doesn't understand how to use the criteria on two dates - HolStart and HolEnd with criteria dDateFrom and dToDate...

Something like this - NOT TESTED!

SELECT stuff 
FROM table
WHERE (HolStart >=Forms!ReportSelector!dDateFrom And Holstart<=DateAdd("d",1,(Forms!ReportSelector!dDateTo))
AND
(HolEnd >=Forms!ReportSelector!dDateFrom And HolEnd<=DateAdd("d",1,(Forms!ReportSelector!dDateTo))

I may have the AND and OR bits not quite right (sorry on tablet and cant' test) but it's about the jist of it.
The date add bit accommodates dates stores as datetime on a SQL server.