Recent content by FlyerMike

I use Outlook as my e-mail client. I prefer to use HTML format, althought the old school folks might prefer ASCII text messages. Dim ouApp As New Outlook.Application Dim MyMessage As Outlook.MailItem Dim sToAddress As String, sSubjectLine As String, sBodyText As String 'Bunch of code here...

Sounds like you need an array of a User Defined Data Type. This array consists of integers (generated by your RANDBETWEEN) and presumably a long integer (TALLY) that stores how frequently the integer has been chosen. Public Type TallyCount iMember as Integer lTally as Long End Type...

Have you tried testing the expression Nz([Question J/N],"Null")? I'd run the query (without a WHERE clause) using this item and check the unique values associated with it.

You could try a class module. The array would have class scope, as opposed to the life of the original routine. It worked in my test. -Mike

SELECT [asset class] , iif(ct2.[asset class]='3050', 'yes', 'Nope!') as IIF_Example FROM CONVERSION_TABLE_2; I think the As was misplaced...

Have you tried the selection in multiple steps (as opposed to a single, concise line of code)? Dim sRange as String .... sRange = "(A2:" .Application.Selection.End(xlDown).Select sRange = sRange & .Application.ActiveCell.Address & ")" .Application.Range(sRange).Select

Have you tried dots (.) before the two instances of Selection? Or perhaps change Selection to .Application.Selection?

Since you are intending to link to another table with leading zeros, you may want to try a series of update queries. First look for those records where the length of SAP CO is 1. The Update value for this field is '000' & [SAP CO]. Repeat where length of field is 2, update with '00' Repeat...

The length (Len()) of a field will be numeric. Try the following: SELECT a.[FA Ctr], a.[SAP Co], a.[SAP Ctr], a.[GL Co], IIf([Len([SAP CO])] =3,'0' & [a].[SAP CO], IIf([Len([SAP CO])]=2,'00' & [a].[SAP CO]), IIf([Len([SAP CO])]=1,'000' & [a].[SAP CO]", "[A].[SAP CO]"))) AS Expr3 INTO...

I'm not familiar with the package wizard. When I distribute an application, the app checks for a list of linked tables. If a table is not linked, the app is directed to a server location and given the table name and password to link to the table. The details of the link changes for an SQL...

How are you linking to the SQL Server tables? Do you authenticate the user and then link to the tables? Is it possible that the test users don't have proper privileges to the SQL server?

Are you really doing this through a macro, or are you using VBA code? In VBA code, I use either: VBA.vbNewLine, or VBA.Chr(13) & VBA.Chr(10) (Carriage Return and Line Feed) ...in order to add blank lines to a log file or Message Box.

Dan: See the attached example.

I'd add another Table: Table: Kits Kit ID: Long (lookup from Kitchen Sink where type = "kit") Part ID: Long (lookup from Kitch Sink where type = "part") I'd use this table to declare the parts that comprise a kit. I'd populate it with a continous subform linked to the record from Kitchen...

Another approach is that the master summary sheet would call the 18 subordinate sheets for updates. The only burden (as far as read-write conflicts) is with the summary file. I would open the subordinate files as read-only, grab their data, and move on to the next file.