Query against dates


G

Guest

Ken,

My bad, that was Karl's statement...

I didn't try to implement your code since our technology department doesn't
'allow' us to build in SQL - the want exclusive rights to that, so they make
us build our queries in the QBE grid.

....don't ask why, no-one knows the logic behind THAT genius decision - not
even our technology management!

Ken Snell (MVP) said:
No. This is what I posted -- no comment about "dropping down" or such
terminology, just a generic SQL statement:
------

Something like this (I'm using generic names for form and fields and table):

PARAMETERS [Forms]![YourFormName]![StartDate] DateTime,
[Forms]![YourFormName]![EndDate] DateTime;
SELECT * FROM TableName
WHERE StartDateField >= IIf([Forms]![YourFormName]![StartDate] Is Null,
#1/1/1900#,[Forms]![YourFormName]![StartDate]) And
EndDateField <= IIf([Forms]![YourFormName]![EndDate] Is Null,
#12/31/2999#,[Forms]![YourFormName]![EndDate]);

--

Ken Snell
<MS ACCESS MVP>

Robert_L_Ross said:
Close, but you said "Then drop down a row and enter the second criteria.
Drop down another row for the third and so forth."

That was causing too many results to return...

Ken Snell (MVP) said:
As it turns out, as I was tinkering I think I found out a solution that
works.
Start Date Criteria is:
<=IIf(IsNull([Forms]![Lender
Status]![EndDate]),#12/31/9999#,[Forms]![Lender
Status]![EndDate])

AND

End Date criteria is:
=IIf(IsNull([Forms]![Lender
Status]![StartDate]),#01/01/1900#,[Forms]![Lender Status]![StartDate])

Since it's an AND ...

Looks pretty similar to what I suggested in my reply dated 7 September <
smile >.....
 
Ad

Advertisements

K

Ken Snell \(MVP\)

< chuckle > "Who knows what evil lurks in the minds of [IT]? The Shadow
knows!" (paraphrasing a tag line from an old radio show here in the US many
years ago)

No problem! If I'd known about that SQL use limitation, I would have changed
the recommendation to a description of how to create the query in the grid
mode.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top