Search results

Thanks CJ - you're a star! Fixed it good and proper you did. :D

I run a MakeTable Query on some data to COunt the number of records and Sum the Value of $$$ in a Currency field. The results produce a Number for the Count, but the SUM of the Balances is returned as text. To get the SUM I use the expression SumBal: DSum("[Balance]","[XAM_data]") Anyone...

Bad eyesight causes them, on occasion ... ;)

I have some code that among other things processes text files. These text files contain individual records and lots of other "rubbish" (not really) that we don't need to track or save. Having processed the file and sanitized the records to reflect only the information we want to record, we write...

So I've been beavering away at my SQL stuff (with Baldy's clever little scheme with Debug.Print strSQL doing yeoman's work for me ... I created a query in Access' design field and the resulting SQL provides a perfect result - it looks like this in the SQL view of Design SELECT...

Well - Tommy Docherty's reign wasn't exactly stellar in the results category. :rolleyes: One thing is certain, other teams would be better off to let their managers have a little time before firing them - its become a laughing stock, the way that so-called intelligent businessmen hire and fire...

I'd love to see the stats of how many other Managers have been in the Premier league during Ferguson's tenure, and how many trophies they have amassed.

Thank you! That is avery useful and I've already integrated it - greatly appreciate your tip! :D

Set prst = CurrentDb.OpenRecordset("SELECT Count ([AHC_data].[AccountNo]) AS CountOfAccountNo, DSum ([AHC_data].[Balance],AHC_data) AS TotalBal " & _ "FROM [AHC_data] " & _ "WHERE [AHC_data].[Processedfile] =...

strSummarytovault = "UPDATE Vault " & _ "INNER JOIN File_Summary ON Vault.[Processed_File] = [File_Summary].[File] " & _ "SET [Vault].[Balance] = [File.Summary].[Total Value];" Comes out to this .... "UPDATE Vault INNER JOIN File_Summary ON Vault.[Processed_File] = [File_Summary].[File] SET...

Having just been wrestling with the concept myself recently, this place is a great resource as is Access and SQL

I've been having lots of fun and help from around here withimporting text files of late. Insofar as unwanted data is concerned, I got around that by importing the complete file and then writing a query to delete the records I did not need. My initial field in the recordset designated the type...

Hearing you on your Storing calculated values - I'm opposed to the idea as I think we can always get at it through a query, but others are not as convinced, and they want to have their own way ;) And thanks for including both scenarios - I'm trying to write to an existing record in this case...

I use a query to sum the outstanding balances that are included in a particular file to a summary table (I call it my Vault) The original file may include dozens of accounts, but all I want to write to the Vault is the sum of the balances. The query is named "Sum of Account Balances" - the...

Ah! Yes - that would be a good idea :D I must have thought you were all Veterinary surgeons who can diagnose without any specified symptoms ..... ;) Well, the symptom is that it refuses to write the filename to the table UPD, although it exports the data file. For some reason the UPDATE SET...

Am busy importing text files and its working OK, save for my attempt to write the filename to each record - its a bit of an after-thought, I suppose :o The challenge I'm having is wrting to the field, only if it is empty. That prevents the code from overwrting every record with teh filename...

Thank you Estuardo - that was one of the solutions I pondered. I ended up using FileDateTime and FileLen - both VBA functions. And they worked! Thanks again and welcome to the Forum!

I'm able to import the contents of the text file into a table, thanks to the help of folks in this forum. What's the best way of tying the data from the text file to the filename and its date? As the files are relatively small in size and they later get scripted into another system, I think...

Thanks for the inspiration I get from these pages, I identified my own problem - I was not defining the Import Spec as a variable and including it in the DoCmd.TransferTxt ..... I think I will thank myself! ;) Thanks again to Beetle and CJ though ....

Well, I solved the initial problem ... The files were arriving as *.COR. My initial idea was simply to add .txt to make them text files .... so they were *.COR.txt .... This was causing an issue. When I remove the .cor the process worked ... sort of ... it processes all the files but lumps...