Basic principle would be to use code to loop through the recordset. Depending on volumes could be quite slow
think you will struggle since the only thing that connects the data to the date is its position. And in databases, position means nothing without an order so your recordset might not be in the order you see as displayed. Might be better to use Excel which makes use of relative positions
Where does this data come from? If imported, suggest add an autonumber primary key field. Providing the order remains the same, then it will be possible to do with a query
Note that Date is a reserved word (it means today and is also a datatype), using it as a field name is likely to cause problems.
Also, you need to clarify what happens to the rows with just a date - are they to be removed or retained.
Basic code would be something like
it may be plant is not null but a zero length string in which case you will need to change the if line toCode:dim rs as dao.recordset dim tmpDate as date set rs=currentdb.openrecordset("Select * from Table1) while not rs.eof if isnull(rs!plant) then tmpDate=rs!Date else rs!date=tmpDate rs.update end if rs.movenext wend rs.close set rs=nothing
if rs!plant="" then
and as already mentioned, the order might not be what you expect - the above relies on the order being as displayed in your example
The data is handwritten and treated as a string of characters. I mainly mean how each of these dates can be copied like on screenshoot, thank you for the quick reply !!
It appears that you have experience with Excel when you reference "cells".
And also that you are entering data directly into a table (guess on my part) -- a practice frowned upon/avoided by most.
Data entry is normally done via a Form.
A table contains/stores records.
A record is made up of fields.
Each record has identical structure.
The "date", if handwritten, would be entered with each record.
If using a form and some vba code, you could enter the date once for each group of records with that date value.
You would need some logic to supply that date / update each record with that date value.
However, more details on what you are trying to automate and what/how you will use the recorded data would be helpful to readers who are trying to offer focused advice.
Good luck with your project.
Generally, the entire table is taken from the excel form as a string of characters entered by the user. The problem appeared at the automation level already in Access itself.
My whole problem is editing the table in access using VBA, the program loads files via the VBA function, but also has to edit this file as I have presented.
Generally, I mean copying the notes line by line and changing characters when the string of characters changes
Based on your latest post, it appears you are overlooking or unfamiliar with some of the basics of database.
Hmmm. Please give us some details on what you are doing, how you have done it and what exactly you have determined to be an automation level problem in Access itself.The problem appeared at the automation level already in Access itself.
It may be an error but we need more info to help resolve your issue.
Access (database) is built on a different underlying model than Excel (spreadsheet). Different concepts, different terminology and experience in one doesn't necessarily translate as a positive thing in the other.
I suggest you tell us what you want and step by step of exactly what are doing.
now totally confused.
Suggest us step by step through your process, starting with the user handwriting into excel
1. user enters data into excel in the format shown - is this always this format? i.e. separate columns, all one column
2. data is copy/pasted//imported using import wizard//vba code transferspreadsheet to a temporary/permanent access table
3. and clarify what 'changing characters when the string of characters changes' means