FIlter on filed making a calulation

[stephenson22]

Dear All,

I have a field in a query e.g: FILEDA:
IIf([dateX]>#22/01/2007#,[dateX]+20,[dateX]+30)

I would then like to place a filter in the criteria section of the design
grid underneath this filed with:

Between Date1 And Date2

However, this does not work.

When I insert a single date as the criteria, ie, "Date1" the query acts on
the filter. Its only when I introduce a 2nd parameter that I get anomolous
results.

I have tired the following with no joy:

* to base the a new query on that field, then insert the criteria
* to inserte >= and <= instead of the "between" function

The following method worked - but I dont want to do this.

* Create a makeTable query then insert the criteria on the filed of the new
table - but this is not an option because the first result set is too large.

I've tried to provide as much info as I can to enable a genius to help me.

Please help.

 

[stephenson22]

Is anyone abole to help with this issue???

MVP's where are you????

 

[John Spencer]

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

That said the fix may be as simple as forcing the data type. If dateX
ALWAYS has a value then try
FILEDA: CDate(IIf([dateX]>#22/01/2007#,[dateX]+20,[dateX]+30))

If dateX can be null then try
FILEDA:
IIF(IsDate(dateX),CDate(IIf([dateX]>#22/01/2007#,[dateX]+20,[dateX]+30)),Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[stephenson22]

I have narrowed down the problem area.

Heres the code:

SELECT IIf((Nz(Left([DTYPNUMBCO],2),"0") Between 1 And
5),[DATEAPVAL]+91,[DATEAPVAL]+56) AS EXPIRYPERIOD

FROM UNI73LIVE_DCAPPL

WHERE

(((IIf((Nz(Left([DTYPNUMBCO],2),"0") Between 1 And
5),[DATEAPVAL]+91,[DATEAPVAL]+56)) Between
[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextStartDate] And
[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextEndDate]));


When I replace the WHERE clause with the actual dates instead of the vba
form reference the results are correct, eg.

Between #27/03/2007# And #28/03/2007#

When I user the immediate window to produce the result of each parameter
using the "?" in front of each
"[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextEndDate]" I
receive the correct dates as above in the "Between" statement.

So I guess the problem relates to the way access is passing the values from
the text box on the Form to the "Between" statement.

Any ideas?

--
Learning SQL and Access

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

That said the fix may be as simple as forcing the data type. If dateX
ALWAYS has a value then try
FILEDA: CDate(IIf([dateX]>#22/01/2007#,[dateX]+20,[dateX]+30))

If dateX can be null then try
FILEDA:
IIF(IsDate(dateX),CDate(IIf([dateX]>#22/01/2007#,[dateX]+20,[dateX]+30)),Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dear All,

I have a field in a query e.g: FILEDA:
IIf([dateX]>#22/01/2007#,[dateX]+20,[dateX]+30)

I would then like to place a filter in the criteria section of the design
grid underneath this filed with:

Between Date1 And Date2

However, this does not work.

When I insert a single date as the criteria, ie, "Date1" the query acts on
the filter. Its only when I introduce a 2nd parameter that I get
anomolous
results.

I have tired the following with no joy:

* to base the a new query on that field, then insert the criteria
* to inserte >= and <= instead of the "between" function

The following method worked - but I dont want to do this.

* Create a makeTable query then insert the criteria on the filed of the
new
table - but this is not an option because the first result set is too
large.

I've tried to provide as much info as I can to enable a genius to help me.

Please help.

 

[John Spencer]

As far as I know, you cannot reference controls on a subform in a query. As
an experiment try

WHERE

(((IIf((Nz(Left([DTYPNUMBCO],2),0) Between 1 And
5),[DATEAPVAL]+91,[DATEAPVAL]+56)) Between
CDate([Enter Start Date]) And CDate([Enter End Date])

If that works, then you know that you need to work out another way of
getting the desired values. Perhaps by setting an invisible unbound control
on the main form to the value on the subform.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have narrowed down the problem area.

Heres the code:

SELECT IIf((Nz(Left([DTYPNUMBCO],2),"0") Between 1 And
5),[DATEAPVAL]+91,[DATEAPVAL]+56) AS EXPIRYPERIOD

FROM UNI73LIVE_DCAPPL

WHERE

(((IIf((Nz(Left([DTYPNUMBCO],2),"0") Between 1 And
5),[DATEAPVAL]+91,[DATEAPVAL]+56)) Between
[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextStartDate] And
[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextEndDate]));


When I replace the WHERE clause with the actual dates instead of the vba
form reference the results are correct, eg.

Between #27/03/2007# And #28/03/2007#

When I user the immediate window to produce the result of each parameter
using the "?" in front of each
"[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextEndDate]" I
receive the correct dates as above in the "Between" statement.

So I guess the problem relates to the way access is passing the values
from
the text box on the Form to the "Between" statement.

Any ideas?

--
Learning SQL and Access

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

That said the fix may be as simple as forcing the data type. If dateX
ALWAYS has a value then try
FILEDA: CDate(IIf([dateX]>#22/01/2007#,[dateX]+20,[dateX]+30))

If dateX can be null then try
FILEDA:
IIF(IsDate(dateX),CDate(IIf([dateX]>#22/01/2007#,[dateX]+20,[dateX]+30)),Null)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dear All,

I have a field in a query e.g: FILEDA:
IIf([dateX]>#22/01/2007#,[dateX]+20,[dateX]+30)

I would then like to place a filter in the criteria section of the
design
grid underneath this filed with:

Between Date1 And Date2

However, this does not work.

When I insert a single date as the criteria, ie, "Date1" the query acts
on
the filter. Its only when I introduce a 2nd parameter that I get
anomolous
results.

I have tired the following with no joy:

* to base the a new query on that field, then insert the criteria
* to inserte >= and <= instead of the "between" function

The following method worked - but I dont want to do this.

* Create a makeTable query then insert the criteria on the filed of the
new
table - but this is not an option because the first result set is too
large.

I've tried to provide as much info as I can to enable a genius to help
me.

Please help.

 

[Rick Brandt]

As far as I know, you cannot reference controls on a subform in a
query.

Huh? I do that a lot and have never had problems. My guess is that Access
is not treating those references as dates. Explicitly defining them as such
in the parameters dialog should solve that problem.

 

[John Spencer]

You are right. I am wrong.

I think that this was true in Access 97 and I've never retested it. Even
then I could be wrong about it not working in Access 97 and I am remembering
that which did not exist.

Ah, well - A new trick for my arsenal.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..