Replies

I have a computer running Access 2003. I want to install Access 2010, but I also want to keep Access 2003 on that machine. Can it be done? If so, how? (In the past I have installed a new Office application and found, too late, that it destroyed my older one. I can’t afford to have that happen this time.)

I no longer install more than one version of Office on a computer. Although some say it can be done successfully, it’s dicey IMO. Before I started using virtual machines, I would see little incompatibilities when I co-installed versions of Office. Such things as Help not working or certain DLLs being incompatible (DLL hell).

I use virtual machines for the past 4+ years now. The setup I’ve decided upon is to have the most current version of Office+Windows on my base hardware. Then for each version of Access/Office I have to support among my clients, I set up a virtual machine running Office and the version of Windows I want running with that Office. Currently my three virtual machines are Office 2003/Win 7 Pro, Office 2007/Win 7 Pro, and Office 2010/Win 7 Pro. My base machine is Office 2013/Win 8.1 Pro.

The separation of the various Office suites is great! If two versions of Access need to get at the same file, I keep it in a shared folder on the base computer, with each virtual machine pointing at that folder too.

I use VMware Workstation 10, but I think Hyper-V in Windows 8.1 Pro may work as well, and I think it’s free; I have not explored it.

The only other solution I can think of at this point is to dump the entire contents of the report’s RecordSource into a temporary table. Open a properly-sorted Recordset against the table. Run code to loop through the table, deleting all the but five appropriate rows for each relevant group. Then open the report against the remainders in the table. All of this can be accomplished in the Report_Open event.

There are probably SQL gurus out there that can craft a SQL statement that will give you the proper set of records, but that is beyond my imagination at the moment.

Good luck to you.

Hi AccessNovice,

Do not delete the temporary table at the end of the session. Reason? Any indexes you’ve created go away along with the table. You will have to recreate the indexes each time you recreate the table. I would create a permanent “temporary table” that has indexes defined. Then, each time you need to use the table, empty it first (DELETE * FROM [MyTempTable]), and refill the table before processing. You can refill it with an append query rather than a make-table query.

So, in the end, you run two queries to fill your indexed table, the first one deletes everything, the second one appends the records. You can automate this with a macro that runs the two queries in sequence. You could even include one or more crosstab queries in the macro. That way it’s only a single click to load the table and run one or more crosstabs.

Good luck to you.

I might be able to help with a bit of VBA. If you are printing or doing a PrintPreview, here are the steps I see:

Define a module-level variable in the report. Put this line at the top of the report’s module:

Private mlngLines as Long

Then in the Format event of the relevant Group, put this line:

mlngLines = 0

This resets the number of lines printed to 0 each time a new group is created.

Finally, put this code in the detail section’s Format event:

mlngLines = mlngLines + 1
Cancel = mlngLines > 5

These lines of code will cancel the formatting of the detail section if five lines have already been formatted. Although the report will process all of the detail rows, only the first five will print on the report.

So, remember to open the report in Normal (printing) or PrintPreview and this should help you. This method becomes more problematic if you are opening the report in ReportView. Events do not fire the same in ReportView as they do in Normal and PrintPreview.

Jim

Make sure you have defined 11 columns on the combo box property sheet. If you have defined fewer than 11, Access and the combo box will not see all of the query columns and you cannot address them. In your example, not being able to address Column(9) suggests that the number of defined columns in the combo box is less than 10.

I’m guessing the actual limit is 255, but no one would ever go there, would they? The most I’ve ever used is in the neighborhood of 10.

Thanks. I have a similar problem now with the following:

=DCount(“[IncomingCalls]”,”tblCallHistory”,”([tblCallHistory]![CallDate] Between [dates1] and [dates2]) AND [SalesID] = ” & [Reports]![rptWeeklyActivityReport]![SalesID].[Value])

I basically want it to add the value of all reords in the field IncomingCalls between the two dates specified and for that particular person based upon their salesid. However it is not giving me the information back I expect, it is only counting the number of records.

Forgive me for pointing it out, but in your example you have asked it to Count, not Add. Use DSum(). I hope this solves your problem. 🙂

Having created a tabular report from a Table, using the Report Wizard, is there a way of formatting it so it looks like a print from the table with gridlines and shading on alternate detail lines?

I’m not sure what you are up to, but try these things:

—————–
Make sure all text boxes fit right next to each other in the detail section; no empty space anywhere across the width.

Make sure you have dark/black borders on all text boxes.

Make sure the height of the detail section is exactly the height of the text boxes, which all must be the same height and aligned with each other “to the top”

Make the text boxes fill as “transparent”

Turn on alternate row color in the detail section of the report.
——————-

That should about do it. It’s at least a good start to making the output look like a datasheet view.

You might try putting a subreport into the detail section, with the subreport being a datasheet view of a query or table. I’d have to fiddle with that to see if the idea works, though.

Hi,

I have the following formula on a report:

