Search results for query: *

It is unusual that I solve my own questions, but I did. It turns out that even though the number was showing as a short decimal when opened in excel, it was actually a Large Number. So my data type was wrong. It only took me 4 hours to figure it out. I am leaving this post incase anyone else...

I have the following line of code... DoCmd.TransferText acImportDelim, "Chart Import Specification", "tblMeterReads", fileName, True, , 1252 I can manually run chart import specification with no problem. When I use DoCmd TransferText it imports all of the records, but skips a field that...

Andy, I have run into a wall and will definitely take your advice. If I can bring the csv into an access table using vba, it would solve a lot of problems. My only concern is that tblImportTemp is the source of an append query. It should not make any difference though. I will report back...

dhookum, you definitely pointed me in the right direction. In fact, I went back to the first in the series of articles and I am very intrigued after reading a couple of paragraphs... The first article is found at...

I will check it out... Thank you!

Andy, Great suggestion from a troubleshooting standpoint. Abandoning the saved import and doing a new step by step import would be faster though. dhookum, I will see if I can find out how to access system tables. I just setup a VBA import procedure that works great, except I get errors...

I tried different ways of renaming the xlsx file and none worked. One thing that stands out to me is that Acceess defaults to the original file name when creating the table during the import. I am not at all sure where access is getting that information as the table is already renamed before I...

Hi, My work flow is to download a table as a csv from a third party and then save it as an xlsx file. I give it a new filename when I do a saveas xlsx, because the default filename from the third party has special characters in it that are not compatible with access import. The import works...

dhookum, you were correct. I had one record that had no data and it that is what caused the problem. Thank you! Andrzejek, I see where you are going with split() and I am happy you showed that to me. I am going to toy around with split() as this may help in future datasets, I come across any...

dhookum, I think you bring up a great point. Maybe there is a field that is not populated. I will check. Thank you! Andrzejek. I will try your suggestion and write back. Thank you!

I have a field [Bill Period] as short text. The data is provided to me as such, "11/07/22-12/07/22", which represents a start date and an end date. I extracted the start day and formatted it as a date using the following expression. StartDate: CDate(Left([tblWegoWiseSpikes]![Bill...

Okay, I think with my new mindset, this should be pretty easy. The goal is to get tblProperties > tblBuildings > tblSpikes normalized. Those three tables alone will make up the database, unless of course I decide to create a separate table for each utility. If I keep my eye on the goal, it...

I am using update in tblSpikes because it has no values in the fkeybuildings field. I need to update those values.

I think I finally figured out where I am going wrong. I need to do more with the original temporary dataset to assign the correct building id's to the fkeyBuildings field in the spikes table as I am appending the new records to tblSpikes. This way, tblProperties, tblBuildings and tblSpikes...

The final query that I posted uses only spike and buildings table to assign building id to the building foreign key in tblSpikes. In that last query, tblProperties is not used and it works.. Is this one finally correct?

This also seems to work... UPDATE tblBuildings INNER JOIN tblSpikes ON (tblSpikes.Property = tblBuildings.Property) AND (tblBuildings.Buildings = tblSpikes.Buildings) SET tblSpikes.fkeyBuilding = [tblBuildings].[pkeyNewBuildingID];

Thank you for the very important correction. I am referring to the excel file that was a csv and I do plan to take your advice and not put it in excel at all and I will stop referring to it as a table. These are the tips that I really appreciate because if I learn to speak the language, it...

Thanks for pointing me back to the post. I completely understand the structure and understand its necessity and I find it very easy to create a normalized database if I am creating the records. I would select a property from a property dropdown and a building from the building dropdown and all...

I thank you for the generous amount of time you have spent helping me to figure this out. While I am unable to accomplish the perfect database, it appears to be accomplishing what I set out to do, which is to create a worksheet for each spike that will allow me to report or list a history of...

Still working on getting account numbers. That would make everything much easier as they should be unique, even though from multiple utilities. The solution there might be to add a prefix to the account numbers that describes the utility provider. Ie. NG12345 for National Grid. and EV12345...