 |
|
|
|
Next: DSum Countrol Source Help
|
| Author |
Message |
External
Since: Feb 10, 2009
Posts: 2
|
(Msg. 1) Posted: Fri Jan 29, 2010 7:42 am
Post subject: Query Parameter
Archived from groups: microsoft>public>access (more info?)
|
|
|
Is there a way to use a query parameter with an expression. Or reference a
textbox value within an expression. In the expression below I would like to
replace the 9/1/2010 date with a reference to a textbox or parameter message
box:
MeasurementDays: IIf(DateDiff("d",qryActive_Agent![Hire
Date],#9/1/2010#)<365,DateDiff("d",qryActive_Agent![Hire
Date],#9/1/2010#)-nz([SumOfExcludedDays]),365-nz([SumOfExcludedDays]))
Thanks for any help |
|
| Back to top |
|
 | |
External
Since: Dec 8, 2008
Posts: 16
|
(Msg. 2) Posted: Fri Jan 29, 2010 11:50 am
Post subject: Re: Query Parameter
Archived from groups: per prev. post (more info?)
|
|
|
This should work in most queries.
MeasurementDays:
IIf(DateDiff("d",qryActive_Agent![Hire Date],CDate([What Date?]))<365,
DateDiff("d",qryActive_Agent![Hire Date],CDate([What Date?]))
-nz([SumOfExcludedDays]),365-nz([SumOfExcludedDays]))
Instead of [What Date?] you can use a reference to a control on a form.
Forms![NameOfForm]![NameOfControl]
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
ram wrote:
> Is there a way to use a query parameter with an expression. Or reference a
> textbox value within an expression. In the expression below I would like to
> replace the 9/1/2010 date with a reference to a textbox or parameter message
> box:
>
>
> MeasurementDays: IIf(DateDiff("d",qryActive_Agent![Hire
> Date],#9/1/2010#)<365,DateDiff("d",qryActive_Agent![Hire
> Date],#9/1/2010#)-nz([SumOfExcludedDays]),365-nz([SumOfExcludedDays]))
>
> Thanks for any help |
|
| Back to top |
|
| |
External
Since: Feb 10, 2009
Posts: 2
|
(Msg. 3) Posted: Fri Jan 29, 2010 11:50 am
Post subject: Re: Query Parameter
Archived from groups: per prev. post (more info?)
|
|
|
HI John,
I received the following error message: The MS Jet Database engin does not
recognize the form and control as a valid field name.
Do you know what I'm doing wrong?
Thanks again for any help
"John Spencer" wrote:
> This should work in most queries.
>
> MeasurementDays:
> IIf(DateDiff("d",qryActive_Agent![Hire Date],CDate([What Date?]))<365,
> DateDiff("d",qryActive_Agent![Hire Date],CDate([What Date?]))
> -nz([SumOfExcludedDays]),365-nz([SumOfExcludedDays]))
>
> Instead of [What Date?] you can use a reference to a control on a form.
> Forms![NameOfForm]![NameOfControl]
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
> ram wrote:
> > Is there a way to use a query parameter with an expression. Or reference a
> > textbox value within an expression. In the expression below I would like to
> > replace the 9/1/2010 date with a reference to a textbox or parameter message
> > box:
> >
> >
> > MeasurementDays: IIf(DateDiff("d",qryActive_Agent![Hire
> > Date],#9/1/2010#)<365,DateDiff("d",qryActive_Agent![Hire
> > Date],#9/1/2010#)-nz([SumOfExcludedDays]),365-nz([SumOfExcludedDays]))
> >
> > Thanks for any help
> .
> >> Stay informed about: Query Parameter |
|
| Back to top |
|
| |
External
Since: Dec 8, 2008
Posts: 16
|
(Msg. 4) Posted: Fri Jan 29, 2010 4:22 pm
Post subject: Re: Query Parameter
Archived from groups: per prev. post (more info?)
|
|
|
Is the form open? It must be open to be available to the SQL (although the
form can have its visible property set to No).
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
ram wrote:
> HI John,
>
> I received the following error message: The MS Jet Database engin does not
> recognize the form and control as a valid field name.
>
> Do you know what I'm doing wrong?
>
> Thanks again for any help
>
> "John Spencer" wrote:
>
>> This should work in most queries.
>>
>> MeasurementDays:
>> IIf(DateDiff("d",qryActive_Agent![Hire Date],CDate([What Date?]))<365,
>> DateDiff("d",qryActive_Agent![Hire Date],CDate([What Date?]))
>> -nz([SumOfExcludedDays]),365-nz([SumOfExcludedDays]))
>>
>> Instead of [What Date?] you can use a reference to a control on a form.
>> Forms![NameOfForm]![NameOfControl]
>>
>> John Spencer
>> Access MVP 2002-2005, 2007-2010
>> The Hilltop Institute
>> University of Maryland Baltimore County
>>
>> ram wrote:
>>> Is there a way to use a query parameter with an expression. Or reference a
>>> textbox value within an expression. In the expression below I would like to
>>> replace the 9/1/2010 date with a reference to a textbox or parameter message
>>> box:
>>>
>>>
>>> MeasurementDays: IIf(DateDiff("d",qryActive_Agent![Hire
>>> Date],#9/1/2010#)<365,DateDiff("d",qryActive_Agent![Hire
>>> Date],#9/1/2010#)-nz([SumOfExcludedDays]),365-nz([SumOfExcludedDays]))
>>>
>>> Thanks for any help
>> .
>> >> Stay informed about: Query Parameter |
|
| Back to top |
|
| |
External
Since: Jan 29, 2010
Posts: 1
|
(Msg. 5) Posted: Fri Jan 29, 2010 4:38 pm
Post subject: Re: Query Parameter
Archived from groups: per prev. post (more info?)
|
|
|
Send US$. RSVP
"ram" wrote in message
> Is there a way to use a query parameter with an expression. Or reference a
> textbox value within an expression. In the expression below I would like
> to
> replace the 9/1/2010 date with a reference to a textbox or parameter
> message
> box:
>
>
> MeasurementDays: IIf(DateDiff("d",qryActive_Agent![Hire
> Date],#9/1/2010#)<365,DateDiff("d",qryActive_Agent![Hire
> Date],#9/1/2010#)-nz([SumOfExcludedDays]),365-nz([SumOfExcludedDays]))
>
> Thanks for any help >> Stay informed about: Query Parameter |
|
| Back to top |
|
| |
| Related Topics: | Remove ''Enter Parameter Value'' - I deleted like 10 field names in my Employees table. When I want to go in my Employees form, ''Enter Parameter Value'' pops up for each field names I deleted, so 10 times. I usually go in the desgin view of that form, in the record source and delete..
Can not change data once i bases a query on a query - I have a despatch System that shows a customers orders with the products to be despatched Its on a Contious form based on a query. On the form i also want to show the Quantity that need to be despatched as the order might be despated in parts Th I hav...
Wildcard for Enter Parameter Value Box? - for example.. when you type in: [Enter Item_ID) The enter Parameter Value box appears asking for the Item ID How would I set it up so only a wild card would need to be entered intead of the full item name?
Alpha Query - Records in my Emp fields is as follows Emp 01-2-06ABC 1-1-09CDA 01/04/09BVE In my query, i want to select alpha and i can get Emp1 ABC CDA BVE Thanks
Query from 3 tables - I have three tables that I want to put in query. But this is what I need: Articles Stores MinimumQTY ----------- ---------- --------------------- ArticleID StoreID ArticleID.. |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Search
Profile
Private Messages
Log in
