Recent content by igillesp

Because the average dataset has 86 columns (single year of age to 84, plus 85+ years) and 64,964 rows (32,482 geographical locations with data for both males and females), and therefore you end up with over five and a half million records. Getting the data in was easy, but writing the code was...

Not using it for analysis in this instance - just data manipulation. Iain

Hi all I often have a requirement to 'normalise' (not sure if that's quite the right term?) population data with lots of age columns () into a single table with few columns and multiple rows (). I wrote the code below to do this. Briefly, it: i) Cycles through the field names of the...

This worked a treat, although I modified it... If (Asc(strTmp) = 32) Or _ (Asc(strTmp) >= 65 And Asc(strTmp) <= 90) Or _ (Asc(strTmp) >= 97 And Asc(strTmp) <= 122) Then ...as it was stripping spaces between words. Thanks! Iain

Thanks! I think this had a type on it, but a corrected version... SELECT HUS.MolisID, HUS.DIAGCODE, Asc(Left([HUS].[FILLV1],1)) AS BadCharNumber FROM HUS WHERE (((Asc(Left([HUS].[FILLV1],1))) Not Like "[A-Z0-9]")); ...returned character 28. I adopted code from this page... Public...

The latter. I'm using a make-table query to write the results to a table called HUS. I've writtent the code below to concatenate the multiple entries, but would like to incorporate a way of stripping the extra characters from the HUS.FILLV1 field. Iain Public Sub DiagCode()...

Hi all I'm pulling text out of a LIMS Database via ODBC and the bit I want is surrounded by strange characters which I'd like to remove (example attached). Any ideas how to do it in VBA or what the character code is? Thanks, Iain

Solved by adapting code from elsewhere (http://www.vbaexpress.com/forum/archive/index.php/t-3115.html) Final code: Sub ChangeControlName() Dim dbs As Database Dim ctr As Container Dim doc As Document Dim frm As Form Dim ctrl As Control Set dbs = CurrentDb Set ctr = dbs.Containers!Forms For...

Thanks, but I'm now getting "runtime error '438': object does not support this property or method" on... For Each ctl In frm.ControlsAm I missing a reference, or just the plot?

Thanks, but only half solved. For one...For Each frm In Forms...only recognised open forms, so I have to open them manually. This isn't a huge problem, but it would be nice to know how to do it properly. Second, I get "Compile error: Syntax error" with...Replace(ctl.Name, "Frame", "YN")Any...

Hi! I've got ~10 forms in a database, each with numerous (say 15) option buttons in frames. Each is named [variable name]Frame, but I'd like to name them all to [variable name]YN to reflect their purpose. Rather than renaming each by hand, is there a piece of VBA which will do this...

AAAARRRGGGHHHHH!!!!! Solved! I'd tried MsgBox FilePath previously and it seemed fine, but following your post I tried Call Shell instead and got File Not Found. Its amazing the difference you get when you put "jpg" instead of "jpeg" into... FilePath = "S:\Images\" & RegName & ".jpeg"

Actually I think you're being incredibly harsh. I think that it should be obvious, considering that this is my seventh post, that I'm a VBA novice, and that I and others like me should be encouraged rather than slated for making amateurish mistakes. The fact is that I was actually trying to...

Good spot, but that was just a typo when I was copying the code over. I did have... Dim FilePath, FullFilePath As String FilePath = "S:\Images\" FullFilePath = FilePath & RegName & ".jpeg" ...but realised that I could combine the two whilst copying.

Hi! I've got an XY chart (objScatterGraph) which I use for mapping. The background image is the whole of England and Wales, and I'd like to be able to change this to specific regions depending on the choice of a combo box (cmbRegion). However, the following... Dim RegName As String Dim...