1

I have a select query that returns records based on 2 dates from a form - >=Forms!frmReport!StartDate AND <=Forms!frmReport!EndDate. The problem is that whenever the user enters the same date in the both StartDate and EndDate no record is returned even though I'm sure there is data in the table that matches the criteria. Can anyone say why the query isn't returning any information?

Kefash
  • 523
  • 2
  • 10
  • 24
  • It is conceivable that the criteria would not return an existing record if that record included a time. For today, the date criteria might be <= 41757 AND >= 41757 and the record contains 41757.63875. Same day but outside the criteria. – geoB Apr 28 '14 at 22:21
  • the default for the field when capturing the data is set to `=Now()` so the field includes a time. Just now I realize that if i remove the time from the associated field in the table then the information comes up. Does that mean that i would have to capture Date and Time separately? – Kefash Apr 28 '14 at 22:53
  • Is time relevant? If so, we'll need to craft the criteria differently. If it is not, see Yawar's comment below. – geoB Apr 28 '14 at 23:41
  • yes the time is relevant. geoB's method worked perfectly except it just dropped of the time. – Kefash Apr 28 '14 at 23:54

2 Answers2

1

If the field being compared is rs("Date"), try

Int(rs("Date")) >=Forms!frmReport!StartDate AND Int(rs("Date")) <=Forms!frmReport!EndDate

For the Query Builder, modify your date checking field to something like this:

enter image description here

Replace YourDateField with the name of your field.

And if time is relevant, get rid of the field expression in the query builder, and instead compare the record's field to:

>=Forms!frmReport!StartDate AND <= iif(Forms!frmReport!EndDate = Forms!frmReport!StartDate, dateadd("d",1,Forms!frmReport!EndDate), Forms!frmReport!EndDate)

This expression says if the end date is the same as the start date add a day, otherwise just use the end date.

geoB
  • 4,578
  • 5
  • 37
  • 70
  • I could not get this to work in the query builder. Am I putting it in the wrong place? – Kefash Apr 28 '14 at 22:56
  • the query now works well. It returns the date only and leaves off the attached time. but i guest i will have to store the time in a field of its own. – Kefash Apr 28 '14 at 23:49
  • No need to add a field. Try the extra-long expression in the edited solution. – geoB Apr 29 '14 at 00:47
1

Cast the form values to just date values in your query:

>= cdate(Forms![frmReport]![StartDate]) and <= cdate(Forms![frmReport]![EndDate])

And set your field default to =date() to capture the date only, instead of =now().

Yawar
  • 11,272
  • 4
  • 48
  • 80
  • thanks for your suggestion with the cdate but it didn't return any result. even after i changed the default to `Date()` the existing data already had the time attached. – Kefash Apr 28 '14 at 23:51