Simple question about using the expression builder a query

[Student_in_need]

Right basically what i'm tryin to do is .... i have a table tblConstants this has one field in which holds a TAX value. I'm wanting to use the value in the query this is what i have entered using the expression builder

Cost inc VAT: [Unit Price]*[No Of Items]*(1+[tblConstants]![VAT]/100)

However whenever i try and open the query it says Enter parameter value which i'm guess is the value of the TAX (VAT) field but i don't want to enter a value every time i open the query!!

please please Help

Thank you

 

[KenHigg]

You may want to consider saving this maybe as a current tax rate field in one of the transaction tables. In which case accessing it would require a different method. If (when) the rate changes, you don't want the different rate to alter the results of previous calculations.

Otherwise, I would consider setting this up as a system constant you could call like any other constant...

Ken

 

[Student_in_need]

Thank you get getting back to me...

I can't set it up as a system constant as i have a set specification. I am might to have a table with just the number value in... i just can't understand why i can't access it. Does it have something to do with the data type?

Any help?

 

[Student_in_need]

Thank you get getting back to me...

I can't set it up as a system constant as i have a set specification. I am might to have a table with just the number value in... i just can't understand why i can't access it. Does it have something to do with the data type?

Any help?

 

[KenHigg]

You may be able to do a dlookup(). What are the other flds in tblConstants and which one is the pk?

kh

 

[jgc31]

I dont believe that you can reference a table field in this way. [tblConstants]![VAT] will not return a value as it is not unique ( even if there is only one record at present ). If there is a bound form that has this field as part of the bound table or query then me!VAT will return a value for the particular record that is active. Alternatively if you are looping through records in a recordset you can then refer to the value of a particular field as again there is a particular record to reference,

 

[Rich]

You should store the VAT rate at the point of sale along with the rest of the invoice details and then calculate the result either in the same query or via a calculated control