Access newbie- get value and manipulate it

vinny2984

Board Regular
Joined
Sep 22, 2008
Messages
202
I'm just starting to play with Access after getting a basic grasp of VB in Excel.
I have a really basic problem which despite hours of surfing I can't find the answer to;

I have done a very basic table with 2 fields. Table called 2984 and field 1st field called ServiceNo.
The serviceno values stepping up incriments of 0.5

All i want to do is get the highest value in the column ServiceNo and add 2 to it ( the rest i'll be abe to work out). then put the new value back into the table as a new record. I've been round and round in circles with code so i must be missing something very basic in my understanding.
Any help would be great to get me started, thanks

I have been playing with the following code without any joy
Code:
Sub New_Day_Member_Table()
Dim Dty As Single
Dim Dte As String
DoCmd.SetWarnings False
[COLOR=red]Dte=DoCmd.RunSQL "SELECT MAX(ServiceNo)FROM 2984;"[/COLOR]  [COLOR=lime]' i want to assign Dte to the value of the highest value in ServiceNo[/COLOR]
    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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

vinny2984

Board Regular
Joined
Sep 22, 2008
Messages
202
sorry, whilst trying many variations i've changed my original,
Dim Dte As String should be Dim Dte as Single
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,545
Members
414,316
Latest member
ExcelLee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top