I need to develop a query that identifies all of the records from one table that are not contained within another table. Inner Joins will not work. I know that there is an approach that will do this but I do not remember it.

I do not even know where to start with this request, but I need to be able to write some text to about 40 years worth of a MS Outlook Calendar. Is it possible to programmatically write to a MS Calendar in Outlook in a loop? for each item in Microsoft.Outlook.Calendar(myName@email.com) Outlook.Date.Text = "Text to Write" Next Are there some articles about writing code for Outlook Calendars that you could point me to? Any assistance would be appreciated.

I am working on a MS Access database that has a central table with a composite key consisting of seven elements, and another table also with the same seven composite key elements. The central table composite key is unique thereby making it a composite primary key. The other table has multiple records of the composite key. In other words, there is a one to many relationship between the main table and the other table using composite keys. I need to create a form with the composite key elements on the top of the form and a subform with the multiple records matches to the composite key from the main form. The problem is the MS Access only provides the ability to create a subform using three elements from the seven elements of the composite key. What would be the solution to allow me to create a subform that matches all seven elements of the composite key form the "many" table to all seven elements of the primary composite key from the central table? :confused::confused::confused:

modified on Thursday, December 3, 2009 9:26 PM

I need some assistance with a small application. I need to loop through an existing array and to assign a value to it in a pattern. I thought that I could do it, but I am really at a loss as to how to proceed. I tried a wide variety of approaches but it does not yield the correct results. As a smaller scale version of the array. Let us say that the array consists of 392 elements. The first element of the array consists of date values from 12/30/2007 and it proceeds up until 1/24/2009. The second element should be number values from 1 to 13. The pattern is that there should be 28 values with the value 1, and then 28 values with the value 2, and then 28 values with the value of 3. This procedure continues with 28 values of the value 13. After the 28th value of 13, the numbering system should start over again with 28 values with the value of 1. This should populate all of the elements of the 392 element array. The code below that I have written, for some reason, does not include the 28th value for each of these assignments. Also, it includes a 14th value following the last 13 value assignment. Please provide me with some assistance! :confused::confused::confused:

Public Function ZodiacPeriod(dteInputDate)
Dim dteStart As Date
Dim dteEnd As Date
Dim intDateCount As Integer
Dim intRow As Integer
Dim intX As Integer
Dim intCounter As Integer
Dim arrArray(392, 1)

intX = 1
dteStart = #12/30/2007#
dteEnd = #1/24/2009#
intDateCount = DateDiff("d", dteStart, dteEnd)

'Loading the array with dates
For intRow = 1 To intDateCount
    arrArray(intRow, intColumn) = dteStart
    dteStart = dteStart + 1
Next

'The second element value is assigned here
For intRow = 0 To 392
    If intCounter <= 27 Then
        If intX >= 14 Then
            intX = 1
        End If
        intCounter = intCounter + 1
    Else
        intCounter = 1
        If intX < 14 Then
            intX = intX + 1
        End If
    End If
    arrArray(intRow, 1) = intX
Next intRow

'Now to go through the array and return the value in the array
For intRow = 0 To 392
    If arrArray(intRow, 0) = dteInputDate Then
        ZodiacPeriod = arrArray(intRow, 1)
        Exit For
    End If
Next intRow

End Function

I have encountered problems in which a value of 14 is assigned. I have encountered problems in which the requirements are not ma

I am working on a customized user function in MS Excel that breaks up the calendar into thirteen 28-day periods. The objective is to have as many 28 day periods in a row as possible. The standard monthly calendar consists of months in which some months have 30 days, while other months have 30 days, and occasionally there is a leap year with 28 days in February. However, for the month of December in the years 2009 and 2015, there are 35 days instead of 28 days. In a spreadsheet, place the date 12/30/2007 and then pull the drag handle down so that the last cell is 12/31/2016. To the right of each of these cells is the place where the function cell result should be placed. Now we need to create the place to add the code for a customized user function. This can be accomplished by pressing the Alt-F11 at the same time. A window opens in which code can be written for the function. Select "Insert" > "Module". Now there is a place to put the code. Here is the code for the custom user function:

Option Explicit

Public Function ZodiacPeriod(dteInputDate)
Dim dteStart As Date
Dim dteEnd As Date
Dim intDateCount As Integer
Dim intRow As Integer
Dim intColumn As Integer
Dim intX As Integer
Dim intCounter As Integer
Dim intYear As Integer
Dim arrArray(3290, 1)

dteStart = #12/30/2007#
dteEnd = #12/31/2016#
intDateCount = DateDiff("d", dteStart, dteEnd)

For intRow = 0 To intDateCount
    arrArray(intRow, intColumn) = dteStart
    dteStart = dteStart + 1
Next

intX = 1
intCounter = 1