=DCount(“*”,”tblValuations”,”[tblValuations]![CreateValuation]>=[reportdate] and =[reportdate] and <=[reportdate2]"[/B] section?

Here is a clean solution for your DCount:

=DCount("*","tblValuations","([CreateValuation] Between [reportdate] and [reportdate2]) AND [SalesID] = " & [Reports]![tblSalesman2]![SalesID].Value)

I enclose the date part of the criteria within parenthesis more for clarity than anything else; it's a habit. Be sure that your [CreateValuation] and both reportdates are all just date values with no time portion attached, or you might not get exactly the count you expect. You can assure that with the DateValue() function:

=DCount("*","tblValuations","(DateValue([CreateValuation]) Between DateValue([reportdate]) and DateValue([reportdate2])) AND [SalesID] = " & [Reports]![tblSalesman2]![SalesID].Value)

Of course, this level of precision is not necessary if you know for certain that you never have time-of-day in your dates.

Cheers!

First off, you do not need to call a function to establish sTopic as a public string variable. Just put this line at the top of any Access VBA module:

Public sTopic as String

That takes care of the public nature of the variable. I don’t know what your function does to establish the public nature of the string, but that approach is foreign to me. I tend to have a module in every Access database named basGlobals which collects into one place all of my global variables (very few!) and global contstants (lots and lots). I don’t like to spread around my global/public declarations.

Next, put a break point on the assignment line sTopic = Me.UniqueTopics.Column(0) in your Access 2007 code. Check the value of Me.UniqueTopics.Column(0) at that very instant. What do you see? If it looks good, step one line further and then check the value of sTopic. What do you see?

You might also try using Me.UniqueTopics.Value if you indeed have column(0) as the data column and you are not using a multi-select list box.

That’s all I can offer without any more information. Good luck!

When using a linked table (Access to Access) I am not able to Cascade update when I establish the relationship (on the linked table side of the database). Is there a way to work around this issue?

If you are trying to establish a relation between two tables in the back end database, yes, that should always be done in the back end file. I can think of no reason to do it in the frontend. If you are trying to establish a relation between a table in the back end and a table in the front end, then your only option is to create the simple relation. Access/Jet will not enforce referential integrity, cascade update, or cascade delete on this type of relation. Furthermore, you will not see the 1-1 and 1-many symbols on the relationship diagram; just a solid line between the tables.

I’ve seen people create relations in a front end database between queries, or a query and a table. That’s useful, I suppose, when you know what a not-so-obvious relationship is and you want Access to recognize the relationship whenever you bring the two objects together in another query; Access draws the relationship for you automatically because you’ve already told Access what it is. Again, there is no referential integrity, cascade update, and cascade delete option when creating the relation.

Happy computing!

Hi,

Having a complete memory block.

I have a report where I have based it upon the salesman we have.

Each salesman has his unique id of ‘salesid’ in the table and other tables I want to query.

In the report, I would like to use a dcount function to count how many appoinments each salesman has booked.

I have a blank field in the report where I would like to display the number of appointments that salesman has booked. The table I need to query is called ‘tblValuations’, where I need to query where the salesid equals the line on the report and also the date each record was created (createvaluation) is today.

I have tried to use dcount(“*”,”tblValuations”,SalesID= & me.salesid & ” and tblValuations!CreateValuation=” & date()-1)

However this does not work.

Any ideas?

Or how about this: DCount(“*”,”tblValuations”,”[SalesID]=” & Me.SalesID.Value & ” AND DateValue([CreateValuation])=Date()”)

I’m assuming SalesID is a numeric value. I use DateValue() to strip away any time component so your are assured of a match with the Date() function. Note that I put the Date() function inside the criteria rather than appending its results to it. If you want to append the results in a fashion similar to your example, use:

DCount(“*”,”tblValuations”,”[SalesID]=” & Me.SalesID.Value & ” AND DateValue([CreateValuation])=” & Format$(Date(),”#mm/dd/yyyy#))

Of course, there are probably 247 other ways to accomplish your goal. Enjoy!

Becareful with DLast() and DFirst().

You want the last record based on what kind of a sort? Sort the domain one way and “last” might be different than when you sort the domain another way. I presume that DLast() will return the last record based upon the order of the primary key. But are you sure?

I don’t use First or Last in my queries unless I perform a sort myself, or if I don’t really care what I get, as long as I get something. Using Domain Aggregate functions, you cannot specify a sort against a table. If your domain is a query, you can, of course, specify a sort and be comfortable with what these two functions return to you.

Thanks Wendell. I had tried that syntax. =Sum([Reports]![rptAmazonOrders]![srptAmazonOrderDetails].[Report]![Quantity]) I don’t get an error but I get nothing. If I drop the “Sum” I get a result of the first record which happens to be 1. The expression also returns 1 if I drop the name of the main report. The expression builder does not add the name of the main report but like the article you referred me to, the help file adds the main report name so I had tried that.

You need to put the control with the SUM in the subform, possibly hidden (or visible) in a section footer. Then, in the main form, you just refer to the control as Wendell mentioned. You cannot SUM the values in the subform from the main form. All you can do is reference the SUM that the subform performs.

You are undoubtedly opening your report in a screen-oriented Report View or Layout View, rather than the long-time standard Print Preview. Format events fire only when Access is preparing send output to a printer. If Report View or Layout View is important to you, move or copy your code to corresponding Paint events. Paint events fire as you page through a report on the screen, whether in Report View or Layout View.

Is this what you are after

SELECT DISTINCT ContactID FROM yourtable WHERE Contribution > 999

Hi Browni,

Your SQL statement will return a list of contacts that have contributed over $999 at least once. It does not return a list of contacts that always contribute over $999. Forgive me if I misread the OP’s original need.