Member Avatar for mailtosridar

help me how to select the record between two date...........................................

  • 4 Contributors
  • 3 Replies
  • 109 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by HI2Japan

Dani AI

Generated

A few clarifications and a more robust pattern than the simple examples already offered by and . BETWEEN works for plain date-only values, but common gotchas are the time portion in Date/Time fields and locale/formatting when you use literal dates. 's sample is a useful starting point, but the approach below avoids missing rows and avoids common type/format pitfalls.

If the column stores times as well as dates, use an inclusive start and an exclusive end (end + 1 day) so rows on the end date at any time are captured:

WHERE MyDate >= DateValue([StartDate])
  AND MyDate < DateAdd('d', 1, DateValue([EndDate]))

If you drive the query from a form, reference the controls directly (and ensure those controls are Date/Time or converted to Date):

WHERE MyDate >= Forms!frmFilter!txtFrom
  AND MyDate < DateAdd('d', 1, Forms!frmFilter!txtTo)

Practical tips: declare your parameter types (Query > Parameters) as Date/Time to avoid type mismatches; avoid wrapping the date column in functions (that prevents index use) — convert and validate inputs instead; prefer parameterized inputs or DateSerial for literals to reduce locale issues; and handle Nulls explicitly if the date field can be empty. This pattern reliably returns all records for full calendar days and avoids excluding records because of hidden time values.

Recommended Answers

All 3 Replies

Member Avatar for boblarson

Check out my sample here:

commented: thanks boblarson +1
Member Avatar for timothybard

Here's an example:

SELECT dateValue
FROM Table1
WHERE (((dateValue) Between #1/1/2008# And #1/1/2009#));
Member Avatar for HI2Japan

Or put this in the criteria of your query

Between [Enter Start Date] And [Enter End Date]

It will prompt for the dates.

If you want it to always be set dates, type

Between #M/D/YY# AND #M/D/YY# With whatever dates you want to set.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.