For intRow = 0 To 3290
    arrArray(intRow, 1) = intX
    If (Year(arrArray(intRow, 0)) = 2009) Or \_
        (Year(arrArray(intRow, 0)) = 2015) Then
        If (Month(arrArray(intRow, 0)) = 12) Then
            If intCounter <= 35 Then
                If intX >= 14 Then
                    intX = 1
                End If
                intCounter = intCounter + 1
            Else
                intX = intX + 1
                intCounter = 1
            End If
        Else
            If intCounter <= 27 Then
                If intX >= 14 Then
                    intX = 1
                End If
                intCounter = intCounter + 1
            Else
                intX = intX + 1
                intCounter = 1
            End If
        End If
    Else

Read this article: http://office.microsoft.com/en-us/excel/HA011117011033.aspx?pid=CL100570551033[^] OK. I have an array created inside the function, and then I search through the array for a match of a value and return the second value as the result. The problem is that it does not make sense to create the array each time the function is run. Can it be stored some place else so that the function has access to it?

I need to develop a specialized user function that will search an array consisting of 16500+/- records and two columns. If I were to load the array each time the function is used, and if there were 10,000 records in the spreadsheet to whic the formula should be applied, it would take a great deal of time. Thus, it would be more appropriate to store the array as a global variable and to only search throuh the array in the function. Is it possible to store the array as a global variable and to access the variable inside a customized function? To create a customized function, press Alt-F11 key at the same time and then "Insert > Module". A new customized function can be written here, but it has a certain file size limitation that will not accommodate the two dimensional array. To use the new function select "Insert > Function".

so that a customized user function can access it in order to search through it? The array should not be stored in a spreadsheet but it should be stored programmatically as a global variable. It appears that there are limitations to the size of a function file so it cannot be stored in the customized function file itself. Plus, it does not make sense for the function to load the array each time that the function is called.

modified on Wednesday, May 13, 2009 10:27 AM

