I'm playing around with some SQL queries and need a bit of help to get me going.
I have a table named 2984. 1st column is ServiceNo which is number values stepping up in increments of 0.5.
I want to find the largest value in the column, then add 2 to that value and enter a new record with the new value. The rest I'm sure i will be able to work out. The code I've been playing with is below. I know why it doesn't work but I don't know how to make it work. I want to get the value and call it Dte then I can manipulte the value and hopefuly add a new record with my new value. Any help would be great. thanks
I have a table named 2984. 1st column is ServiceNo which is number values stepping up in increments of 0.5.
I want to find the largest value in the column, then add 2 to that value and enter a new record with the new value. The rest I'm sure i will be able to work out. The code I've been playing with is below. I know why it doesn't work but I don't know how to make it work. I want to get the value and call it Dte then I can manipulte the value and hopefuly add a new record with my new value. Any help would be great. thanks
Code:
Sub New_Day_Member_Table()
Dim Dty As Single
Dim Dte As Single
DoCmd.SetWarnings False
Dte=DoCmd.RunSQL "SELECT MAX(ServiceNo)FROM 2984;" ' i want to assign Dte to the value of the highest value in ServiceNo
Dty = Dte + 2
DoCmd.RunSQL "INSERT INTO 2984(ServiceNo, Duty) values(' " & Dty + 2 & " ', ""Day"");"
DoCmd.RunSQL "INSERT INTO 2984(ServiceNo, Duty) values(' " & Dty + 0.5 & " ', ""Night"");"
End Sub