Calculated Field code

[cdr1869]

I'm having some trouble entering the exact code I need to deliver the results I'm looking for. I am using MS Access 2007.

For my query, I have

Deliverable ID: Primary Key/Constant
Budget-to-deliverable: $$
Invoice 1 amount:$
Invoice 2 amount:$
Invoice 3 amount:$
invoice 4 amount:$
Invoice 5 amount:$

Below is the code I am using to calculate the Budget Remaining field:

Budget Remaining: [Budget-to-Deliverable]-[Invoice #1 Amount]-[Invoice #2 Amount]-[Invoice #3 Amount]-[Invoice #4 Amount]-[Invoice #5 Amount]

however, when running this query, it prompts me/user to enter in the values for Invoice 1-5 rather than pulling the information from the table, even if it is 0. What modifications would I need to make to this code to have the query pull the values directly from the table and not prompt the user to enter them in before the query results are delivered?

Thanks!

 

[Brianwarnock]

You appear to be quotting different field names.

 

[cdr1869]

Im sorry. All of the original field names and the ones entered in the query calcualtion are static: Invoice #1 etc. I just mistyped it here...

 

[vbaInet]

Let's see the full sql statement.

 

[cdr1869]

I believe this is what you're asking for?:

Budget Remaining: [Budget-to-Deliverable]-([Invoice1 Amount]-[Invoice2 Amount]-[Invoice3 Amount]-[Invoice4 Amount]-[Invoice5 Amount])

I've made some changes: I took the (#) sign out of my field names for ease of entry in the code.

What I need at the end of the day would look something like this:

Budget Remaining: $500 ; derived from: [1000]-([200]-[150]-[100]-[50]-[0])

 

[vbaInet]

Right click your query and select SQL View. Copy and paste that here

 

[cdr1869]

SELECT [N/E Deliverables].[Deliverable ID Tag], [N/E Deliverables].[Budget-to-Deliverable], [N/E Deliverables].[Invoice1 Amount], [N/E Deliverables].[Invoice2 Amount], [N/E Deliverables].[Invoice3 Amount], [N/E Deliverables].[Invoice4 Amount], [N/E Deliverables].[Invoice5 Amount], [Budget-to-Deliverable]-([Invoice1 Amount]-[Invoice2 Amount]-[Invoice3 Amount]-[Invoice4 Amount]-[Invoice5 Amount]) AS [Budget Remaining]
FROM [N/E Deliverables];

 

[vbaInet]

I don't see anything wrong with it. The field names can also be written without the prefixed table name:

SELECT [Deliverable ID Tag], [Budget-to-Deliverable], [Invoice1 Amount], [Invoice2 Amount], [Invoice3 Amount], [Invoice4 Amount], [Invoice5 Amount], [Budget-to-Deliverable]-([Invoice1 Amount]-[Invoice2 Amount]-[Invoice3 Amount]-[Invoice4 Amount]-[Invoice5 Amount]) AS [Budget Remaining]
FROM [N/E Deliverables];

Is it still popping up with the input box?

By the way, it's a good idea to avoid spaces in field names.

 

[Brianwarnock]

Are you sure that you have your logic correct here
6-3-2 is different to
6-(3-2)

Brian