Getting 'enter parameter value' when there are no typos

[wmphoto]

I have a query based on tableA in which fieldX and fieldY are the sums of two fields in tableB, which is on the many side of a 1-many relationship with tableA.

Initially fieldX and fieldY work correctly, however when I add a new column with the expression fieldM: [fieldX] - [fieldY], and open the query I get the following boxes.

Enter Paramter Value
Enter fieldX

and the same for fieldY.

If I leave them blank and just click OK then fieldX and fieldY still show the correct values, and fieldM shows the correct difference. So I can't understand why the enter parameter value box appears as everything I can find on this says it occurs either deliberately if you want a parameter to be defined each time the query is run, or if you have misspelled one of the field names in the query.

Then I create a new field which uses the values in fields X, Y and M in a nested IIf statement to create a status message for each record the desired effect being that X=0 is one status, the next level of nesting makes it and Y=0 and the final level is M>0

Now I get enter parameter boxes for X Y and also M, but again if I just leave them blank, the entire query works as it should.

Any sugggestions?

 

[aytee111]

Put the tablename in the front - [tablea].[fieldx] - [tablea].[fieldy]

 

[Galaxiom]

Access doens't recognise the calculated fields because they don't exist until they are derived in the query.

Change the query structure to calculating them in a subquery first and refer to them from the main query.

 

[wmphoto]

Thanks I'll try this, it seems the solution to almost every problem I have is 'this needs to be done using two seperate queries '

was gonna say I tried using the full field address but still got the boxes. Thanks for the help everyone.

 

[Galaxiom]

Once you get the two separate queries working you can tidy up by combining them into a single query with a subquery structure.

You need to work on this in the SQL view. The key to doing this well is layout of the text which Access unfortunatley has a habit of trashing.