db.OpenRecordset error (Too few parameters, expected 1) (1 Viewer)

[sorenwrang]

Hello

I am trying to do some simple table operations. I have a field (Date) containing dates, and an empty field called Day.

I want to extract the day number from the Date field, and write it to the Day field.
I didn't get very far until I ran into trouble when setting my recordset. I get the error "Too few parameters, expected 1". Clicking "Debug", will highlight the code line "Set rs = db.OpenRecordset(sqlString, dbOpenDynaset)".

So far, my code looks as follows:

Private Sub Command16_Click()

   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim sqlString As String
   Dim dataDay As Byte
   
   'Open connection to current Access database
   Set db = CurrentDb()
   
   'Create SQL statement
   sqlString = "SELECT Date,Day FROM MovesOverview_vessel"
   
   Set rs = db.OpenRecordset(sqlString, dbOpenDynaset)
   
   Do Until rs.EOF
        dataDay = Format(rs!Date, "dd")
        rs.MoveNext
   Loop
   rs.Close
End Sub

I don't really see my mistake.

Please note, that I am not very familiar with the various types of recordset settings. I just want to be able to read data from the Date field, and write data to the Day field.

 

[Galaxiom]

Date is a reserved word. Use a different fieldname.

I am assuming you are just doing an exercise as such a table would be entirely pointless.

 

[pr2-eugin]

Hello sorenwrang, Welcome to AWF

Date, Day are both reserved words in Access. Try wrapping them in square brackets and try again..

SELECT yourTable.[Date], yourTable.[Day] FROM yourTable;

 

[CJ_London]

Also:

looks like Day is not a field in your MovesOverview_vessel table since you are calculating it later

Day is also a function so you can use

dataDay = Day(rs!myDate)

Finally, your dataDay is continually being recalculated so when you drop out of the loop, it will only so the day for the last record in the recordset so not quite sure what you are trying to acheive

 

[namliam]

Indeed unless you are building a datawarehouse dimensions (or is it measures table, I always screw them around), having this table is indeed pointless.

If it is DWH related you will want mucho moro in information wize, so why not add that too?

 

[sorenwrang]

Thanks for the swift replies.

I know that the function is not working correctly at the moment - I was just testing things...

The reason for doing this operation, is, that the data is going to be used for some linear programming exercises. Hence, I need day number (1-365), rather than the date format. So what I will end up doing is determining the exact day of the year, depending on month and day from the Date field, for all my rows.

Maybe I wasn't clear. I am not building a new table, but modifying an existing. The two fields Date and Day are just two among a sh*tload of fields in my existing table

 

[namliam]

format(AnyDateHere, "y")

Will return the day of the year for you, no problems

Plus if you want to know the number of days between two dates you can simply do
ThisDate - ThatDate
Or any number of functions that you can do on stuff like this

 

[sorenwrang]

format(AnyDateHere, "y")

Will return the day of the year for you, no problems

This just saved my day. Thank you SO much ! I assume this "format" will consider leap years correctly, ye?

 

[namliam]

?format(#12/31/2010#, "Y") & "/" & format(#12/31/2011#, "Y") & "/" & format(#12/31/2012#, "Y") & "/" & format(#12/31/2013#, "Y") & "/" & format(#12/31/2014#, "Y")
365/365/366/365/365

Does a Mailman always deliver?

Keep in mind that format retuns a string value, which may cause sorting problems if you are intending on doing so.
Make sure to use a function to turn it into a number if you need it as a number.

 

[Galaxiom]

Keep in mind that format retuns a string value, which may cause sorting problems if you are intending on doing so.
Make sure to use a function to turn it into a number if you need it as a number.

If a numeric result was required one would use the Year() function.

 

[pr2-eugin]

If a numeric result was required one would use the Year() function.

Would not Year return the year Number rather than Day of the year?

? Year(Date)
 2014 
? Format(Date, "Y")
10

 

[Galaxiom]

Would not Year return the year Number rather than Day of the year?

Yes. Me bad. Not paying attention.

 

[namliam]

Actually instead of Format one can also use Datepart, which will return an integer.