K kwokv616 Registered User. Local time Today, 05:20 Joined Nov 10, 2008 Messages 46 Dec 10, 2008 #1 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
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
N namliam The Mailman - AWF VIP Local time Today, 14:20 Joined Aug 11, 2003 Messages 11,695 Dec 10, 2008 #2 Create an update query....
K kwokv616 Registered User. Local time Today, 05:20 Joined Nov 10, 2008 Messages 46 Dec 10, 2008 #3 I tried Currentdb.Execute "UPDATE Rid SET Rcode = [Pol].[Rcode] WHERE [Pol].[Pnumber]=[Rid].[Pnumber];" but doesnt work....
I tried Currentdb.Execute "UPDATE Rid SET Rcode = [Pol].[Rcode] WHERE [Pol].[Pnumber]=[Rid].[Pnumber];" but doesnt work....
N namliam The Mailman - AWF VIP Local time Today, 14:20 Joined Aug 11, 2003 Messages 11,695 Dec 10, 2008 #4 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 & "#"
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 Super Moderator Staff member Local time Today, 13:20 Joined Sep 12, 2006 Messages 16,022 Dec 10, 2008 #5 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
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
K kwokv616 Registered User. Local time Today, 05:20 Joined Nov 10, 2008 Messages 46 Dec 11, 2008 #6 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!!
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!!
K kwokv616 Registered User. Local time Today, 05:20 Joined Nov 10, 2008 Messages 46 Dec 11, 2008 #7 Or even sth like this with two conditions: If (rst![bsa] = 0) And (Left(Trim(rst! Code: ), 4) = "JYRP") Then rst.Edit rst![Rcode] = "JYRCI" & Right(Trim(rst![code]), 1) rst.Update End If
Or even sth like this with two conditions: If (rst![bsa] = 0) And (Left(Trim(rst! Code: ), 4) = "JYRP") Then rst.Edit rst![Rcode] = "JYRCI" & Right(Trim(rst![code]), 1) rst.Update End If
N namliam The Mailman - AWF VIP Local time Today, 14:20 Joined Aug 11, 2003 Messages 11,695 Dec 11, 2008 #8 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
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