Replies

Charlotte’s solution will group months of different years together (e.g., you’ll get a composite average for January 2000 and January 2001 together, if your dates span more than the last 12 months). If you want to differentiate months from different years, then specify “mmmm yyyy” in the format functions.

Also, if you want the months to list in chronological order, add DateSerial(Year([ShipDate]),Month([ShipDate]),1) to the GROUP BY and to the ORDER BY clauses.

So, your query might look something like:

SELECT Format([ShipDate],”mmmm yyyy”) AS [Month], Avg([ShipDate]-[OrderDate]) AS Days
FROM Orders
WHERE ([ShipDate] Is Not Null)
GROUP BY DateSerial(Year([ShipDate]),Month([ShipDate]),1), Format([ShipDate],”mmmm yyyy”)
ORDER BY DateSerial(Year([ShipDate]),Month([ShipDate]),1);

Finally, if you want average months rather than average days you can get close by substituting “Avg([ShipDate]-[OrderDate]) AS Days” with “Avg([ShipDate]-[OrderDate])/(365.25/12) AS Months” in the above query.

Tom

Drk ,

If I understand your problem, it sounds like you want to average all the durations that end in a given month (or start in a given month?). If so, then I’d include a field in your query something like dteMonth:dateserial(year(dteEnd),Month(dteEnd),1). Then group on dteMonth and avg(dteEnd-dteStart). The average, of course, will be the average number of days between the start and end. If you want the average number of months, then divide the result by 365.25/12 (an approximation!). If you want to display the months spelled out then use format(dteMonth,”mmmm”)

Tom

There may be more elegant solutions than this, but here’s one that should work: Add “A” = [Enter Section] to your WHERE clause (with OR logic): e.g., something like ([Section] = [Enter Section]) OR (“A” = [Enter Section]). “A” needs to be unique (“All” would be another choice) — it can’t be one of your legitimate Section IDs. You can do this in Design mode or SQL mode. In design mode, specify “A” (with the quotes) as the field, uncheck the Show box, and specify [Enter Section] (without quotes) as a criteria (on its own row to indicate OR logic).

When you run the query and specify a legitimate section ID for the [Enter Section] parameter, the first subclause will pick up the lots belonging to that section. When you specify ‘A’ for the [Enter Section] parameter, the second subclause will pick up all the sections (that is, all the lots).

Tom

Larry,

Rather than one-time opening the folder of interest (as you describe), add it “permanently” to your Outlook folder list. To do this (assuming you’re using Microsoft Exchange Server), first select Tools | Services. Then select “Microsoft Exchange Server” and click the Properties button. Then click the Advanced Tab, then the Add… button and type in the mailbox name (e.g., “John Doe”). Click OK a few times to get out of all this. Now, you should have this mailbox listed in your folder list, in addition to your own mailbox.

Once this is done (it becomes part of your profile so you won’t have to do it again the next time you open Outlook), THEN you can use the VBA code I provided above (which, now that I better understand your problem, is probably similar to what you were already using to access your own mailbox):

Set objFolder = objNameSpace.Folders(“Mailbox – John Doe”).Folders(“Inbox”)

or something similar.

Does this get you closer?

Tom

Right. I don’t think table definitions allow calculated fields. As suggested above, do the calculation in a query based on the table or as the control source for a form or report field.

Besides, in general, it’s good practice not to bloat your tables too much with data that’s directly calculatable from other fields in the record.

Tom

Try something like:

Public Sub ListMessageSubjects()

Dim appOutlook As Outlook.Application
Dim objNameSpace As Object
Dim objFolder As MAPIFolder
Dim objMailItem As Object

Set appOutlook = CreateObject(“Outlook.Application”)
Set objNameSpace = appOutlook.GetNamespace(“MAPI”)

Set objFolder = objNameSpace.Folders(“Your Folder Name Here”)

For Each objMailItem in objFolder.Items

Debug.Print objMailItem.Subject

Next objMailItem

appOutlook.Quit

End Sub

If you want to “drill down” into nested folders, then append .Folders(“Next level down folder name”); e.g.:

Set objFolder = objNameSpace.Folders(“Your Folder Name Here”).Folders(“Next level down folder name”)

Add more to drill further.

Hope this helps a little.

Tom

Check out the following threads:
Thread 1
Thread 2

My impression from these threads and some first-hand experience is that you will have problems with archiving if you use an incremental autonumber, but your chances of problems if you use random autonumbers will be somewhat worse than your chances of winning the Powerball lottery.

