Search results

LIKE filters with no wildcards are like asking for: [TableName] = 'tblPD' AND [FieldName] = 'PD' LIKE is a whole lot more inefficient than plain comparison, so the code should be using comparison. And I see nothing that would upset T-SQL or MySQL or, for that matter, PostgreSQL.

I already get a weekly update of popular posts by setting an option.

I’ve been recommending unmatched queries for this sort of problem since at least 2003 and probably earlier. Microsoft never added the unmatched optimization to NOT IN, and even with an index, NOT IN forces a full table scan. If this set of quantitative results doesn’t convince you to NEVER use...

Just ran an interesting test: Public Sub TestMissing(Optional A As String, Optional B As Integer, Optional C As Variant, Optional D As Object) Debug.Print "A " & IsMissing(A) Debug.Print "B " & IsMissing(B) Debug.Print "C " & IsMissing(C) Debug.Print "D " & IsMissing(D) End Sub...

ChrisO- You are correct! I originally wrote that text for Access 97 or maybe 2000. You must declare as Variant if you want to use IsMissing, but that's not required. However, once you use Optional, all successive arguments must also be Optional, and you cannot use ParamArray as the last...

All- Attached to this message is an excerpt from Chapter 22 of Microsoft Office Access 2003 Inside Out that may help you understand Class Modules better. These are bits and pieces related to Class Module from a reference chapter that may seem a bit disjointed. First, there's a description of...

You may not realize it, but you use Class modules all the time! The code modules associated with forms and reports are Class modules. One nice thing you can do with Class modules is declare some of the Module variables and some of the Procedures as Public. This then "exposes" these items as...

If you allow both Deposit and Discount to be Null when they don't exist, then it's easy to accomplish what you want. Simply make the two text boxes (and attached labels) 0" in height and placed so they don't overlap anything else. Set Can Grow to Yes, and they will expand to print something...

Ruzz- So, you want the other filters to reflect only values valid for "Amy Hardie" once Investigator is selected? What do you do if there is an existing filter, for example, for Section that doesn't match any Amy Hardie records? Clear it? I think you need to lay it all out for yourself more...

Maybe it would help if you wrote out what you expect to happen when the user updates each combo box.

Ruzz- After looking at your database, I still can't see what you're trying to do. You seem to be resetting strFilter whenever the Status changes. What else do you want to happen, and what should the code do about filters already set for things like Investigator and Section? Why aren't you...

Never mind - I figured out it's frmMain.

Ruzz- I don't need a login - in which form do you have this code?

Ruzz- Might be easier to figure out if you can post a sample of the database.

It could be something messed up in what you've built in strFilter. Try a Debug.Print strFilter just before this statement, then look at the Immediate Window (CTRL+G). As for setting up a filter, I NEVER reset the Row Source of a control containing a filter value as I'm building the filter. I...

Whoa. I just looked at your code more closely. Why in the world are you changing the Row Source of parameter combo boxes when you're trying to assemble a filter? For example, ' Fix up all the other row sources Me.No.RowSource = "SELECT [tblInvestigations].[No] " & _ "FROM tblInvestigations "...

Russ- Are you talking about this line? If Not IsNull(Me.YearRange) Then ' If the current value of the No control is no longer in the filtered set If IsNull(DLookup("Date Logged", "tblInvestigations", _ strFilter & _ " And Year([Date Logged] = '" & Me.YearRange & "')")) Then What's in...

I'm trying to remember whether Access 2000 included Pivot Charts. (Isn't it time to upgrade?) They're much easier to work with than the Chart Object (Chart Wizard).

It was a fun thing to put together. Glad to hear you could tweak my code to get the filters to work the way you wanted. I assume you've successfully added "Outstanding." Sorry, but Conditional Formatting has always been limited to no more than three criteria. Are you using Pivot Charts...

Well, what you want to do cannot be done with query parameters. It's not uncommon to want to use parameters like this to check for a value in a control and apply a filter, but ignore the filter if the control is empty (Null). Typically, your criteria will look something like: (([FieldInTable]...