coding

  • Thread starter peljo via AccessMonster.com
  • Start date
P

peljo via AccessMonster.com

I want to make an update code without referring to the form

My update query reads in is sql as follows
Dim SQL As String
StrSQL = "UPDATE products SET products.new = [products].[grossprice]-
[products].[grossprice]*28/100"
I want to rewrite it in the following way :
Dim Diff As String
Diff = [products].[grossprice] - [products].[grossprice]
SQL = "UPDATE products SET products.new = diff*28/100 "

However this effort fails since i have to refer to a form.Can i build a code
without referring to a form?
 
S

Stuart McCall

peljo via AccessMonster.com said:
I want to make an update code without referring to the form

My update query reads in is sql as follows
Dim SQL As String
StrSQL = "UPDATE products SET products.new = [products].[grossprice]-
[products].[grossprice]*28/100"
I want to rewrite it in the following way :
Dim Diff As String
Diff = [products].[grossprice] - [products].[grossprice]
SQL = "UPDATE products SET products.new = diff*28/100 "

However this effort fails since i have to refer to a form.Can i build a
code
without referring to a form?

It is a variable, not a form, that you are referring to. The variable needs
to be 'broken out' of the SQL string so that Access can evaluate it.
Re-write your code like this:

Dim Diff As String
Diff = [products].[grossprice] - [products].[grossprice]
SQL = "UPDATE products SET products.new = " & diff & "*28/100"
 
P

peljo via AccessMonster.com

Thank you very much! That is eaxctky what i needed

Stuart said:
I want to make an update code without referring to the form
[quoted text clipped - 10 lines]
code
without referring to a form?

It is a variable, not a form, that you are referring to. The variable needs
to be 'broken out' of the SQL string so that Access can evaluate it.
Re-write your code like this:

Dim Diff As String
Diff = [products].[grossprice] - [products].[grossprice]
SQL = "UPDATE products SET products.new = " & diff & "*28/100"
 
P

peljo via AccessMonster.com

In the same vein and in the same manner as you suggested would help me also
rewrite correctly the following code ?

Dim Bulk As String
Bulk = " UPDATE products SET products.office = [products].[grossprice]-
[products].[grossprice]"
Dim StrSQL As String
StrSQL = Bulk * 28 / 100
CurrentDb.Execute StrSQL



Stuart said:
I want to make an update code without referring to the form
[quoted text clipped - 10 lines]
code
without referring to a form?

It is a variable, not a form, that you are referring to. The variable needs
to be 'broken out' of the SQL string so that Access can evaluate it.
Re-write your code like this:

Dim Diff As String
Diff = [products].[grossprice] - [products].[grossprice]
SQL = "UPDATE products SET products.new = " & diff & "*28/100"
 
D

Douglas J. Steele

Perhaps you can explain in words what you're trying to do. At first blush,
you're trying to use an Update query in a calculation, which doesn't make
any sense.

More fundamental, though, is the fact that you're trying to store calculated
values in a table, something that should rarely be done. As fellow Access
MVP John Vinson likes to say "Storing calculated data generally accomplishes
only three things: it wastes disk space, it wastes time (a disk fetch is
much slower than almost any reasonable calculation), and it risks data
validity, since once it's stored in a table either the Total or one of the
fields that goes into the total may be changed, making the value WRONG."

Instead, you should put the calculations into a Select query, and use that
Select query wherever you would otherwise have used the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


peljo via AccessMonster.com said:
In the same vein and in the same manner as you suggested would help me
also
rewrite correctly the following code ?

Dim Bulk As String
Bulk = " UPDATE products SET products.office = [products].[grossprice]-
[products].[grossprice]"
Dim StrSQL As String
StrSQL = Bulk * 28 / 100
CurrentDb.Execute StrSQL



Stuart said:
I want to make an update code without referring to the form
[quoted text clipped - 10 lines]
code
without referring to a form?

It is a variable, not a form, that you are referring to. The variable
needs
to be 'broken out' of the SQL string so that Access can evaluate it.
Re-write your code like this:

Dim Diff As String
Diff = [products].[grossprice] - [products].[grossprice]
SQL = "UPDATE products SET products.new = " & diff & "*28/100"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top