if statement

T

t.roff

hello, I am an inexperienced access user and I have been given some code to
add to:

i=1
Do while i<=12
G=Trim(i)
DetailsUpdateQuery = "Update Data Set Data.[G & "value] = (([G & "value]
-[G & "B])/[G & "A])*[G & "C]+[G & "D] WHERE (((Data.tagged) = False))"
DoCmd.RunSQL DetailsUpdateQuery

What I want to add is the condition that the update only occurs if
[G & "A] is not equal to [G & "C]
and [G & "B] is not equal to [G & "D]

[G & "value] is from the table called Data and the others ([G & "A,B,C,D])
are from a different table/form.

Thanks
 
F

Frank Stone

hi
Try this.
i=1
do while 1<=12
G= Trim(i)
DetailsUpdateQuery = "Update Data Set Data.[G & "value] =
(([G & "value]
-[G & "B])/[G & "A])*[G & "C]+[G & "D] WHERE
Click to expand...
(((Data.tagged) = False))"
-------------------------------------------------------
If >[G & "A] <> [G & "C] AND [G & "B] <> [G & "D] then
DoCmd.RunSQL DetailsUpdateQuery
end if
-------------------------------------------------------
what you want is between the dashed lines.
Regards
Frank
-----Original Message-----
hello, I am an inexperienced access user and I have been given some code to
add to:

i=1
Do while i<=12
G=Trim(i)
DetailsUpdateQuery = "Update Data Set Data.[G & "value] = (([G & "value]
-[G & "B])/[G & "A])*[G & "C]+[G & "D] WHERE (((Data.tagged) = False))"
DoCmd.RunSQL DetailsUpdateQuery

What I want to add is the condition that the update only occurs if
[G & "A] is not equal to [G & "C]
and [G & "B] is not equal to [G & "D]

[G & "value] is from the table called Data and the others ([G & "A,B,C,D])
are from a different table/form.

Thanks
.
Click to expand...
 
J

John Vinson

hello, I am an inexperienced access user and I have been given some code to
add to:

i=1
Do while i<=12
G=Trim(i)
DetailsUpdateQuery = "Update Data Set Data.[G & "value] = (([G & "value]
-[G & "B])/[G & "A])*[G & "C]+[G & "D] WHERE (((Data.tagged) = False))"
DoCmd.RunSQL DetailsUpdateQuery

What I want to add is the condition that the update only occurs if
[G & "A] is not equal to [G & "C]
and [G & "B] is not equal to [G & "D]

[G & "value] is from the table called Data and the others ([G & "A,B,C,D])
are from a different table/form.

Thanks
Click to expand...

Could you explain the unpaired quotemarks? As I read it, the statement
is completely erroneous - you have

value] = (([G

outside the quoted string constant, and a nonexistant value "value".

What should the SQL for DetailsUpdateQuery look like??

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
T

t.roff

Hi, sorry for confusing things because I had tried to simplify the
expression. Here is how it really is:

i = 1
Do While i <= 12
S_No = Trim(i)
DetailsUpdateQuery = "Update Data Set Data.[S" & S_No & "value] = (([S" &
S_No & "value]-[S" & S_No & " Loffset])/[S" & S_No & " Lslope])*[S" & S_No &
" Mslope]+[S" & S_No & " Moffset] WHERE (((Data.tagged) = False))"

If [S" & S_No & " Loffset] <> [S" & S_No & " Moffset] And [S" & S_No & "
Lslope] <> [S" & S_No & " Mslope] Then
DoCmd.RunSQL DetailsUpdateQuery
End If

i = i + 1
Loop

It works fine without the If statement. But with the If statement, I get
the following message 'Run-time error '2465': Microsoft Access can't find
the field '|' referred to in your expression'





John Vinson said:
hello, I am an inexperienced access user and I have been given some code to
add to:

i=1
Do while i<=12
G=Trim(i)
DetailsUpdateQuery = "Update Data Set Data.[G & "value] = (([G & "value]
-[G & "B])/[G & "A])*[G & "C]+[G & "D] WHERE (((Data.tagged) = False))"
DoCmd.RunSQL DetailsUpdateQuery

What I want to add is the condition that the update only occurs if
[G & "A] is not equal to [G & "C]
and [G & "B] is not equal to [G & "D]

[G & "value] is from the table called Data and the others ([G & "A,B,C,D])
are from a different table/form.

Thanks
Click to expand...

Could you explain the unpaired quotemarks? As I read it, the statement
is completely erroneous - you have

value] = (([G

outside the quoted string constant, and a nonexistant value "value".

What should the SQL for DetailsUpdateQuery look like??

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
J

John Vinson

Hi, sorry for confusing things because I had tried to simplify the
expression. Here is how it really is:

i = 1
Do While i <= 12
S_No = Trim(i)
DetailsUpdateQuery = "Update Data Set Data.[S" & S_No & "value] = (([S" &
S_No & "value]-[S" & S_No & " Loffset])/[S" & S_No & " Lslope])*[S" & S_No &
" Mslope]+[S" & S_No & " Moffset] WHERE (((Data.tagged) = False))"

If [S" & S_No & " Loffset] <> [S" & S_No & " Moffset] And [S" & S_No & "
Lslope] <> [S" & S_No & " Mslope] Then
DoCmd.RunSQL DetailsUpdateQuery
End If

i = i + 1
Loop

It works fine without the If statement. But with the If statement, I get
the following message 'Run-time error '2465': Microsoft Access can't find
the field '|' referred to in your expression'
Click to expand...

Exactly. Your IF statement makes no sense syntactically. I'm surprised
it even compiles!

A quoted string constant requires an opening quote " and a closing
quote ". You have

IF [S"

The IF is part of the VBA syntax statement - but the " is just hanging
there, not doing anything!

I still do not understand what you are trying to accomplish. Does your
table have fields named [S2 Loffset] and [S11 MOffset]? If so, it's
not properly normalized; and if you're assuming that VBA code has any
way to directly refer to table fieldnames in an IF statement you're
mistaken - VBA and SQL are two different languages.

What's the context here? Could you explain what real-life problem
you're trying to solve? Why do you feel that you need to run twelve
update queries to (apparently) update twelve fields, when a single
query with no code would (again apparently) work?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top