Question Expression Builder Help, Looking up and comparing.

[Blue 08 R6]

Hi everyone,

I am trying to use expression builder to gather information for my table.

For example i have 2 TBL's

TBL Accommodation Transactions

TBL Camp Costs

and have generated a query that gets its results from TBL Camp Costs.

QRY Camp Costs

Results are:
For Example

Camp Location.......Cost Per Night
Moomba ................77.27
Jackson ................94.94

Now depending on what location the user selects I would like the Transaction Amount field in my TBL to look up the correct rate for that location based on the information supplied

So far I have the following but it does not seem to work and I get an error saying that i have an invalid syntax, I have entered an operand without an operator.

Transaction Amount = [QRY CAMP COSTS]![COST PER NIGHT] where [TBL Accommodation Transactions]![Camp Location]==[TBL CAMP COSTS]![CAMP LOCATION]

Any assistance on this would be greatly appreciated.

Thank you

Adam

 

[John Big Booty]

It may be being caused by the embedded space you have in your control and object names.

Avoid spaces and other special characters in both control and object names, stick with alpha numeric characters and underscore(_), for example; TBL_TableName

 

[Blue 08 R6]

Thanks for your reply John,

I tried what you suggested but still no luck. I'm getting the same error as before.

I now have the code as:

Transaction Amount: = ( [QRY_CAMP COSTS]![COST PER NIGHT] where [TBL_Accommodation Transactions]![Camp Location]==[TBL_CAMP COSTS]![CAMP LOCATION]

Cheers

Adam

 

[John Big Booty]

Well you've still got embedded spaces in you object and control names, for example there is a space in the name of the query QRY_CAMP COSTS to name but one. Now it's not just a simple matter of removing spaces from the code you will need to start by renaming first your tables, then the field, then your forms and the controls on your form and so on.

I've also noticed that you have a double equal sign in your code, which most certainly is not helping things;

Transaction Amount: = ( [QRY_CAMP COSTS]![COST PER NIGHT] where [TBL_Accommodation Transactions]![Camp Location][B][COLOR="Red"]==[/COLOR][/B][TBL_CAMP COSTS]![CAMP LOCATION]

 

[Blue 08 R6]

Thanks John,

Please excuse my ignorance as I am fairly new to access.

I will look at changing the names of the tbl's etc.. and give that a go.

I have had some limited success with the following but it is producing some interesting results

Transaction Amount: IIf([TBL Accommodation Transactions]![Camp Location]=[Moomba],"","77.27") Or IIf([TBL Accommodation Transactions]![Camp Location]=[Jackson],"l","95.45") Or IIf([TBL Accommodation Transactions]![Camp Location]=[Ballera],"","95.45") Or IIf([TBL Accommodation Transactions]![Camp Location]=[Dullingari]," Or ","77.27") Or IIf([TBL Accommodation Transactions]![Camp Location]=[Wareena],"","180.00")

For some reason it returns a figure of (-1) instead of the "true statement"


if i change the "or" to an "and" it returns the "true statement" but the problem there is it compiles all of them, so for example if Moomba is in the Location Column it should return 77.27 in the Transaction amount column. But it is returning 77.2795.4595.4577.27180.00 which is an accumilation of all "true statements"

Any thoughts on this?

Thank you again

Adam

 

[John Big Booty]

Just quickly you seem to have an "OR" in the IIF for Dullingaril based on the other IIF's I'd guess that's supposed to be "".

I'll try and get my head around the rest of it in a little while.

 

[John Big Booty]

Just thinking a little further, perhaps you need a nested IFF rather than OR

 

[John Big Booty]

Ok; the structure of your Iif Or is not quite correct.

I think it should probably look something like;

Transaction Amount: IIf([TBL Accommodation Transactions]![Camp Location]=[Moomba] OR [TBL Accommodation Transactions]![Camp Location]=[Dullingari], “77.27”, IIf([TBL Accommodation Transactions]![Camp Location]=[Jackson] OR [TBL Accommodation Transactions]![Camp Location]=[Ballera], “77.27”,”180”))

I'm only guessing as the argument you have presented doesn't make a lot of sense.

If I've read between the line correctly you want to return a result of 77.27 for Moomba AND Dullingari whilst Jackson AND Ballera will return a result of 95.45 and Wareena will return a result of 180.00. This is what the code I have posted Should do.