Search results

Access 2003 I am working with two controls - tblStatusStep (integer) - ctlCustomerName (string) I am trying to get the background color of ctlCustomerName to change based on the value of tblStatusStep 0 to 10 = Yellow 11 to 20 = Orange 21 to 30 = Green 31 to 40 = Blue 41 to 50 = Violet All...

I have a string "strMatrix" which is an SQL Select Statement I am trying to export the results of strMatrix to an Excel (2003) named range "Matrix" in c:\Matrix.xls Originally, "strMatrix" was a select query and the Docmd... worked fine. I copied the select SQL Statement and Dim'd it as...

I'm getting a Run-time error '3011' The Microsoft Office Access database engine could not find the object 'strMatrix'. Make sure the object exists and that you spell its name and the path name correctly. Can someone tell me what I'm missing? Dim strMatrix as String strMatrix = "SELECT ..."...

RESOLVED! argh! The problem was I had data in the Named Range of the Excel Template

Development in Access 2007 & Excel 2007 To be deployed on Access 2007 & Excel 2007 AND Access 2007 (runtime) & Excel 2003 All, This code runs fine when it is executed on Access 2007 and Excel 2007. However, it fails on Access 2007 RUNTIME and Excel 2003 "Error 3434 "Cannot expand named range""...

Resolved!!!! Right.. I do thank you guys for your help!

Hi Bob, This worked !! thanks Also... When I commented out the Close I get an Error 3010 .. 'Table ExecReportData' already exists

Thanks Bob / Trevor.. Here is the end result. I've included the recommendations you guys made and I added a piece at the end to open the file Once I fire off the code, I get an Excel message. THis will be running on both Office 2007 machines and Office 2003 with Access2007 runtime machines...

Thanks Trevor... That got me to Should objXLApp also be an object? Set objXLApp = New Excel.Application

All I need to run this routine without adding the reference to use of Excel 12.0. This will be running on machines with Office 2007 as well as Office 2003 (Access2007Runtime) The routine throws a Compile Error: User-defined type not defined at "Dim objXLBook As Excel.Workbook" Any ideas on how...

You're right about tacking together code. I can "usually" modify code to suit my purposes as I haven't developed my own skills yet. I can see StripIllegal is overkill for my needs as it looks for ASCII characters and then removes them. Only keeping numeric values will run more efficiently...

The Phone was spot on. you're right "Invalid Use of Null". If I comment out Else: GetAlphaPHONE it seems to run fine. What's the risk of eliminating the Else statement? I need to strip all Alpha and "special characters". So I run strAPhone = "UPDATE Contact SET Contact.account_phone =...

I've added a function... GetAlphaEXT to GetAlphaPHONE I wasn't sure how to bring it all into one function and add the boolean argument as Galaxiom suggested. When I put this in a query, the "Ext: GetAlphaPhone([account_phone])" seems right but the "Phone: GetAlphaExt([account_phone])" does...

Ok.. So it wont be as easy as I thought to clean these fields up. I was able to clean the Ext field by striping all non-numeric characters . To strip the result of GetAlphaString from [Direct_Phone], I've been trying unsuccessfully to use this to strip the "GetAlphaString" from the...

I simply added Not Null criteria to the query. So tell me.. does it actually hurt to be so smart?

Here is what I've got.... Public Function GetAlphaString(strMyString) As Variant Dim i As Integer Dim iAsc As Integer strMyString = Nz(strMyString, "Invalid") For i = 1 To Len(strMyString) iAsc = Asc(Mid(strMyString, i, 1)) If (iAsc >= 65 And iAsc <= 90) Or (iAsc >= 97 And iAsc...

Hey Galaxiom... Thanks for helping me this... I put Extension: GetAlphaString([Direct_phone]) into a query and executed it and I am being thrown a Run-time error '94': Invalid use of null at For i = 1 To Len(strMyString) and #Error in the query results grid. .. is this what you meant by...

I've found this.. but I'm not sure where to go with it Public Function GetAlphaString(strMyString) As String Dim i As Integer Dim iAsc As Integer For i = 1 To Len(strMyString) iAsc = Asc(Mid(strMyString, i, 1)) If (iAsc >= 65 And iAsc <= 90) Or (iAsc >= 97 And iAsc <=...

Thanks Bob... It may have any alpha character (nothing is ever easy)

Access 2007 SQL 2005 I have a field Contact.Direct_phone (varchar) that may include numeric special and alpha characters. example +44112 12345 ext 117 Basically, I need to move the extension to Contact.DirectPhoneExt. What can I use to find the first alpha character "e" in the above example...