By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,390 Members | 1,598 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,390 IT Pros & Developers. It's quick & easy.

Return All Records

P: n/a
How do I write the following expression so if I click through the two parameter
dialogs I get all records?

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

Thanks!

Melissa
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"Melissa" <mk****@Earthlink.net> wrote in message
news:mg***************@newsread3.news.atl.earthlin k.net...
How do I write the following expression so if I click through the two parameter dialogs I get all records?

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

in sql:

where
(someDate >= [Enter Start Date] or [Enter Start Date] Is Null)
and
(someDate <= [Enter End Date] or [Enter End Date] Is Null)
Nov 13 '05 #2

P: n/a
I just tested this, it worked.

Between Nz([Enter Start Date], DMin("[DateFieldName]", "[TableName]")) And
Nz([Enter End Date], DMax("[DateFieldName]", "[TableName]"))

--
Wayne Morgan
MS Access MVP
"Melissa" <mk****@Earthlink.net> wrote in message
news:mg***************@newsread3.news.atl.earthlin k.net...
How do I write the following expression so if I click through the two parameter dialogs I get all records?

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

Thanks!

Melissa

Nov 13 '05 #3

P: n/a
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:s9***************@newssvr24.news.prodigy.com. ..
I just tested this, it worked.

Between Nz([Enter Start Date], DMin("[DateFieldName]", "[TableName]")) And
Nz([Enter End Date], DMax("[DateFieldName]", "[TableName]"))


it might work but it's an extremely inefficient way to do it - you are
forcing possibly two extra table scans for no reason. Unless the Jet
optimizer is smarter than I think :)
Nov 13 '05 #4

P: n/a
Melissa wrote:
How do I write the following expression so if I click through the two parameter
dialogs I get all records?

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

Thanks!

Melissa


Knowing me, I'd probably create a form and scrap your parameter method.
I'd have a form with 2 date fields (from and to), a Select command
button and a Cancel command button. In the double click of each date
field I would call/present a calendar to ease data entry use. The dates
are nicely formated, may have an input mask on the form. If Cancel is
pressed, action stops. If Select is pressed write some code. Ex:

Dim strWhere As String
If Not IsNUll(Me.From) Then
strWhere = "DateField >=#" & Me.From & "# And "
Endif
If Not IsNUll(Me.To) Then
strWhere = "DateField <=#" & Me.To & "# And "
Endif
'remove AND at end of filter string
If strWhere > "" Then
strWhere = Left(strWHere,Len(strWhere)-5)
Endif

Then I'd call a form (datasheet perhaps) with your query with the
strWHere as the filter. Plus your form could add other filter parts and
you simply concatenate them to your string.

This is more powerful since you can select all records between a date,
all records if the filter is blank, all records greater/equal than the
from date, or all records less/equal the to date.

But that's my preference. Others have provided other options.

Nov 13 '05 #5

P: n/a
You are correct, I prefer your method.

--
Wayne Morgan
MS Access MVP
"John Winterbottom" <as******@hotmail.com> wrote in message
news:2h************@uni-berlin.de...
"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:s9***************@newssvr24.news.prodigy.com. ..
I just tested this, it worked.

Between Nz([Enter Start Date], DMin("[DateFieldName]", "[TableName]")) And Nz([Enter End Date], DMax("[DateFieldName]", "[TableName]"))


it might work but it's an extremely inefficient way to do it - you are
forcing possibly two extra table scans for no reason. Unless the Jet
optimizer is smarter than I think :)

Nov 13 '05 #6

P: n/a
Ok, I sorted through what you have. It relies on True being anything but
zero. Nice.

--
Wayne Morgan
MS Access MVP
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.