Refering to date fields in a form.

  • Thread starter Thread starter Kjell Harnesk
  • Start date Start date
K

Kjell Harnesk

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.
 
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.
 
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.
--
KARL DEWEY
Build a little - Test a little


akphidelt said:
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.


Kjell Harnesk said:
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.
Click to expand...
Click to expand...
 
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
 
akphidelt said:
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
Click to expand...

Thank You!
 
Back
Top