Agregate FUnctions in Query against Oracle 9i datasource

[Guest]

I have filter on a data/time field.

=Date() And <=[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextStartDate]

When I input this query it does produce the results between the specified
two dates. I have checked the values in the VBA immediate window for the
string and they are good.

When I remove the ">=Date() And " part, the filters produces the correct
result set.

So thier appears to be something wrong with the above bit of code.

Anyone have any ideas what could be goping wrong???

Please help.

 

[John Spencer]

I'm not sure but I suspect that the query is not able to use the
reference to the control on the subform.

<=[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextStartDate]

I would check that by hardcoding a value in place of the reference. If
using an actual date - #1/1/2008# - returns the expected records, then
I would try a simple parameter call
<= [Enter Date]

If that works then you could try putting an unbound control on the main
form that gets set to the value in the subform control. And then
reference the unbound control on the main form in your query.


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

I have filter on a data/time field.

=Date() And <=[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextStartDate]

When I input this query it does produce the results between the specified
two dates. I have checked the values in the VBA immediate window for the
string and they are good.

When I remove the ">=Date() And " part, the filters produces the correct
result set.

So thier appears to be something wrong with the above bit of code.

Anyone have any ideas what could be goping wrong???

Please help.

 

[Guest]

Thanks for the reply.

I tried the hard coding the date as follows: Between Date() And #25/03/2007#
- this worked fine.

I then tried to enter a simple parameter:

Between Date() And [enter date]

This didnt work and produced an anomolous result set.

Even when I enter

Between Date() And
[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextEndDate]

This still does not work.

Is their a setting on access relating to parameters?
--
Learning SQL and Access

I'm not sure but I suspect that the query is not able to use the
reference to the control on the subform.

<=[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextStartDate]

I would check that by hardcoding a value in place of the reference. If
using an actual date - #1/1/2008# - returns the expected records, then
I would try a simple parameter call
<= [Enter Date]

If that works then you could try putting an unbound control on the main
form that gets set to the value in the subform control. And then
reference the unbound control on the main form in your query.


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

I have filter on a data/time field.

=Date() And <=[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextStartDate]

When I input this query it does produce the results between the specified
two dates. I have checked the values in the VBA immediate window for the
string and they are good.

When I remove the ">=Date() And " part, the filters produces the correct
result set.

So thier appears to be something wrong with the above bit of code.

Anyone have any ideas what could be goping wrong???

Please help.

 

[John Spencer]

Try entering the date in yyyy-mm-dd format. Access expects dates to be in
US format of mm/dd/yyyy or in yyyy-mm-dd format

Try declaring the parameters
--Open the query in design mode
--Select Query: Parameters from the Menu
--Fill in the EXACT name of the parameter in column 1
--Select the data type of the parameter in column 2

Beyond that I am stuck. It could have something to do with the Oracle ISAM,
but I don't know.

Perhaps you could construct a pass through query to solve this problem.
That assumes that you know the SQL for Oracle.


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

Thanks for the reply.

I tried the hard coding the date as follows: Between Date() And
#25/03/2007#
- this worked fine.

I then tried to enter a simple parameter:

Between Date() And [enter date]

This didnt work and produced an anomolous result set.

Even when I enter

Between Date() And
[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextEndDate]

This still does not work.

Is their a setting on access relating to parameters?
--
Learning SQL and Access

I'm not sure but I suspect that the query is not able to use the
reference to the control on the subform.

<=[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextStartDate]

I would check that by hardcoding a value in place of the reference. If
using an actual date - #1/1/2008# - returns the expected records, then
I would try a simple parameter call
<= [Enter Date]

If that works then you could try putting an unbound control on the main
form that gets set to the value in the subform control. And then
reference the unbound control on the main form in your query.


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

I have filter on a data/time field.

=Date() And
<=[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextStartDate]

When I input this query it does produce the results between the
specified
two dates. I have checked the values in the VBA immediate window for
the
string and they are good.

When I remove the ">=Date() And " part, the filters produces the
correct
result set.

So thier appears to be something wrong with the above bit of code.

Anyone have any ideas what could be goping wrong???

Please help.

 

[Guest]

I believe I hae the suspect.

I am attempting to perform the agg' function on a field that is already
making a calculation.

Eg. the field name has the following code:

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

Then in the criteria I am attmepting to add the Between DateX And DateY.

When I make a table from the query with the field calc I can then perform
the Dates Agg' function on that field.

Is their a way to use the Agg' functions on a Query field that is already
making a calc???

I dont want to use a MkTable query first because the resultset is too large!!

Please help

--
Learning SQL and Access

Try entering the date in yyyy-mm-dd format. Access expects dates to be in
US format of mm/dd/yyyy or in yyyy-mm-dd format

Try declaring the parameters
--Open the query in design mode
--Select Query: Parameters from the Menu
--Fill in the EXACT name of the parameter in column 1
--Select the data type of the parameter in column 2

Beyond that I am stuck. It could have something to do with the Oracle ISAM,
but I don't know.

Perhaps you could construct a pass through query to solve this problem.
That assumes that you know the SQL for Oracle.


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

Thanks for the reply.

I tried the hard coding the date as follows: Between Date() And
#25/03/2007#
- this worked fine.

I then tried to enter a simple parameter:

Between Date() And [enter date]

This didnt work and produced an anomolous result set.

Even when I enter

Between Date() And
[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextEndDate]

This still does not work.

Is their a setting on access relating to parameters?
--
Learning SQL and Access

I'm not sure but I suspect that the query is not able to use the
reference to the control on the subform.

<=[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextStartDate]

I would check that by hardcoding a value in place of the reference. If
using an actual date - #1/1/2008# - returns the expected records, then
I would try a simple parameter call
<= [Enter Date]

