Topics Search

Inserting a DATE from Recordset into Table

Inserting a DATE from Recordset into Table
Views: 15
Getting a Syntax error when trying to insert a date from recordset into a table. If I take the date out of the insert is works without issue. Abbrevaited code looks like:


Set rsP = CurrentDb.OpenRecordset("SELECT " & _
"FROM RawData " & _
"WHERE RawData.Date = #" & VDate & "# " & _
"AND RawData.Channel = """ & VChannel & """ " & _

DoCmd.RunSQL "INSERT INTO tblRankings (Date, Channel) & _
"VALUES (#" & VDate & "#, """ & VChannel & """)"

If I remove the VDate from the DoCmd.RunSQL everything works perfectly. I can see in the debugger that my VDate is holding the correct date however the syntax is wrong on the insert. Why it is not the same as in the recordset is confusing to me.
Sponsored Links:

More topics


Move through recordset inserting new records

I have a table which appears as follows:


CumSales = Cumulative Sales

In the table, I have missing dates. I want to add the missing dates. For example in the above, 2nd January is missing so the new record would be:


When adding a missing date, I will use the cumulative sales from the last reported date before the new record being added.

I have a few hundred stores and about ten products for a period of two years so it's quite a lot of data to go through.

I will be adding data to the table in future (with missing dates) so I will need to run the update frequently to fill the gaps.

I wanted to do it in a query but couldn't figure it out.

Instead I am trying to do it in VBA. The code fails when a new record is inserted into the table. It causes the recordset to position itself at the end. I tried to reposition the recordset but I am getting an error which says, "Provider cannot determine the value.

Update Table - SQL or Recordset Edit

I'm relatively new to Databases and have been working on some projects. I have a doubt of what methods are more appropriate when updating or inserting data in a table. Here are the two methods that I use.

1. Open e snapshot recordset, calculate things and insert or update with sql the result in the table. Example:

Dim DataSet as DAO.Recordset Set DataSet = CurrentDb.OpenRecordset("Table", dbOpenSnapshot) CurrentDb.Execute "INSERT INTO Table VALUES (Example)"

2. Open a recordset and use the edit command to update it

Dim DataSet as DAO.Recordset Set DataSet = CurrentDb.OpenRecordset("Table") DataSet.Edit DataSet("Field") = Example DataSet.Update

I don't know which one is faster or more appropriate. any help is very appreciated.

Recordset get records from Recordset

trying to open a recordset from a select statement off of another recordset. access can't find the input table or query.

Is it not possible to open a recordset off the results of a prior opened recordset

Find first date in recordset to use for calculated control

I have an invoice report that has a calculated control for billing cycle. Currently it is set to

=[Enter start date] & "-" & [Enter end date]

This is because the start date depends on the date of the first charge and changes every month, so the user enters the dates simply for display purposes. What I was hoping to do is have the start date determined automatically based on the underlying recordset. Is there a way to find the first date in the recordset and use it as the start date in the calculated control

Problem with UpdateBatch statement - empty date fields no longer empty

I've created a form, data is pulled from a table into a recordset, and the form fields are populated from the recordset. I've got a little "next" and "previous" button which, upon being clicked, first update the recordset and the table to reflect any changes made on the form, and then move to the next or previous record.

I'm facing a problem with date/time fields in my table. If the date/time field in the table is blank, and I press the "next" button, it populates the field with a date from theprevious record, and pushes it into my table.

I tried to solve it in my UpdateRecords sub by pushing a blank field into the recordset if the form field is blank (as seen in the code below).

Here's some (shortened) code samples. I didn't show a sample of the FillData sub because I don't think the problem is there. All data is showing up correctly on my form.

DAO.Recordset Vs ADODB.Recordset Vs Recordset

So I know that there are two types of libraries for retrieving recordsets, either DAO or ADO.
I read somewhere that if you are using DAO, you should use DAO.recordset instead of just "recordset" to make sure it run properly.
Dim rs As DAO.Recordset
instead of
Dim rs As Recordset

but if the default is DAO anyways (I think), would it really cause any problems. (I'm using Access 2003)

So now, which is better DAO or ADO if I'm only using a recordset to update a single field of a record from a table that may contain hundreds of records.

Which is better in general? What exceptions are there? What do other people use

Add query recordset results to Table

I am trying to add a recordset to an existing table using VBA/DAO from Excel. I'm stumbling on this because the recordset is not a table but a result of a query. The below does not work. Myrecordset is the recordset created from running a nested parameter query...

Insert query inserting 2 same records in the table

My below sql inser statement is at first inserting one row in the table but after some time it automatically adds one more row in the table

strSQL = "INSERT INTO RELEASELIST ([Release Name],[Production Date],[Code Delivery Date],[Reg1 Completion Date],[Reg2 Completion Date],[Imp Test Completion Date],[Content Type],[Modified],[Created],[Created By],[Modified By] ) VALUES ('" & txtReleaseName & "','" & dateProd & "','" & codeDelDate & "','" & regression1date & "','" & regression2date & "','" & impcompdate & "','Item','" & currDateTime & "','" & currDateTime & "','" & strUsername & "','" & strUsername & "')"

DoCmd.RunSQL strSQL

Recordset Update Issues

I have a button that filters a form filled with dates from two tables.

It filters the Season to Seasons and the DevCode to DevCodeA and pulls the marketingname from the current form for display.

The dates pulled from the first table are set and do not change (except from the table by a manager) the dates from the second table need to be updateable as they are "actual" dates.

Name | Set Date | Entered Date
Milestone | Mgr Set | Actual Date

Right now nothing is updateable. I am getting a recordset not updateable error.
I check both date tables and they both have primary key fields (autonumber) and all of the other fields are not indexed.

Is it something to do with the filtering?

inserting values in column based another column

I have a table having a column of Date of Birth. another column named Date of Retirement. Now I want to create an update query that fills the column date of retirement by adding 60 in year of Date of Birth.

I have 1200 record in my table so I want to fill it by query.