Access app, Query problem

[Rwatson]

I am having a problem writing a query in the Access app. I'm writing an expression where i am multiplying a field by a number. Then i take that new field and multiply it by another number in the next field.


Example.



Weight(g): [Volume]*5

Weight(oz): [Weight(g)]*5


However, when i finish the second field and try to save the query, it comes up with an error (invalid field name). As best i can figure out, it doesn't want there to be two fields with the same name.
Everything else is working great, just this one thing is standing in my way.

Any ideas?

Thanks

[June7]

Certainly can't have two fields with same name but I don't see two fields with same name.

Try not using special characters/punctuation (underscore is exception) in names.

Or since it is really a simple, short expression, just use the Volume field again:

Weight_oz: [Volume]*25

[Rwatson]

[QUOTE=June7;259132]Certainly can't have two fields with same name but I don't see two fields with same name.


Example a

Weight(g): [Volume]*5
Here i am making a new field that is calculated by taking the volume and multiplying it by 5.

Example b

Weight(oz): [Weight(g)]*5
Here i want to use the value i found for the weight in grams and multiply it by 5. Access keeps telling me that weight in grams has already shown up once (example part a) and it wont except it in this expression.


I other words you cant use the same field in multiple expressions.
That is the problem i am trying to work around.

[John_G]

I think it may be your use of brackets () that it doesn't like - it is interpreting them as array subscripts. As June7 suggested, try Weight_g and Weight_oz as field names.

Additional info: I'm using Access 2010, and I cannot replicate the error. The brackets are OK, I can use the same fields more than once, and I can use a previous field name with brackets in an expression - query SQL below:

SELECT CmptcyInst_ID, Stat_ID, Stat_LVL, Stat_SEQ, [stat_id]/2 AS [Text(a)], [text(a)]*2 AS [text(b)], [text(a)]*1 AS [text(c)]
FROM Competency_Statement

So maybe it's something "new" in A2013.

[Rwatson]

I already tried that. Just to make sure i named a field "g" (without the quotes obviously) and it still rejected it. Everything is fine until the field name shows up in an expression after it has been set as the field title.

Should i take some screen shots or something?

Thanks

[June7]

Attach whatever you think will help: screen shot, the SQL statement, the database.

I suggested using the native Volume field in the expression so don't have to use the constructed field.

However, really should be able to use the constructed field. So this is odd. Odd often means corruption.

[Rwatson]

Here is a screen shot. Remember this is the Access web app not the desktop version. I replicated this query in the desktop access and it worked fine.

[rpeare]

first, as others have mentioned, take the () marks OUT of your field names.

part volume (in3)

change this field name to something like part_volume_in3

Second depending on whether or not you are aggregating your current formulas may or may not work. to be more thorough you should have something like

PartWeightG: [Part_Volume_in3] * 5
PartWeightL: ([Part_Volume_in3] * 5)*5

[John_G]

From the screenshot, it's ambiguous as to what is causing the error because you use the field name PartWeightG twice. Conceivably it's the use in the first column it doesn't like (though I can't imagine why - Capital letters maybe???))

Change the name in column 1 (only) to PartWeightX for example, and see what the error message is.

[Rwatson]

If i'm understanding you correctly you are saying to add the entire equation the number is built on before adding another operation to the expression.
If at all possible i would really like to avoid this because i have quite a few more fields that need to be generated from previous fields. It all builds on itself and the equations would get really long.

[Rwatson]

It is still coming up as the same thing but now i don't know why.

[June7]

What is [part volume (in3)]? Is that a field in table?

You show compound joins. You are not using the values of PK ID fields as foreign key? Why? If you are not using the ID fields then why have them designated as PK? Should have composite PK.

Expand the table boxes so all fields can be seen.

[rpeare]

first, answer a basic question.

Do you actually have a field named

PART VOLUME (in3)

On one of your tables?
Secondly why not add a field from one of your tables, it may be crashing because you are not showing any data, just formulas, try adding your field that you're trying to peform this calculation on (the actual field PART VOLUME (in3))

Lastly... how about you upload a sample database with some garbage data in it to illustrate the question. Just copy your database, take out any private information but leave enough fake data in there to duplicate the problem, then zip it up and upload it through the GO ADVANCED function on this forum.