Between dates in queries

AccessDeano

Trying not to be a newbie
Local time
Today, 14:03
Joined
Aug 29, 2003
Messages
30
This may sound a stupid thing to ask, but when you use:

Between [StartDate] and [EndDate]

does the period include data entered on the [end date] or data entered upto that date.

Does that make sense?

I seem to be experiencing problems with data entered today and when I run a query with an enddate of today the data is not included.

Should that be the case?
 
does the period include data entered on the [end date]

Yes. i.e. if you search for 1st April to 31st April it WILL include data entered on the 31st April.

If that isn't happening, I'm guessing you might have problems with how your date is stored. I think I'm right in saying that if your field also stores a time (i.e. 31/04/2003 11:31:05) then these would NOT show up, as Access treats the [EndDate] parameter as 31/04/2003 00:00:00

HTH.
 
Matt,

If your end date is April 30th and your date is a short date
then none of the entries for the 30th will show up unless
you make it:

4/30/03 11:59:59 PM

The short date will default to 00:00:00 AM (a decision that
Access will make), but the short date for the end parameter
will not default to 11:59:59 PM (a decision that Access will
not make).

Wayne
 
Thanks Wayne, that is what I suspected.

Is there anything I could put in my criteria to make the selection add an extra day?

I tried

Between [startdate] And ([enddate] + 1)

but that did not work.

Thanks
 
I think so, the date is entered in a separate form, with both boxes set as short date.

The query also has short date configured.
 
:confused: :confused:

I'm confused by Wayne's reply. I'm running a holiday database which contains various queries where I want to bring back records between two dates - and it does just that.

My dates are stored as short dates (i.e. 30/06/2003), and if my end date parameter is 30/06/2003 it returns all the records I expect. But if I understand Wayne correctly, he's saying this shouldn't be the case???

I will always defer to somebody of Wayne's experience, but I'm still confused! :D

Matt.
 
If you use Now instead of Date for default entries then the time element is included
 
Hi all,

I knew I had suffered before because of this.
The attached db has tblDemoDates with one
entry: 4/9/03

There are two queries; qryGood and qryBad.

Wayne
 

Attachments

If the date/time field contains the time, I often use the DateValue() function.


Field: DateValue([DateTimeFieldName])
Show: uncheck
Criteria: Between [StartDate] and [EndDate]
 
Last edited:
Start Date and End date from a table

i want to be able to enter a start date and end date for a query
in a form. I have been trying to do it by using a table to change the 2 dates i want to specify, then have the criteria of the query point back to the 2 date entries in my table.

Between [tblJobBook]![Date1] And [tblJobBook]![Date2]

The fields are date/time types and each time i run the query it pops up asking for the values of the fields.

Is there something i'm doing wrong or a better way of doing it??
 
Kieran, you can't reference values in a table like you've written.

You have said that you are wanting to do this in a form although your piece about trying to get table values is somewhat confusing; moreso, however, irrelevant.

If you create two textboxes on your form called txtStart and txtEnd (that the user types the date range into) then you would reference these in your query...

Between [Forms]![frmYourForm]![txtStart] And [Forms]![frmYourForm]![txtEnd]


...where frmYourForm is the name of the form containing the controls.
 
What Format??

Ok thats gotten me a bit further, but now i get an error saying "This expression is typed incorrectly, or it is too complex to be evaluated."
Now i just want to make sure i'm typing the date in the correct format for the between operation to work.

I'm using just a short date.

Example.. 01/09/03 - 30/09/03

Is there more i need to add to use the between operation??
 
kieran,

Example.. 01/09/03 - 30/09/03

These are short dates, but to use them in a query or
SQL you need either:

Between #01/09/03# And #30/09/03#

Or

Between Forms ...

You had the syntax for the form method a few posts ago.

Wayne
 
Ok... The examples i gave were of the format i use to type the date in my form. I am using the similar syntax given by phil for my query criteria and when i try and run the query after filling out the form, that is when i get the error....

Now i use an input mask for the short date, could that be interfering??
 
Kieren,

It does not matter (somewhat) how you type them on the
form. I take it that it is a query that is referencing these
dates from controls on your form.

Your references should be:

Code:
Between Forms![YourForm]![YourDate] And Forms![YourForm]![YourOtherDate]

Wayne
 
i got it working. i removed the input mask and it worked.

I used the input mask just to add the "/" between the numbers automatically. Does anyone know an input mask that is query friendly??
 
kieran,

If they stored as DateTime fields, then the query should be
happy with them. No need to "format".

Are the fields in your table Dates? Or are they text fields
that you're formatting to look like dates?

Wayne
 
I got it going now.....

Thanks for the help, sorry for the stupid questions....
 

Users who are viewing this thread

Back
Top Bottom