between And Dates in Query Criteria (1 Viewer)

rkrause

Registered User.
Local time
Today, 08:14
Joined
Sep 7, 2007
Messages
343
I have a query and im using between [Start} and [end] for my criteria giving the user a parameter to enter the dates they choose.
im using a field called datesubmitted and if anything is submitted on 1st of month or last day of month it wont pull into query.
so if i put 4-1-11 and 4-30-11 as dates i get 4-2-11 and 4-29-11

any help would be great.
 

boblarson

Smeghead
Local time
Today, 08:14
Joined
Jan 12, 2001
Messages
32,068
If your datesubmitted date has a time element stored, even if it isn't displayed, it will cause you to have to query it in a different way - something like this:

Between ([Start] + #12:00:00 AM#) And ([End] + #11:59:59 PM#)
 

rkrause

Registered User.
Local time
Today, 08:14
Joined
Sep 7, 2007
Messages
343
it is a date/time field , but i have no times associated withthis field
 

rkrause

Registered User.
Local time
Today, 08:14
Joined
Sep 7, 2007
Messages
343
even if i hardcode 4-1-11 and 4-30-11 in im still missing data from the 4-30 date.
 

boblarson

Smeghead
Local time
Today, 08:14
Joined
Jan 12, 2001
Messages
32,068
even if i hardcode 4-1-11 and 4-30-11 in im still missing data from the 4-30 date.

Try using this instead:
Code:
Between (Format([Start], "\#mm\/dd\/yyyy\#") + #12:00:00 AM#) And (Format([End], "\#mm\/dd\/yyyy\#") + #11:59:59 PM#)
If that fails then create a new field in your query by using:

NewDate:DateValue([OriginalFieldName])

And then
Code:
Between Format([Start], "\#mm\/dd\/yyyy\#") And Format([End], "\#mm\/dd\/yyyy\#")
 

datAdrenaline

AWF VIP
Local time
Today, 10:14
Joined
Jun 23, 2008
Messages
697
>> it is a date/time field , but i have no times associated withthis field <<

Times are ALWAYS associated with a Date/Time field. The value of a date/time field is stored as an 8 byte floating point number (ie: a Number/Double). The whole number portion is the number of days away from the base date of 1899-30-12, and the fractional portion is the time represented as a fraction of a day with midnight being a value of .0.

So ... you always have a time, even if the time is 12:00 AM. Also, keep in mind that in many circumstances, a Date/Time field is DISPLAYED with only the date portion of the value. Unfortuatly this displayed value can also be set at the table level, thus creating the illusion that you only have time components of 12:00 AM. I would encourage you to remove any Format Property setting at the table level, then view your data in the Datasheet View, you will likely see that your data has times other than 12:00 AM.

----

Have you tried Bob's suggestion? He suggested as he did because the range that will result encompases all times from the date range your users specify.
 

rkrause

Registered User.
Local time
Today, 08:14
Joined
Sep 7, 2007
Messages
343
bob

neither of your suggestions work. i get errors about it being to complex?
 

boblarson

Smeghead
Local time
Today, 08:14
Joined
Jan 12, 2001
Messages
32,068
bob

neither of your suggestions work. i get errors about it being to complex?
I've had others have the same issue and it usually came down to using the DateValue with the new field but you also have to account for nulls with that. If you want to upload a copy of the db I can try to take a look at it.
 

boblarson

Smeghead
Local time
Today, 08:14
Joined
Jan 12, 2001
Messages
32,068
my database is too big to upload
Perhaps running Compact and Repair and then zipping it would help and if it doesn't go below 2Mb then perhaps MediaFire or something like that you could upload to. It would just be easier for me to be able to get to a point where it works.
 

datAdrenaline

AWF VIP
Local time
Today, 10:14
Joined
Jun 23, 2008
Messages
697
... Also, understand that parameters that are not specifically given a datatype, will be first interpretted as Text. Most often, Access knows how to coerce the text into a the data type that the user wants, however, Access is not always successfull.

So ... along with Bob's example of using the Format() function, you may also what to force the data type by explicitly declaring your Parameters with their datatype, or use the CDate() function ...

Code:
Between DateValue(CDate([Start])) And DateValue(CDate([End])) + TimeValue(#23:59:59#)
 

boblarson

Smeghead
Local time
Today, 08:14
Joined
Jan 12, 2001
Messages
32,068
Okay, I found the problem. You have a record with a NULL in the date field and that seems to be causing the problem. If you create a new field like this in your query:

NewField:DateValue(Nz([DateSubmitted], #1/1/1899#))

And then the criteria as you had it with the form references will work.
 

datAdrenaline

AWF VIP
Local time
Today, 10:14
Joined
Jun 23, 2008
Messages
697
You have a Null "DateSubmitted", as Bob had suspected, for a record in Innovative. Also, of you remove the format of "Short Date" at the Table design level for DateSubmitted, you will see that your DateSubmitted does indeed have times associated with the record (as I suggested in an earlier post :) ). Many of them being 8:00 AM and 7:30 AM ... In the upload I attached, I have removed that Format property on the DateSubmitted field so you can see the stored date value when you open the Table object in datasheet view.

Also, we have no way to get to your linked table ... so ... this is what your criteria expression should be ...

Between DateValue([Forms]![frm_InnovativeByDept_DateForm].[startdate]) And DateValue([Forms]![frm_InnovativeByDept_DateForm].[EndDate])+TimeValue(#11:59:59 PM#)

I have put that in both queries but was unable to test since we can't get to your linked tables.

Please let us know how that works for you.
 

Attachments

datAdrenaline

AWF VIP
Local time
Today, 10:14
Joined
Jun 23, 2008
Messages
697
Sorry 'bout the dup info ... Bob and I racing to get the finish and his speedy internet prowess beat me to the punch! ... :D

... But ... do take a look at the full value of DateSubmitted from the attachment I posted ... you will see the fact that your DateSubmitted set of values do include times that are not exclusive to 12:00 AM.
 

boblarson

Smeghead
Local time
Today, 08:14
Joined
Jan 12, 2001
Messages
32,068
Sorry 'bout the dup info ... Bob and I racing to get the finish and his speedy internet prowess beat me to the punch! ... :D
A couple of different ways to get the result. However, if Brent's works, I would go with that instead because it doesn't require creating an extra field in the query.

... But ... do take a look at the full value of DateSubmitted from the attachment I posted ... you will see the fact that your DateSubmitted set of values do include times that are not exclusive to 12:00 AM.
Yes, for sure.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom