Report Query: Comparion variable from data field

[mikmonto]

Hi,

I need to have the comparison variable (< or >) to be read from a field in the form.
Namely:

WHERE (((([PackagesCosts].[TotalCost]) & [Forms]![F_Reports]![Increased] & [C].[TotalCost])

Where [Forms]![F_Reports]![Increased] is the filed on my form.

What is wrong in the syntax (as it is not working properly)?

 

[CJ_London]

WHERE (((([PackagesCosts].[TotalCost]) & [Forms]![F_Reports]![Increased] & [C].[TotalCost])
 
Where [Forms]![F_Reports]![Increased]

looks like you are not stating the criteria correctly. The syntax should be - assuming you are trying to match the first part to the second part

WHERE [PackagesCosts].[TotalCost] + [Forms]![F_Reports]![Increased] + [C].[TotalCost] = [Forms]![F_Reports]![Increased]

Alternatively if you have two criteria then

WHERE [PackagesCosts].[TotalCost] + [Forms]![F_Reports]![Increased] + [C].[TotalCost] = [SomeValue] AND [Forms]![F_Reports]![Increased] = [AnotherValue]

I've stripped out the brackets because they are not really required changed the '&' to '+' since I assume you want to add values rather than concatenate them

If you use the query builder it should be pretty straight forward (it would not have produced the code you supplied)

 

[mikmonto]

Hi CJ,

thanks for the prompt response.
Actually the filed [Forms]![F_Reports]![Increased] will provide values "<" 0 or" >" and it is used to compare the other 2 fields
[PackagesCosts].[TotalCost] and [C].[TotalCost]



Does this change the query in any extent?

 

[CJ_London]

trying to make sense of that

are you saying you want records where [Forms]![F_Reports]![Increased] does not equal 0?

If so the the code is [Forms]![F_Reports]![Increased]<>0

Or is this what you are looking for?

"Return all records where [Forms]![F_Reports]![Increased] equals[PackagesCosts].[TotalCost] and [Forms]![F_Reports]![Increased] does not equal [C].[TotalCost]"

Perhaps you can write it out in the above form and I should be able to provide the solution

 

[mikmonto]

It may sound confusing, but the field [Forms]![F_Reports]![Increased] is actually the operator (< or > value) of the where clause.

This is because on the same report I want to show two different scenarios.

 

[CJ_London]

I still don't understand what you are trying to achieve can you write it out as I suggested

"Return all records where [Forms]![F_Reports]![Increased] equals[PackagesCosts].[TotalCost] and [Forms]![F_Reports]![Increased] does not equal [C].[TotalCost]"

 

[mikmonto]

"Return all records where [PackagesCosts].[TotalCost] [Forms]![F_Reports]![Increased] [C].[TotalCost]"


with [Forms]![F_Reports]![Increased] being < , > or =

 

[CJ_London]

I'm sorry -it still doesn't make sense to me

use words like these between all of your values

equals
not equal to
greater than
less than
and
or
plus
minus
if..then

Does this make sense?

Return all records where

if [Forms]![F_Reports]![Increased]='>' then [PackagesCosts].[TotalCost] plus [Forms]![F_Reports]![Increased] is greater than [PackagesCosts].[TotalCost]

OR if [Forms]![F_Reports]![Increased]='<' then [PackagesCosts].[TotalCost] plus [Forms]![F_Reports]![Increased] is less than [PackagesCosts].[TotalCost]

OR if [Forms]![F_Reports]![Increased]='=' then [PackagesCosts].[TotalCost] plus [Forms]![F_Reports]![Increased] equals[PackagesCosts].[TotalCost]

 

[CJ_London]

Revisiting your last post - Just noticed in the email advice there are asterisks which have not appeared in the forum.

[PackagesCosts].[TotalCost]* [Forms]![F_Reports]![Increased]* [C].[TotalCost]"

Revisiting your last post - are your trying to see the effect of an increase which is in [Forms]![F_Reports]![Increased] i.e. this is a number value and not a >, < or = character

 

[mikmonto]

[Forms]![F_Reports]![Increased] is exactly a character (eg < or > or =)


This can have generated confusion

 

[CJ_London]

you cannot multiply by a character (which is what * does) so

[PackagesCosts].[TotalCost]* [Forms]![F_Reports]![Increased]* [C].[TotalCost]" [PackagesCosts].[TotalCost]* [Forms]![F_Reports]![Increased]* [C].[TotalCost]"
"Return all records where [PackagesCosts].[TotalCost]* [Forms]![F_Reports]![Increased]* [C].[TotalCost]"

will not work.

I am unable to help you if you do not explain what you require more clearly

So try to change the below the explain

Return all records where

if [Forms]![F_Reports]![Increased]='>' then [PackagesCosts].[TotalCost] plus [Forms]![F_Reports]![Increased] is greater than [PackagesCosts].[TotalCost]

OR if[Forms]![F_Reports]![Increased]='<' then [PackagesCosts].[TotalCost] plus [Forms]![F_Reports]![Increased] is less than [PackagesCosts].[TotalCost]

OR if [Forms]![F_Reports]![Increased]='=' then [PackagesCosts].[TotalCost] plus [Forms]![F_Reports]![Increased] equals[PackagesCosts].[TotalCost]

 

[mikmonto]

Sorry, I think we have to go back to the original request.

WHERE (((([PackagesCosts].[TotalCost]) & [Forms]![F_Reports]![Increased] & [C].[TotalCost])
Meaning Where TABLE.FIELD1 < > TABLE.FIELD2

and the < > is given by
[Forms]![F_Reports]![Increased]

 

[CJ_London]

Ah! Got it at last!

OK - you need to build a string so what is wrong is your brackets and you need to add in some quotation marks

FilterStr="WHERE [PackagesCosts].[TotalCost] " & [Forms]![F_Reports]![Increased] & " [C].[TotalCost]"

Then apply the filterstr to the rest of the query sql or add to the filter option in docmd.openform or docmd.openreport (in which case your would remove the word 'WHERE'.

Don't think you can apply this in the query builder or macro filter - you would have to build something like this in the field line and then set the criteria line to True

iif([Forms]![F_Reports]![Increased]='>', [PackagesCosts].[TotalCost]> [PackagesCosts].[TotalCost], iif[Forms]![F_Reports]![Increased]='<' , [PackagesCosts].[TotalCost] < [PackagesCosts].[TotalCost],iif([Forms]![F_Reports]![Increased]='=',PackagesCosts].[TotalCost] =[PackagesCosts].[TotalCost],False)))

 

[mikmonto]

Hi CJ,

Thanks for this! Sorry for annoying you, but the explanation of what I need to do does not sound familiar at all to me. Can you please be more descriptive? Bear in mind I am using Access 2010.

Thanks,
M