Convert to SQL

[kwokv616]

How do i convert this into SQL??
Thanks =)

rst.MoveFirst
Do
rst.Edit
rst![ValDate] = Forms![MainScreen].[InputDate].Value
rst.Update
rst.MoveNext

Loop Until rst.EOF
rst.Close

 

[namliam]

Create an update query....

 

[kwokv616]

I tried Currentdb.Execute "UPDATE Rid SET Rcode = [Pol].[Rcode] WHERE [Pol].[Pnumber]=[Rid].[Pnumber];" but doesnt work....

 

[namliam]

What does one have to do with the other??
Replacing your code with update would look something like

"Update Yourtable set ValDate = #" & Forms![MainScreen].[InputDate].Value
& "#"

 

[gemma-the-husky]

your string is

"UPDATE Rid SET Rcode = [Pol].[Rcode] WHERE [Pol].[Pnumber]=[Rid].[Pnumber];"

and that is what Access will try to do ie set rcode to a nonsensical value of "[pol].[rcode]"

to do what you want you have to make the string look like this

"UPDATE Rid SET Rcode = 12 WHERE [Pol].[Pnumber] = 256;"

so you have to include punctuation syntax to get this

"UPDATE Rid SET Rcode = " & [Pol].[Rcode] variable & " WHERE [Pol].[Pnumber] = " & [Rid].[Pnumber] variable

where the [Pol].[Rcode] variable and [Rid].[Pnumber] variable are properly formatted for the type of variable

bearing in mind that numbers/text/dates have different formatting requirements - easy once you get the hang of it

--------
now if you add the following bits

onerror goto fail
currentdb.execute statement, dbfailonerror

exithere:
exit sub

fail:
msgbox("Error: " & err & " Desc: " & err.description)
resume exithere


you add an error handler, that will help identify what is wrong with your statement, either syntax or even if it just fails

 

[kwokv616]

Sorry 1 more question. what if i want to write

If rst![prem] = 103.56 Then
rst.Edit
rst![prem] = 101.52
rst![eprem_o] = 2.04
rst.Update
End If

with two SETs. what is the format required?
Thankyou very much!!

 

[kwokv616]

Or even sth like this with two conditions:

If (rst![bsa] = 0) And (Left(Trim(rst!

), 4) = "JYRP") Then
            rst.Edit
            rst![Rcode] = "JYRCI" & Right(Trim(rst![code]), 1)
            rst.Update
        End If

 

[namliam]

What do you know about the query design??

Update YourTable
Set field1 = x, field2 = y
where condition


Update YourTable
Set field1 = x
where condition
and condition1
or Condition2