Can't get a simple expression to run in an access Query

[jyadayada]

I'm building an access query, in it one of the fields is called



[Space Available (cm)]


I'm trying to add a divided by 6 field, so I've inserted a field in design view in the query, it looks like this:


Field: Expr1: SectioalSpaceAvailable

Table:
Sort:
Show:
Criteria: =[Space Available (cm)] / 6

or:


When I go to switch into Datasheet View I get the error "Undeined function 'SectionalSpaceAvailable' in expression but all the expression examples i see on the net have =[fieldname] / X as how to write such an expression, what am i missing here?

 

[Minty]

The ( ) in the field name are your problem. I'm always amazed that access allows it to be honest.

Get rid of them and all the spaces, to make your life much easier going forwards. Adopt camel case / a naming convention and avoid reserved words in field names. All will help you avoid strange issues like this one.

 

[arnelgp]

do you have the field "SectioalSpaceAvailable"
or you mean "SectionalSpaceAvailable".

try your query without Criteria, does it work.
criteria is Boolean.

"[Space Available (cm)] / 6" doesn't mean anything, you should compare it to something.

 

[Minty]

Actually re-reading that , i've mistaken that message - if it works you want;

SectionalSpaceAvailable: [Space Available (cm)] / 6

My advice about the field names stands though

 

[arnelgp]

yes for minty.

if you want to round the result to 2 decimal places:

SectionalSpaceAvailable: Round([Space Available (cm)] / 6, 2)

 

[jyadayada]

Thanks for that, that's not erroring now, I had to delete out and reload all my tables with spaces / brackets removed


but now when I switch into datasheet view it asks for a paramater value and then brings me an empty datasheet


[Space_Available_CM] is a table field containing values and I want this expression field [Sectional_Space_Available_CM] in the query to simply bring a sixth of the [Space_Available_CM] value through


I'm very new to access I know but everything i'm reading states that this is how you write a divide this field by this value expression

 

[jyadayada]

Basically I'm building a query bringing in columns from tables with joined fields, this is me trying to insert a field that doesn't exist in any of the tables that is a calculation of one of the fields that is also being brought into the query


Going into SQL view on the above error, after all the SELECT and INNER JOIN information, this additional field i'm putting in is reading like this at the end of the SQL :


WHERE ((([Sectional_Space_Available_CM])=[Space_Availabale_CM]/6));


this reads like its a matching record criteria not a field creation string but i'm doing some more digging....

 

[jyadayada]

What a doof!


I put your text into the front end of design view and it runs fine, clicking into build in the field and letting Access add the Expr1 bit seemed to be the problem


thanks for your inputs!

 

[Minty]

Sorry - perhaps should have made it clearer that the supplied code was to replace the "Expr1:" bit .

Glad you have it sorted, and trust me all those spaces would have come back to haunt you and added a million [ ] in extra typing in your code.

 

[jyadayada]

I've now turned it into an IIF that won't switch into datasheet view without asking for paramaters again, it reads:


Group_Space_Available_CM: IIF([Space_Type]="Legacy",[Space_Available_CM]/6,IIF([Space_Type]="Dual",{Space_Available_CM]/10))


i've other iif statements written the same way that aren't asking for paramaters?

 

[JHB]

..
Group_Space_Available_CM: IIF([Space_Type]="Legacy",[Space_Available_CM]/6,IIF([Space_Type]="Dual",{Space_Available_CM]/10))
..

Is it a typo, else you've a bad letter ({).