View Full Version : Access / ASP Problem

12-08-2004, 05:11 AM
Hi, I need help on a little function.

I use an access database for a large list of products.

I have 2x price tables, one for ex vat, and one for inc.

The problem is that when I update it in the backoffice program, I have to enter the ex vat and also the inc vat. Very time consuming, and a big annoyance.

The program i use to update is just 2 dynamic textboxes with an update button on a repeated region.

<input name="iprice" type="text" id="iprice" size="8" value="<%=formatnumber(rsprods.Fields.Item("SPRICE").Value,2,-1)%>">

I'm pretty sure there is a way of setting this up in the database, but the expression builder is very confusing, and I've tried for ages to come up with something, if anyone knows access I need the [RPRICE] table to get the price from the [SPRICE] table and divide it by 1.175. (UK VAT Rate)

And if I use that in the database, i can delete the RPRICE update feature, and when I update the SPrice table it should automatically work out the vat, and display it dynamically on pages.

I figure this is what will happen, but theres always unexpected results.

Appreciate any help at all.



12-08-2004, 10:46 PM
sorry, but i don't understand why you need two tables. Why not symply have 2 columns inside one table? or why not add the VAT on the fly when you compute a price?

12-09-2004, 02:31 PM
Sorry thats what i meant, I have got two rows, little mistake there.

If anyone knows how to calculate from one row to the other?

Thanks again


12-09-2004, 04:04 PM
UPDATE your_tablename SET your_col_without_vat = (your_col_with_vat / 1.175)

--> your course, you need to replace the table and columnnames with the ones in your table

12-10-2004, 04:44 PM
on a sidenote, prob a better idea to use
withVAT = (withoutvat*1.175)

since then you can put the price you want, but i would just generate the vat to be dispalyed on the fly, i think you can also just set it to autoamttically add it from inside the field properties in access so it is just linked (default value property)

12-12-2004, 07:05 AM
Thanks for the help, I'm pretty new to databases, and still having a problem, don't mean to sound stupid but do I enter it as the "default value"?

I have tried all rows to try and get it working and cant seem to get it, if I enter it into the Default Value one, then I get "The database engine does not recognize either the field 'SPRICE' in a validation expression, or the default value in the table 'Products'

Anything I'm doing wrong?

Thanks again