I am working on a customized Excel VBA function. I have developed a two dimensional array consisting of two fields. The first element is a Date field and the second field is an Integer field. The problem is that now I need to search through the array using the first field, and when a match is found to return the second field as the result. The name of the array is arrArray. It is a static array with a set number of records. How should I go about searching the array? As an example, let us assume that there are ten elements and that they are properly loaded like this:

   Dim arrArray(10, 1)

   arrArray(#1/1/2006#, 1)
   arrArray(#1/2/2006#, 4)

How do I search through the first element field for a match, and when a match is found to return the second element? :confused::confused::confused:

It worked!!! Woo Hoo!!! Thank you liqz, for your assistance in resolving this item!!!

Thank you, liqz for your assistance. The item that I have encountered in the implementation in the temporary copy of the application is: "Return without GoSub". The code is as follows in VBA: Public Function MakeSqlSafe(strData) As String strNewStrData = Replace(strData, "'", "''") Return End Function strSQLHyperion = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _ "[ALT_GROUPING], [PERSONNEL_AREA], [L_R_G], [REGION], [JOB_FUNCTION], " & _ "[PRIMARY_KEY] FROM [TBLHYPERION] " & _ "WHERE [COUNTRY]='" & UCase(rstInputFile![COUNTRY]) & "' " & _ "AND [TYPE]='" & UCase(rstInputFile![Type]) & "' " & _ "AND [BUSINESS_UNIT]='" & UCase(rstInputFile![BUSINESS_UNIT]) & "' " & _ "AND IsNull([ALT_GROUPING]) " & _ "AND [PERSONNEL_AREA]='" & UCase(rstInputFile![PERSONNEL_AREA]) & "' " & _ "AND [L_R_G]='" & UCase(rstInputFile![L_R_G]) & "' " & _ "AND [REGION]='" & UCase(rstInputFile![REGION]) & "' " & _ "AND [JOB_FUNCTION]='" & MakeSqlSafe(UCase(rstInputFile![JOB_FUNCTION])) & "'" Any assistance in resolving this item would be greatly appreciated!!!

I need some assistance in accommodating an apostrophe in an SQL statement using VBA in MS Access. Please help. strSQLHyperion = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _ "[ALT_GROUPING], [PERSONNEL_AREA], [L_R_G], [REGION], [JOB_FUNCTION], " & _ "[PRIMARY_KEY] FROM [TBLHYPERION] " & _ "WHERE [COUNTRY]='" & UCase(rstInputFile![COUNTRY]) & "' " & _ "AND [TYPE]='" & UCase(rstInputFile![Type]) & "' " & _ "AND [BUSINESS_UNIT]='" & UCase(rstInputFile![BUSINESS_UNIT]) & "' " & _ "AND [ALT_GROUPING]='" & UCase(rstInputFile![ALT_GROUPING]) & "' " & _ "AND [PERSONNEL_AREA]='" & UCase(rstInputFile![PERSONNEL_AREA]) & "' " & _ "AND [L_R_G]='" & UCase(rstInputFile![L_R_G]) & "' " & _ "AND [REGION]='" & UCase(rstInputFile![REGION]) & "' " & _ "AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'" What do I need to do to prevent it from hanging up on the apostrophe? The apostrophe needs to be accepted in: "AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'" :confused::confused::confused:

modified on Wednesday, December 3, 2008 11:31 AM

Here is the issue, the data file is not yet in CSV file format, so while the data is in VBA format in MS Access, it can be modified to a decimal format. Here is the code.

'Still in VBA in MS Access
strFileName = "qryAggregationGroupEntityMatching"
strSQLString = "SELECT tblBSMappingGroup.COUNTRY, " & _
"tblBSMappingGroup.ENTITY, tblBSMappingGroup.ACCOUNT_LABEL, " & _
"tblBSMappingGroup.GL_CATEGORY, qryAggregationRollingTotals.ACTUAL " & _
"FROM qryAggregationRollingTotals INNER JOIN tblBSMappingGroup ON " & _
"qryAggregationRollingTotals.GL_CATEGORY=tblBSMappingGroup.GL_CATEGORY"
Set qdfLinkedTables = dbsBalanceSheet.CreateQueryDef(strFileName , _
strSQLString)

'Exports to CSV
strOutputCSV1Path = strOutputFilePath + " - Grouping File.xls"
strMakeTableUpdateQuery = "SELECT qryAggregationGroupEntityMatching.ENTITY, " & _
"qryAggregationGroupEntityMatching.ACCOUNT_LABEL, qryAggregationGroupEntityMatching.ACTUAL " & _
"INTO tblExportCSV1 FROM qryAggregationGroupEntityMatching"
DoCmd.SetWarnings False
DoCmd.RunSQL (strMakeTableUpdateQuery)
strReplacementOutputString = "csv"
strNewOutputString = Replace(strOutputCSV1Path, _
strRemovedOutputString, strReplacementOutputString)
DoCmd.TransferText acExportDelim, "Standard Output", _
"tblExportCSV1", strNewOutputString, False

The question then is, how do I set it explicitly in Excel using code to decimal of 2 digits? I believe that it is something like:

Convert.Decimal(tblInputGroupFile.GL_AMOUNT, 2)

I know that this is not correct, but could you give me some idea as to how to convert it using an auxiliary function?

Actually, in the data can be formatted in the query but it would need to be done explicitly with code like CovertDecimal(AMOUNT, 2). Is there code like this?

Correct. When I put the cursor in the cell, it shows the value as being stored in the cell as .72 but it only displays .7. However, when I manually format it using "Number" with "2" decimal places, it shows the full value. If I open the CSV file and use the AutoSum feature, the aggregate total does not match the original data file. From this effect, I can conclude that it is not really being used in the calculation properly. To that end, I need to programmatically save the value as if I were manually sitting there in the middle of the code and applying the "Format > Cells" and "Number" with "2" decimal places. How can this be done? Here is some code.

strAggregateGroupFileName1 = "qryAggregationMatching"
strAggregateGroupSQLString1 = "SELECT tblGroupGLCodeMapping.GL\_CODE, " & \_
    "tblGroupGLCodeMapping.GL\_CATEGORY, tblInputGroupFile.GL\_AMOUNT " & \_
    "FROM tblGroupGLCodeMapping INNER JOIN tblInputGroupFile " & \_
    "ON tblGroupGLCodeMapping.GL\_CODE = tblInputGroupFile.GL\_CODE"
Set qdfLinkedTables = dbsBalanceSheet.CreateQueryDef(strAggregateGroupFileName1, \_
    strAggregateGroupSQLString1)

The code should probably go around: tblInputGroupFile.GL_AMOUNT

When exporting a data file from MS Access into a CSV file, the dollar amount loses the second decimal place. When clicking the mouse on the cell with the data, it shows .72 in the top address bar, but the cell itself only shows .7. What is the reason, and how do I correct it so that it shows the correct amount. I suspect that there may need to be some formatting before it is exported, but I do not know how to format it correctly. I believe it should be formatted as "Format Cells" and "Number" with "2" decimal places, but how do I do this programmatically? :confused::confused::confused:

Could somebody please point me to a tutorial on how to create a Help system similar to Microsoft application? I know it could be done with .HLP or .CHM type files, or even in HTML. What is the preferred approach and how is it done in the the C# part of Visual Studio?

I am working on an application that requires the Help forms that are commonly seen in a windows application, but I do not have any idea how to go about it. I believe that there is some module somewhere in Visual Studio .NET to do it. There are three tab strips composed of: Contents, Index and Find. The Contents tab strip has expandable and collapsible menu items. The Index tab strip provides the ability to search through all of the Help items in the Contents tab strip. I tried to find an article on the subject, but I could not find anything on it. Could someone kindly point me toward the correct resource or article on how to get started?

I am looping through a recordset, and I am both creating and destroying a QueryDef with what I believe to be a pass-through query. The records get processed correctly most of the time but then I get this abberant Error #3218 which states: "DAO.QueryDefs. Could not update. Currently locked." What does this mean and how do I prevent it from occurring? The application works correctly most of the time, but this error occurs intermittently, thus, it is the hardest cause to identify. Why is it locked up, and what is locked up?