dates and expressions

[stephanie]

I have an IIF expression that combines three date fields.
Can I set criteria to find dates between two dates, such
as: Between [forms]![event report search]![beginning
date] And [forms]![event report search]![ending date]??
If so, how. I tried to put it in the Expr1 field.
Doesn't work. Tried to put it into each of the individual
fields.. Didn't work. Any ideas?

Steph

 

[Ken Snell]

Yes, the Between [ ] And [ ] syntax can be used. Can you give us a bit
more detailed info about your setup so that we might offer some suggestions?

 

[John Vinson]

I have an IIF expression that combines three date fields.
Can I set criteria to find dates between two dates, such
as: Between [forms]![event report search]![beginning
date] And [forms]![event report search]![ending date]??
If so, how. I tried to put it in the Expr1 field.
Doesn't work. Tried to put it into each of the individual
fields.. Didn't work. Any ideas?

Steph

An IIF? or a Query? Please post the actual context.

If you have a Date/Time field in the Query then putting your BETWEEN
expression on the criteria line under the expression should work...
but without knowing the expression, it's hard to say just what the
syntax should be.

 

[John Spencer (MVP)]

Yes, it can probably be done. HOWEVER, without more details...

Can you show us the IIF expression?

Can you post The SQL with or without the Criteria?

"Doesn't work" is rather vague. It can mean:
The SQL returned NO records
The SQL returned the WRONG records
The SQL just causes an error to pop-up
The SQL returned EVERY record.

Help us, help you by giving a little more detail.

 

[stephanie]

This is the expression I have. It combines three date
fields, one of which shows just year and month (when I
type it in, I type in month, day, year. I tried to show a
range under all fields. I can do it for one field, but
not the expression or more than one date field.

Expr1: IIf(IsNull([assigned]),Format([Due],"yyyy-mm-dd") &
Format([Estimate],"yyyy-mm"),Format([Assigned],"yyyy-mm-
dd") & ' - ' & Format([Due],"yyyy-mm-dd"))

Thanks for your help,
Steph

-----Original Message-----
Yes, the Between [ ] And [ ] syntax can be used. Can you give us a bit
more detailed info about your setup so that we might offer some suggestions?

--
Ken Snell
<MS ACCESS MVP>

I have an IIF expression that combines three date fields.
Can I set criteria to find dates between two dates, such
as: Between [forms]![event report search]![beginning
date] And [forms]![event report search]![ending date]??
If so, how. I tried to put it in the Expr1 field.
Doesn't work. Tried to put it into each of the individual
fields.. Didn't work. Any ideas?

Steph

.

 

[John Vinson]

This is the expression I have. It combines three date
fields, one of which shows just year and month (when I
type it in, I type in month, day, year. I tried to show a
range under all fields. I can do it for one field, but
not the expression or more than one date field.

Expr1: IIf(IsNull([assigned]),Format([Due],"yyyy-mm-dd") &
Format([Estimate],"yyyy-mm"),Format([Assigned],"yyyy-mm-
dd") & ' - ' & Format([Due],"yyyy-mm-dd"))

This expression returns a Text String, not a date. For instance, if
Due is December 1, 2003 and Estimate is November, 2003, it will return
the text string

2003-12-012003-11

which isn't going to be particularly searchable in any sense, and
certainly will fail on a date range search!

Could you explain the logic of what you're trying to accomplish? I'm
sure a criterion can be devised to do it... but the way you're doing
it isn't going to work!

 

[stephanie]

This is the expression I have. I want to be able to use
this: Between [forms]![Event Report Search]![beginning
date] And [forms]![Event Report Search]![ending date]. I
can use it in an individual field, but not for the
expression.

Expr1: IIf(IsNull([assigned]),Format([Due],"yyyy-mm-dd") &
Format([Estimate],"yyyy-mm"),Format([Assigned],"yyyy-mm-
dd") & ' - ' & Format([Due],"yyyy-mm-dd"))

Steph

-----Original Message-----
Yes, the Between [ ] And [ ] syntax can be used. Can you give us a bit
more detailed info about your setup so that we might offer some suggestions?

--
Ken Snell
<MS ACCESS MVP>

I have an IIF expression that combines three date fields.
Can I set criteria to find dates between two dates, such
as: Between [forms]![event report search]![beginning
date] And [forms]![event report search]![ending date]??
If so, how. I tried to put it in the Expr1 field.
Doesn't work. Tried to put it into each of the individual
fields.. Didn't work. Any ideas?

Steph

.