Getting "Invalid bracketing of name" error.

[bradp1979]

Similar to my other post here, http://bit.ly/1TMiCP1, I'm trying to get a total. On the special event form, post event sub form, I have a textbox in which I type in the actual number of cars parked in a garage, and another in which I type in actual number of cars parked in a lot. These textboxes are named Actual#CarsGarage and Actual#CarsOutside, respectively. Where I want this total displayed is in an unbound textbox on another, related form, the client form.



The control source of the unbound textbox is a query named qryClientEvent. In qryClientEvent, I entered this into a column: Expr1: Sum([EventID]![tblEvent.Actual#CarsGarage]+[tblEvent.Actual#CarsOutside]).

When I try to go back to form view, I get the error message "Invalid bracketing of name [EventID]![tblEvent.Actual#CarsGarage]"

Here are a few pictures to illustrate:

[June7]

Why is EventID! prefixing the first term? A field would never be prefix.

Drag a field to design grid or double click to send it to grid to view how the query will reference. Probably just need:

Sum([Actual#CarsGarage] + [Actual#CarsOutside])

[bradp1979]

That's what I initially tried, but I got the error message: "You tried to execute a query that does not include the specified expression 'EventID' as part of an aggregate function"

[June7]

Post the entire SQL statement. It doesn't look like an aggregate query in the image and it couldn't be for a data entry form.

As you said, similar issue to your other thread. Same concept. Calculate with aggregate functions in textbox placed in form footer.

Query should probably be:

AliasFieldNameHere: [Actual#CarsGarage] + [Actual#CarsOutside]

Then textbox in form footer:
=Sum([AliasFieldNameHere])

[bradp1979]

I'll give that a try, but it's fairly important not to have this be tallied up in the form footer, but in the unbound textbox, inline with the rest of the information for the particular event.

[June7]

Isn't that what the simple addition of the two fields does?

However, arithmetic with null results in null. If either field is null the calc will result in null. Need to handle possible null. That or just show raw data on form and let people do simple addition in their heads.

Do more complex calcs on reports.

[bradp1979]

Ok, I got it all working. I just had to use the code you gave, and tweak the column size.

Thanks!!

[bradp1979]

The way I handled the possibility of null is that I set the default value as zero. Now it won't be left blank.

[bradp1979]

Though I may have spoken too soon. I set the default value as zero, but it's not displaying a zero..

[bradp1979]

One last question. On the query, in the column that has Actual#CarsOutside and Garage, if I want to say "If null, =0," How do I word that so that the query accepts it and applies it?

[bradp1979]

That or just show raw data on form and let people do simple addition in their heads.

I like this idea better... Would the easiest way to do this simply be to put Cars Parked: [Actual#CarsGarage] "&" [Actual#CarsOutside]?

[June7]

Either 2 textboxes to display the individual values or:

="Garage: " & Nz([Actual#CarsGarage], 0) " & Outside: " & Nz([Actual#CarsOutside], 0)

Review http://allenbrowne.com/QueryPerfIssue.html

Are users supposed to do data entry into these two fields?

[ssanfu]

FYI: "Index" and "Date" are reserved words in Access and shouldn't be used as object names. You will have headaches..... "Date" is also an intrinsic function.

You should only use letters, numbers and the underscore in object names. No spaces, punctuation of special characters.

"#" is a delimiter for dates. Having "#" in an object can/will confuse Access. Should use "Num" or "No".

In table "tblRelEventEmployee" you have a field named "Mgr?" a better name would be "IsMgr".

[bradp1979]

Are users supposed to do data entry into these two fields?

Yes. Every couple days or so, we process reports, and enter values into these fields.

[June7]

Then aren't there already two textboxes for input of this data? Why bother with the expression to show these two values in one textbox?