QUERY PARAMETER

[Guest]

Hi,

I have this query that has a calculated field thus:
DTD:
Nz(DateDiff("d",[DateAcquired],Forms!AssetsGeneralDepreciationSchedule!AsAt),0)*[EstimatedValue]*DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]=" & [AssetCategory])

When I run it, it asks for the value of:
Forms!AssetsGeneralDepreciationSchedule!AsAt. If I supply it, say today's
date, expected values are returned. Otherwise, 0 is returned on account of
the NZ function. So far so good.

On the form named AssetsGeneralDepreciationSchedule, on which the textbox
AsAt (formated to dd-mmm-yyyy) resides, however, the value consistently
returned is 0. It appears that the query does not recognize
Forms!AssetsGeneralDepreciationSchedule!AsAt on the form itself.

What am I doing wrong?

 

[Arvin Meyer [MVP]]

That's because SQL only recognizes the format as mm/dd/yy or mm/dd/yyyy
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have this query that has a calculated field thus:
DTD:

Nz(DateDiff("d",[DateAcquired],Forms!AssetsGeneralDepreciationSchedule!AsAt)
,0)*[EstimatedValue]*DLookUp("[DepreciationRate]/365","AssetsGeneralCategory
","[CategoryID]=" & [AssetCategory])

 

[Guest]

Thanks, Arvin.

But perhaps the problem is not quite due to the date format because the
query runs with any valid date format while the form returns zero (I assume
on account of the NZ function) regardless of the date format of the AsAt
textbox.

Please look again and suggest another solution.
--
Glint

That's because SQL only recognizes the format as mm/dd/yy or mm/dd/yyyy
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Hi,

I have this query that has a calculated field thus:
DTD:

Nz(DateDiff("d",[DateAcquired],Forms!AssetsGeneralDepreciationSchedule!AsAt)
,0)*[EstimatedValue]*DLookUp("[DepreciationRate]/365","AssetsGeneralCategory
","[CategoryID]=" & [AssetCategory])

When I run it, it asks for the value of:
Forms!AssetsGeneralDepreciationSchedule!AsAt. If I supply it, say today's
date, expected values are returned. Otherwise, 0 is returned on account of
the NZ function. So far so good.

On the form named AssetsGeneralDepreciationSchedule, on which the textbox
AsAt (formated to dd-mmm-yyyy) resides, however, the value consistently
returned is 0. It appears that the query does not recognize
Forms!AssetsGeneralDepreciationSchedule!AsAt on the form itself.

What am I doing wrong?

 

[John Vinson]

When I run it, it asks for the value of:
Forms!AssetsGeneralDepreciationSchedule!AsAt. If I supply it, say today's
date, expected values are returned. Otherwise, 0 is returned on account of
the NZ function. So far so good.

Is AssetsGeneralDepreciationSchedule perhaps being used as a Subform?
If so, it's not part of the Forms collection and not available to
queries; you need to navigate via the mainform.

John W. Vinson[MVP]

 

[Guest]

Try this

Nz(DateDiff("d",[DateAcquired], #" &
[Forms]![AssetsGeneralDepreciationSchedule]![AsAt] &
"#),0)*[EstimatedValue]*DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]=" & [AssetCategory])

Regards,
Bizpro

 

[Guest]

Thanx for your advice.

To answer John Vinson's question on wether the form is a subform: No, it is
a main form. The query was built with the query builder and was then used as
the record source of the form.

I have tried bizpro's suggestion. It was rejected as invalid date syntax.
The interesting thing is that the query works perfectly when the
AssetsGeneralDepreciationSchedule form is open, even though the form itslef
does not. When the form is not open, the query asks for the value of
Forms!AssetsGeneralDepreciationSchedule!AsAt. Once a valid date is supplied,
it works well.

So what is the correct way to write the code so that the underlying query of
a form automatically takes its input parameter from the form?
--
Glint

Try this

Nz(DateDiff("d",[DateAcquired], #" &
[Forms]![AssetsGeneralDepreciationSchedule]![AsAt] &
"#),0)*[EstimatedValue]*DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]=" & [AssetCategory])

Regards,
Bizpro

Hi,

I have this query that has a calculated field thus:
DTD:
Nz(DateDiff("d",[DateAcquired],Forms!AssetsGeneralDepreciationSchedule!AsAt),0)*[EstimatedValue]*DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]=" & [AssetCategory])

When I run it, it asks for the value of:
Forms!AssetsGeneralDepreciationSchedule!AsAt. If I supply it, say today's
date, expected values are returned. Otherwise, 0 is returned on account of
the NZ function. So far so good.

On the form named AssetsGeneralDepreciationSchedule, on which the textbox
AsAt (formated to dd-mmm-yyyy) resides, however, the value consistently
returned is 0. It appears that the query does not recognize
Forms!AssetsGeneralDepreciationSchedule!AsAt on the form itself.

What am I doing wrong?

 

[Guest]

Sorry Glint, I didn't read the post properly and assumed you were working on
a SQL in VB.

You say the query runs with no problem when the form is open - thats because
it can "see" the object of the query;
Forms!AssetsGeneralDepreciationSchedule!AsAt textbox. Nothing wrong with the
syntax. However, if the form is not open, it can't see the textbox its
looking for and therefore pops up the parameter box for a value. If you wish
to run the query without the form open then you should query the table that
supplies data for the form instead of the form itself.

Thanx for your advice.

To answer John Vinson's question on wether the form is a subform: No, it is
a main form. The query was built with the query builder and was then used as
the record source of the form.

I have tried bizpro's suggestion. It was rejected as invalid date syntax.
The interesting thing is that the query works perfectly when the
AssetsGeneralDepreciationSchedule form is open, even though the form itslef
does not. When the form is not open, the query asks for the value of
Forms!AssetsGeneralDepreciationSchedule!AsAt. Once a valid date is supplied,
it works well.

So what is the correct way to write the code so that the underlying query of
a form automatically takes its input parameter from the form?
--
Glint

Try this

Nz(DateDiff("d",[DateAcquired], #" &
[Forms]![AssetsGeneralDepreciationSchedule]![AsAt] &
"#),0)*[EstimatedValue]*DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]=" & [AssetCategory])

Regards,
Bizpro

Hi,

I have this query that has a calculated field thus:
DTD:
Nz(DateDiff("d",[DateAcquired],Forms!AssetsGeneralDepreciationSchedule!AsAt),0)*[EstimatedValue]*DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]=" & [AssetCategory])

When I run it, it asks for the value of:
Forms!AssetsGeneralDepreciationSchedule!AsAt. If I supply it, say today's
date, expected values are returned. Otherwise, 0 is returned on account of
the NZ function. So far so good.

On the form named AssetsGeneralDepreciationSchedule, on which the textbox
AsAt (formated to dd-mmm-yyyy) resides, however, the value consistently
returned is 0. It appears that the query does not recognize
Forms!AssetsGeneralDepreciationSchedule!AsAt on the form itself.

What am I doing wrong?

 

[Guest]

Thanx.

The problem is that when I open the form, the value displayed on the DTD
field is 0, while the query runs well. I am assuming that the form does not
detect the presence of the AsAt textbox (an unbound control which defaults to
system date but can be changed by user) on itself, hence it treats that value
as a null and returns 0. But the query returns normal results with form
opened, or if one supplies a valid date for the AsAt textbox when the form is
closed.

That is why I am wondering if the syntax for the form should be different
from the query.
--
Glint

Sorry Glint, I didn't read the post properly and assumed you were working on
a SQL in VB.

You say the query runs with no problem when the form is open - thats because
it can "see" the object of the query;
Forms!AssetsGeneralDepreciationSchedule!AsAt textbox. Nothing wrong with the
syntax. However, if the form is not open, it can't see the textbox its
looking for and therefore pops up the parameter box for a value. If you wish
to run the query without the form open then you should query the table that
supplies data for the form instead of the form itself.

Thanx for your advice.

To answer John Vinson's question on wether the form is a subform: No, it is
a main form. The query was built with the query builder and was then used as
the record source of the form.

I have tried bizpro's suggestion. It was rejected as invalid date syntax.
The interesting thing is that the query works perfectly when the
AssetsGeneralDepreciationSchedule form is open, even though the form itslef
does not. When the form is not open, the query asks for the value of
Forms!AssetsGeneralDepreciationSchedule!AsAt. Once a valid date is supplied,
it works well.

So what is the correct way to write the code so that the underlying query of
a form automatically takes its input parameter from the form?
--
Glint

Try this

Nz(DateDiff("d",[DateAcquired], #" &
[Forms]![AssetsGeneralDepreciationSchedule]![AsAt] &
"#),0)*[EstimatedValue]*DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]=" & [AssetCategory])

Regards,
Bizpro

:

Hi,

I have this query that has a calculated field thus:
DTD:
Nz(DateDiff("d",[DateAcquired],Forms!AssetsGeneralDepreciationSchedule!AsAt),0)*[EstimatedValue]*DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]=" & [AssetCategory])

When I run it, it asks for the value of:
Forms!AssetsGeneralDepreciationSchedule!AsAt. If I supply it, say today's
date, expected values are returned. Otherwise, 0 is returned on account of
the NZ function. So far so good.

On the form named AssetsGeneralDepreciationSchedule, on which the textbox
AsAt (formated to dd-mmm-yyyy) resides, however, the value consistently
returned is 0. It appears that the query does not recognize
Forms!AssetsGeneralDepreciationSchedule!AsAt on the form itself.

What am I doing wrong?

 

[Guest]

I think that you have hit on the answer.

Here is how I would approach it.
1. Query the underlying table to get the recordsource.
2. Openform using recordsource
3. Run new query using the form as the parameter values for the new query

Regards,

Bizpro

Thanx.

The problem is that when I open the form, the value displayed on the DTD
field is 0, while the query runs well. I am assuming that the form does not
detect the presence of the AsAt textbox (an unbound control which defaults to
system date but can be changed by user) on itself, hence it treats that value
as a null and returns 0. But the query returns normal results with form
opened, or if one supplies a valid date for the AsAt textbox when the form is
closed.

That is why I am wondering if the syntax for the form should be different
from the query.
--
Glint

Sorry Glint, I didn't read the post properly and assumed you were working on
a SQL in VB.

You say the query runs with no problem when the form is open - thats because
it can "see" the object of the query;
Forms!AssetsGeneralDepreciationSchedule!AsAt textbox. Nothing wrong with the
syntax. However, if the form is not open, it can't see the textbox its
looking for and therefore pops up the parameter box for a value. If you wish
to run the query without the form open then you should query the table that
supplies data for the form instead of the form itself.

Thanx for your advice.

To answer John Vinson's question on wether the form is a subform: No, it is
a main form. The query was built with the query builder and was then used as
the record source of the form.

I have tried bizpro's suggestion. It was rejected as invalid date syntax.
The interesting thing is that the query works perfectly when the
AssetsGeneralDepreciationSchedule form is open, even though the form itslef
does not. When the form is not open, the query asks for the value of
Forms!AssetsGeneralDepreciationSchedule!AsAt. Once a valid date is supplied,
it works well.

So what is the correct way to write the code so that the underlying query of
a form automatically takes its input parameter from the form?
--
Glint


:

Try this

Nz(DateDiff("d",[DateAcquired], #" &
[Forms]![AssetsGeneralDepreciationSchedule]![AsAt] &
"#),0)*[EstimatedValue]*DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]=" & [AssetCategory])

Regards,
Bizpro

:

Hi,

I have this query that has a calculated field thus:
DTD:
Nz(DateDiff("d",[DateAcquired],Forms!AssetsGeneralDepreciationSchedule!AsAt),0)*[EstimatedValue]*DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]=" & [AssetCategory])

When I run it, it asks for the value of:
Forms!AssetsGeneralDepreciationSchedule!AsAt. If I supply it, say today's
date, expected values are returned. Otherwise, 0 is returned on account of
the NZ function. So far so good.

On the form named AssetsGeneralDepreciationSchedule, on which the textbox
AsAt (formated to dd-mmm-yyyy) resides, however, the value consistently
returned is 0. It appears that the query does not recognize
Forms!AssetsGeneralDepreciationSchedule!AsAt on the form itself.

What am I doing wrong?

 

[Guest]

Thanks bizpro,

I "accidentally" found that the form did work, albeit unreliably, after all.
It would display 0 for the DTD on load, but as soon as I filtered the
recordset, correct values would come up. I have no idea why it does this
(that is, why it would not display correct values on load) .

So meanwhile, I just filter (to get all records to start with) as it is
loading, and all is well subsequently.

I am grateful for your time and effort.
--
Glint

I think that you have hit on the answer.

Here is how I would approach it.
1. Query the underlying table to get the recordsource.
2. Openform using recordsource
3. Run new query using the form as the parameter values for the new query

Regards,

Bizpro

Thanx.

The problem is that when I open the form, the value displayed on the DTD
field is 0, while the query runs well. I am assuming that the form does not
detect the presence of the AsAt textbox (an unbound control which defaults to
system date but can be changed by user) on itself, hence it treats that value
as a null and returns 0. But the query returns normal results with form
opened, or if one supplies a valid date for the AsAt textbox when the form is
closed.

That is why I am wondering if the syntax for the form should be different
from the query.
--
Glint

Sorry Glint, I didn't read the post properly and assumed you were working on
a SQL in VB.

You say the query runs with no problem when the form is open - thats because
it can "see" the object of the query;
Forms!AssetsGeneralDepreciationSchedule!AsAt textbox. Nothing wrong with the
syntax. However, if the form is not open, it can't see the textbox its
looking for and therefore pops up the parameter box for a value. If you wish
to run the query without the form open then you should query the table that
supplies data for the form instead of the form itself.


:

Thanx for your advice.

To answer John Vinson's question on wether the form is a subform: No, it is
a main form. The query was built with the query builder and was then used as
the record source of the form.

I have tried bizpro's suggestion. It was rejected as invalid date syntax.
The interesting thing is that the query works perfectly when the
AssetsGeneralDepreciationSchedule form is open, even though the form itslef
does not. When the form is not open, the query asks for the value of
Forms!AssetsGeneralDepreciationSchedule!AsAt. Once a valid date is supplied,
it works well.

So what is the correct way to write the code so that the underlying query of
a form automatically takes its input parameter from the form?
--
Glint


:

Try this

Nz(DateDiff("d",[DateAcquired], #" &
[Forms]![AssetsGeneralDepreciationSchedule]![AsAt] &
"#),0)*[EstimatedValue]*DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]=" & [AssetCategory])

Regards,
Bizpro

:

Hi,

I have this query that has a calculated field thus:
DTD:
Nz(DateDiff("d",[DateAcquired],Forms!AssetsGeneralDepreciationSchedule!AsAt),0)*[EstimatedValue]*DLookUp("[DepreciationRate]/365","AssetsGeneralCategory","[CategoryID]=" & [AssetCategory])

When I run it, it asks for the value of:
Forms!AssetsGeneralDepreciationSchedule!AsAt. If I supply it, say today's
date, expected values are returned. Otherwise, 0 is returned on account of
the NZ function. So far so good.

On the form named AssetsGeneralDepreciationSchedule, on which the textbox
AsAt (formated to dd-mmm-yyyy) resides, however, the value consistently
returned is 0. It appears that the query does not recognize
Forms!AssetsGeneralDepreciationSchedule!AsAt on the form itself.

What am I doing wrong?

 

[John Vinson]

I "accidentally" found that the form did work, albeit unreliably, after all.
It would display 0 for the DTD on load, but as soon as I filtered the
recordset, correct values would come up. I have no idea why it does this
(that is, why it would not display correct values on load) .

This can happen when you don't take off Microsoft's stupid "default
default" - Number fields default to 0, even when you want to use them
as a key field (which should be NULL until set). Check your table
definitions and remove the Default Value property of DTD if that's the
case.

John W. Vinson[MVP]

 

[Guest]

Hi Guys,

Sorry I have to use an old thread; I could not start a new one, and I wonder
why.

The problem I have concerns a query that I just built with the query
builder. I have a calculated field called DTDated. There is a form based on
this query. I want a user to input a date variable that determines what is
returned. But then, I want to be able to run the query independent of the
form. So I coded it this way:

DTDated: IIf(Not isloaded("myForm"),[DTD],myForm![When])

DTD is also a calculated field defined earlier in the query. myForm is the
form based on the query, and When is an unbound date field on the form.

The result is that when myForm is loaded, even in design view, the query
does not ask for parameters, and the DTDated field returns exactly DTD
(strange, because that is when I want it to return the value of
[myForm]![when]). But then when the form is not loaded, the query asks for
the value of [myForm]![When], (strange again, because I expected the IIf to
take care of that) and then returns the same DTD again! I want it to return
DTD only when the form not open, but to return values based on user input
when the form is in use.

Is something wrong with the IIf syntax? Is there a better way to achieve the
same result: that is, have the query run perfectly without asking for
parameters whether myForm is loaded or not, and still allow a user to
manipulate it with some variables on the form?