Return a field value when date field matches Input parameter!

[ekimr]

Would somebody be kind enough to help me build a formula.

Scenario.
---------------------
Access Database table

fields in question are:

DateTimeField : In DateTime format
Moneyvalue : Number
-------------------------

My report is built based on a date time parameter passed from a vb front end.

VB format : 12 January 2003

--------------------------

In english - my requirement is this

GET 'MoneyValue' from access table when month and year from VB Parameter = Month and Year from DateTimeField in Access Table

---------------------------

To anybody that can help - many thanks

 

[nagornyi]

Month({?Parameter})=Month{{DateTimeField}} and Year({?Parameter})=Year{{DateTimeField}}

 

[ekimr]

that was quick - thanks

Can i take it one step further....please

As this goes into a formula field....How do I add the get clause

as in GET 'moneyvalue' when (insert your formula here)?

Thanks

 

[synapsevampire]

There is no GET clause in Crystal, perhaps you should supply example data and expected output.

The formula nagornyi supplied is fine, place it in th report under Report->Edit Selection Formula->Record

The above is Crystal version dependent, something you should always share when posting.

-k

 

[ekimr]

Hi

Its crystal 8.5

and this is not a record selection as such (well as far as I understand in my accepted very limited knowledge).

My field : It is a group header field (one field amongst many others) if its absent - then the other fields formulate the gp header....its just that this field result is dependant on the selection matching.

So: To give you an example
---------------------------------------------

Report Header

Report date (passed from VB) is 01 February 2004 to 28 February 2004

Then...

GP header = Electronic equipment location (= house)
***Rental period cost (changes by month)

--------------------------------------
So the rental period table is:

DateTime Cost
(mm/yyyy)
-------- ----
01/2004 £25
02/2004 £30
03/2004 £25

Result the above would be in Gp header

Rental period cost £30 (february).

Thank you again

 

[nagornyi]

Just put the cost field into the group header.

 

[ekimr]

Sorry for being thick - but how do I attach it to the help I have had so far...

I have put the field in the group header. But by itself it gets any old value from the table?

 

[ekimr]

Just to confirm -

I had created a formula field - so the formula somehow has to link the costfield to the date criteria?

If I don't have a formula - but just insert the field. How do I build the date criteria formula in?

This is just one field amongst many in the gorup header - so doesn't = the actual group selection critieria - because if the test fails, i still want all the rest of the group header out.

Confused!

Thanks

 

[nagornyi]

The grouping should be by DateTime field, which you can also put into the group header.

 

[ekimr]

How does that achieve a field in my group header that says

"Rental cost" £30

because I can't still see in english the link from good help you gave below to the actual costfield in the access table that I mentioned

Month({?Parameter})=Month{{DateTimeField}} and Year({?Parameter})=Year{{DateTimeField}}

How is the above linked to the costfield without affecting every other field in the group header?

Surely just having the field in the group header won't do it - where would the above syntax go then?

 

[nagornyi]

The formula is to be used in the CR selection expert. You create new selection, pick "formula" from the drop-down and type the above formula in the formula window. That will make the report to select only those records having month and year the same as in the parameter. Now about grouping. I am nor sure what kind of details you want to see in the group, but according to the data you gave, there is only one record for the month/year. So ... what is the group? Of course, you can create group by DateTime field, but (again, as far as I can infer from your data) there will be only one group and only one record in the group.
For the header, you can use formula
'Rental cost '+ {cost}

 

[synapsevampire]

Basically what you've gotten back to was my original post, except nagornyi suggests using the expert, and I didn't (and don't). To paraphrase:

There is no GET clause in Crystal, perhaps you should supply example data and expected output.

The formula nagornyi supplied is fine, place it in the report under Report->Edit Selection Formula->Record

As nagornyi suggested, you only have one row in the example, but if you are to return multiples, they'll still already br grouped by month and be one row.

Just in case we misunderstand (which seems very likely), any field that you want an aggregate of (such as a sum) in a group header (such as a date with the option set to for each month), just right click it in the details, select insert summary sum and select the group level of interest. It will be placed in the group footer. Drag it to the group header.

-k

 

[ekimr]

Thanks to both who have helped...

Monday will see my efforts at putting your ideas into place...

My concern is modifying my group criteria to cater for this new field having a selection criteria that is optional. I don't want my group header to be suppressed or any other field to that matter just because this new field needs some special criteria on it!

Synapse: you have suggested I should supply example data and expected output.

I believe i have but here is another shot...

GP header (has many fields on it) but essentially its location and electrical items in location.

I want to add a new field into this gp header which is the rental cost of this item. the rental cost changes monthly.

This is got from the table below...

so if my input parameter is Feb 04 - i get £30 in my gp header.

Thanks both again.


So the rental period table is:

DateTime Cost
(mm/yyyy)
-------- ----
01/2004 £25
02/2004 £30
03/2004 £25

When you also say there is no GET clause ....I would have thought that any system that links to databases would have the facility for a SELECT statement at field level?

So I could have easily solved it at field level by saying SELECT costfield where 'month and year' from input is equal to month and year in DateTimefield:Table

Regards again.

 

[ekimr]

Hi

I am afraid I am not getting anywhere on this...

I think the problem is because my new field belongs in the group header not as both of you have suggested in the details..

I already have selection criteria for the group which must not change.

What i want is this new field in the group header - it may or may not have a value, but if it does it comes from a record based on a field in that record (datetime) matching datetime from input critieria.


So to reiterate: Starred field is the 'guilty one'.
----------------------------------------------------
GPHEADER
LOCATION OF ITEM, NAME OF ITEM , ****Rental price of item*****
------------------------------------------------------
Details
Service record of item by location
------------------------------------