If that works then you could try putting an unbound control on the main
form that gets set to the value in the subform control. And then
reference the unbound control on the main form in your query.


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


stephenson22 wrote:
I have filter on a data/time field.

=Date() And
<=[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextStartDate]

When I input this query it does produce the results between the
specified
two dates. I have checked the values in the VBA immediate window for
the
string and they are good.

When I remove the ">=Date() And " part, the filters produces the
correct
result set.

So thier appears to be something wrong with the above bit of code.

Anyone have any ideas what could be goping wrong???

Please help.

 

[John Spencer]

Try using the following expression to return a date value or Null if
DateApVal is ever null.

ExpiryPeriod: DateAdd("d", IIF(Val(Left(NZ(DTypNumbCo,"0"),2)) Between 1 and
5, 91, 56),DateApVal)

This adds 91 or 56 to the date value and returns a date value.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I believe I hae the suspect.

I am attempting to perform the agg' function on a field that is already
making a calculation.

Eg. the field name has the following code:

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

Then in the criteria I am attmepting to add the Between DateX And DateY.

When I make a table from the query with the field calc I can then perform
the Dates Agg' function on that field.

Is their a way to use the Agg' functions on a Query field that is already
making a calc???

I dont want to use a MkTable query first because the resultset is too
large!!

Please help

--
Learning SQL and Access

Try entering the date in yyyy-mm-dd format. Access expects dates to be
in
US format of mm/dd/yyyy or in yyyy-mm-dd format

Try declaring the parameters
--Open the query in design mode
--Select Query: Parameters from the Menu
--Fill in the EXACT name of the parameter in column 1
--Select the data type of the parameter in column 2

Beyond that I am stuck. It could have something to do with the Oracle
ISAM,
but I don't know.

Perhaps you could construct a pass through query to solve this problem.
That assumes that you know the SQL for Oracle.


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

Thanks for the reply.

I tried the hard coding the date as follows: Between Date() And
#25/03/2007#
- this worked fine.

I then tried to enter a simple parameter:

Between Date() And [enter date]

This didnt work and produced an anomolous result set.

Even when I enter

Between Date() And
[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextEndDate]

This still does not work.

Is their a setting on access relating to parameters?
--
Learning SQL and Access


:

I'm not sure but I suspect that the query is not able to use the
reference to the control on the subform.

<=[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextStartDate]

I would check that by hardcoding a value in place of the reference.
If
using an actual date - #1/1/2008# - returns the expected records,
then
I would try a simple parameter call
<= [Enter Date]

If that works then you could try putting an unbound control on the
main
form that gets set to the value in the subform control. And then
reference the unbound control on the main form in your query.


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


stephenson22 wrote:
I have filter on a data/time field.

=Date() And
<=[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextStartDate]

When I input this query it does produce the results between the
specified
two dates. I have checked the values in the VBA immediate window
for
the
string and they are good.

When I remove the ">=Date() And " part, the filters produces the
correct
result set.

So thier appears to be something wrong with the above bit of code.

Anyone have any ideas what could be goping wrong???

Please help.

 

[Guest]

I will try, but then do you think I will be able to perform the date fucntion
in the criteria section of the query underneath your code?
--
Learning SQL and Access

Try using the following expression to return a date value or Null if
DateApVal is ever null.

ExpiryPeriod: DateAdd("d", IIF(Val(Left(NZ(DTypNumbCo,"0"),2)) Between 1 and
5, 91, 56),DateApVal)

This adds 91 or 56 to the date value and returns a date value.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I believe I hae the suspect.

I am attempting to perform the agg' function on a field that is already
making a calculation.

Eg. the field name has the following code:

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

Then in the criteria I am attmepting to add the Between DateX And DateY.

When I make a table from the query with the field calc I can then perform
the Dates Agg' function on that field.

Is their a way to use the Agg' functions on a Query field that is already
making a calc???

I dont want to use a MkTable query first because the resultset is too
large!!

Please help

--
Learning SQL and Access

Try entering the date in yyyy-mm-dd format. Access expects dates to be
in
US format of mm/dd/yyyy or in yyyy-mm-dd format

Try declaring the parameters
--Open the query in design mode
--Select Query: Parameters from the Menu
--Fill in the EXACT name of the parameter in column 1
--Select the data type of the parameter in column 2

Beyond that I am stuck. It could have something to do with the Oracle
ISAM,
but I don't know.

Perhaps you could construct a pass through query to solve this problem.
That assumes that you know the SQL for Oracle.


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

Thanks for the reply.

I tried the hard coding the date as follows: Between Date() And
#25/03/2007#
- this worked fine.

I then tried to enter a simple parameter:

Between Date() And [enter date]

This didnt work and produced an anomolous result set.

Even when I enter

Between Date() And
[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextEndDate]

This still does not work.

Is their a setting on access relating to parameters?
--
Learning SQL and Access


:

I'm not sure but I suspect that the query is not able to use the
reference to the control on the subform.

<=[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextStartDate]

I would check that by hardcoding a value in place of the reference.
If
using an actual date - #1/1/2008# - returns the expected records,
then
I would try a simple parameter call
<= [Enter Date]

If that works then you could try putting an unbound control on the
main
form that gets set to the value in the subform control. And then
reference the unbound control on the main form in your query.


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


stephenson22 wrote:
I have filter on a data/time field.

=Date() And
<=[Forms]![frm_dc_main_menu]![frm_dateSelection].[Form]![TextStartDate]

When I input this query it does produce the results between the
specified
two dates. I have checked the values in the VBA immediate window
for
the
string and they are good.

When I remove the ">=Date() And " part, the filters produces the
correct
result set.

So thier appears to be something wrong with the above bit of code.

Anyone have any ideas what could be goping wrong???

Please help.