An alternative that Charlotte suggested is to use a “master” table with your autonumber field (random or incremental) and an “Archived?” Yes/No field (the latter for convenience only) and not much else. Then relate this table to your “active” and “archive” tables through the autonumbered master field I.e., when you create a new record, create it in the master table (generating a new autonumber). Then create the new record in the active table and copy this autonumber value into the link field (which itself is not an autonumber field).

Tom

I won’t guarantee that this is the BEST way to do what you want (and I’m sure we’ll both hear one way or the other), but I’d suggest using a union query to combine all six property tables. This will work best if all six tables have the same structure. If they don’t, then perhaps you can create a query for each that pulls out the information you need for the reports and puts it in a consistent format. Then run the union query on these six queries (or combination of queries and tables) that all have a consistent structure.

It would probably be easiest just to “start from scratch” each time you build the consolidated table. That is, import (or link to or whatever) the latest version of the six property tables and just report off of the union query result. Alternatively, you could perform a make-table query using the union query as input to create a static consolidated table.

Hope this helps a little.

Tom

Arage,

Well, your query could contain WHERE sub-clause something like ((EventNumber Between 3 And 30) And (PromotionType = “be”) And (ManagerName = “ken williams”)). Add one of these for manager, separated by “Or”. This is a little cumbersome, but probably not much worse than the string of If-Thens in VBA.

Perhaps a more elegant way to do this would be to create a link table that defines which events/promotions each manager is to “see” (e.g., each record has ManagerID, EventID, and PromotionID for each “allowed” combination). Use this link table to set up one-to-many relationships (the link table is on the many side) with the manager table, event table, and promotion table. A query built on this structure will then generate records only for those manger-event-promotion combinations that you have established in the link table.

Tom

Arage,

I’d suggest using a query for your report’s Record Source. Let the query filter out the records you don’t want to see.

Tom

One last thing: You’re getting a “1” from the code example because MsgBox(“True”) evaluates to “1” (you clicked the “1”-st button (the only one – “OK”) to exit the box — MsgBox() returns which button you clicked, and that’s the value that the iif() function returns).

If you try:

?IIf(True, MsgBox(“True”, vbOKCancel), MsgBox(“False”, vbOKCancel))

and click on Cancel, you’ll get a “2” (the 2nd button was clicked).

Logical expressions should always evaluate to -1 (True), 0 (False), or Null. What value is actually returned from iif() depends on the 2nd and 3rd arguments, respectively, of course.

Tom

Try this command in the VBA Immediate window:

?IIf(True, MsgBox(“True”), MsgBox(“False”))

Guess what you get…

Tom

A couple thoughts (I suspect you’ll get a few more from others):
(1) I believe both the true and false portions of an iif() statement are evaluated, regardless of the result of the logic evaluation. Therefore, if the form isn’t open, you may still get the #NAME? error because Access still tries to resolve [Forms]![FormName]![FieldName]. You may have to resort to a VBA function call that uses the if…then…else structure. Can you include the information of interest (that field on your form) as part of the report’s record source (possibly using an intermediate query that picks up this value as the record source) and then avoid referencing the form altogether?
(2) A periodic repair and compact seems to do wonders in squashing flaky behavior.

HTH,

Tom

Try something like:

appExcel.Run “main.xls!Module1.ExcelMacroToRun”

Fairly self-explanatory I suspect: ‘main.xls’ is the workbook where the macro resides (it must already be open in the ‘appExcel’ instance of Excel); ‘Module1’ is the module containing the macro of interest; and ‘ExcelMacroToRun’ is the macro name. If there are arguments to be passed to the macro, they should follow the quote string, separated by commas:

appExcel.Run “main.xls!Module1.ExcelMacroToRun”, arg1, arg2, arg3, …

Analogously, you can “drive” Access from Excel macros. Pretty cool stuff. If you want to explore further, I’d suggest you wade around the Object Model using the Object Browser in the Visual Basic Editor. It’s fairly remarkable what can be done with a little (sometimes a LOT) of poking around and experimentation.

Tom

My apologies. Two things wrong with my original suggestion. It now is apparent to me that you already have Excel running (with the dummy file open) when you run this macro. If so then you want to reference this instance of Excel, rather than starting up a new one. So, replace

Set appExcel = CreateObject(“Excel.Application”)

with

Set appExcel = GetObject(, “Excel. Application”)

(Note the comma after the opening parenthesis.)

Also, with that troublesome Close statement, forget the drive and path portion of the file name; just use

appExcel.Workbooks(“Dummy.xls”).Close False

(my mistake). Try the full name, too (e.g., “dummy file.xls”; my memory may be wrong about the MS-DOS filename thing).

I hope this gets you back on the path to happiness and good fortune.

Tom