Refering to date fields in a form.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kjell Harnesk - 17 Apr 2008 16:46 GMT

I have a form with 3 controls involved in my problem.
Let's call them "UseDdates" this is a checkbox and two date-controls
"Stardate" and "Enddate".
In the query by design window I want to use the date-field of my query to
limit to start- and enddates
if the chechbox is checked. If not checked I want to use all dates.

I have tried a trillion of combinations like:
iif(myform.UseDates=-1; between myform.startdate and myform.enddate;between
#1900-01-01# and #2030-01-01#)
Can't get it to work, I would appreciate help on this problem.

--
Kjell Harnesk, Sweden

Reply to this Message

akphidelt - 17 Apr 2008 20:04 GMT

In my experience, not sure if it's even allowed, I've never been able to do
that using the queries criteria for the date. What I do is a work around

You go to a blank column and type in

DateCheck: IIF(Forms!myform.UseDates=-1,[YourDateField],0)

This says that if it is checked then put in the date from the date field of
your current query. If it is not checked put in 0.

Then you'll create 2 criterias underneat this new column you made

Between Forms!myform.StartDate and Forms!myform.EndDate

The second criteria type in

0

You can then hide this column show it doesn't show up. What this does is if
it is not checked it gives every item the same number... so when it isn't
checked you get every date in your query... if it is checked it shows all the
dates and only selects between the dates on your form.

> I have a form with 3 controls involved in my problem.
> Let's call them "UseDdates" this is a checkbox and two date-controls
[quoted text clipped - 10 lines]
> --
> Kjell Harnesk, Sweden

Reply to this Message

KARL DEWEY - 17 Apr 2008 20:34 GMT

Just worked up the SQL so as not to waste my time I am posting --
SELECT [YourTableName].*
FROM [YourTableName]
WHERE ((([YourTableName].[YourDateField]) Between
[Forms]![YourFormName]![Stardate] And [Forms]![YourFormName]![Enddate])) OR
(((-1)=[Forms]![YourFormName]![YourCheckbox]));

Substitute table and field names.

Signature

KARL DEWEY
Build a little - Test a little

> In my experience, not sure if it's even allowed, I've never been able to do
> that using the queries criteria for the date. What I do is a work around
[quoted text clipped - 33 lines]
> > --
> > Kjell Harnesk, Sweden

Reply to this Message

Ron2006 - 17 Apr 2008 21:26 GMT

An alternative that I have sometime used, is to change the form:

Have the default from date be #01/01/2000# (or some date earlier that
any data can be)
Have the default to date be date()
have the two fields locked.
If they check the chkbox then make the fields unlocked.
if they then uncheck the chkbox then reset the dates to the default
dates. - if you want.

Then always use the normal between forms etc stardate and forms etc
thrudate.

Ron

Reply to this Message

Kjell Harnesk - 18 Apr 2008 20:05 GMT

> In my experience, not sure if it's even allowed, I've never been able to
> do
[quoted text clipped - 15 lines]
>
> 0

Thank You!

Reply to this Message