coding

[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?

 

[Stuart McCall]

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"

 

[peljo via AccessMonster.com]

Thank you very much! That is eaxctky what i needed

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"

 

[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

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"

 

[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)

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

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"