Solved

Using "*" in numeric combo box in MS Access

Posted on 2014-10-06
25
296 Views
Last Modified: 2014-10-09
I am using a form in Access to run a query.  The form uses combo box values in the query criteria to filter the values I am pulling back.  When the form loads, I use "*' in the numeric combo box and do not get an error.  If i change the value in the combo box and then try to put the "*" back in it tells me the value is invalid since it is not numeric.  The same happens when I try to put the value in there with VBA.  I get an error.  What value do I use or how do I change how the criteria is written to get all values to pull back?  I am using this now:

Like [Forms]![frm_Campus_offer_Main]![CboUniqueID]
0
Comment
Question by:marku24
  • 10
  • 6
  • 4
  • +2
25 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40363979
You can't use star with a number directly. Are you trying to get *78* to include IDs such as 12789 and 2478?
0
 

Author Comment

by:marku24
ID: 40363995
I just want something to use in the combo box to give me all numbers in the table.  Similar to using * for text.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40364000
In which case, the option could be to leave it blank, and then use an IS NULL as a criteria.
0
 

Author Comment

by:marku24
ID: 40364072
sorry - how do i use Is Null in the criteria if I want it to use the value the user selects in the drop down to filter my criteria?  I can't type IS Null in the drop down since it is numeric.  At times a user may want to select a spefic record (i.e. 2258) or they may want to select all the values.  I just am unsure how to get all the values to pull back will still allowing them to use the drop down for a single value.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40364119
These type of comboboxes can be a bit of fun to create.
The MUST be unbound -- after all you are displaying the data for the user to select a value to filter, and not to actually change anything.
Next, you muck with the SQL underlying the comboboxes RowSource.
Right now, it is Select SomeNumber from SomeTable Order By SomeOrder
We're going to change that to
Select "ALL" as display, 0 as sortorder order by sortorder
Union
Select SomeNumber as display, 1 as sortorder from SomeTable Order By sortorder, SomeOrder


Now, ALL will always display as the first entry.
You combobox must have some AfterUpdate code.
Me.Filter = "SomeField = " & TheCombo.Value

Now you're going to have

If me.TheCombo.Value = "ALL" them
me.filter = ""
me.filteron = false
else
Me.Filter = "SomeField = " & TheCombo.Value
me.filteron = true
end if

Open in new window

Note that if your code is presently using the value of the combobox as a parameter in the query, and your AfterUpdate code is simply requerying, this requires some reworking -- but it will run MUCH better not having to requery all the time.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40364336
How do you use a null value? that's what you have if the results of the drop down is blank,
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40364356
" I just am unsure how to get all the values to pull back will still allowing them to use the drop down for a single value"

You build your form on a query that has all it's possible values.  You then use the combobox's  AfterUpdate event to craft a valid "WHERE" statement for the query without the word "Where".  This becomes your Filter property.  And then you turn the FilterOn property to True.

No Nulls need apply.
I think you're problem is that you've built the form on a parameter query where the combobox value is the parameter.  That works -- but not robustly as you have seen.  Remove the parameter and build filters instead.  Similar functionality --you are applying a WHERE to a SQL data source -- but better execution.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40364423
If the Combo Box is bound to an integer field, then "*" or "All" causes a type error.
In this case, you may use a command button to Display All.
0
 

Author Comment

by:marku24
ID: 40364514
What is the code for a command button to display all?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40364533
That depends on how your form was created.
If is DOES NOT depend on a parameter query then

Public Sub Killfilter()
me.filter = ""
me.filteron = false
end sub

Will do the trick
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40364810
"What is the code for a command button to display all? "

Private Sub cmdDisplayAll_Click ()
    Me.FilterOn = False
END SUb
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40364926
@hnasr
The evil bastard that invented FilterOnLoad and defaulted it to TRUE in A2007+ should be shot.
Hence, why it's always go from to set Me.Filter = ""
You never know when the filter might get auto-magically re-applied

:)
Nick67
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40364963
Using filters is a Jet/ACE ONLY technique.  If you ever think you might want to upsize, do not use filters.  Use criteria in queries so that the server does the heavy lifting.

To get the query to return all rows when the combo is empty -

Where SomeField = Forms!yourForm!yourCombo OR Forms!yourForm!yourCombo Is Null

LIKE is a string operation and will not work correctly in all cases if you try to apply it to numeric values or dates (which are numeric as they are stored).
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40364989
Here's a sample of how it should work.
A bound form to the full recordset, with the combo AfterUpdate applying a filter.

Filters work perfectly fine in an Access front-end /  SQL server backend.  And if you really DO want and need all the records, the network traffic is going to take the same performance hit whether you use a saved query with a parameter initially and want everything later, or you ask for everything upfront.

The fun comes later.  If the situation permits, you pass in an opening argument to your form that sets the filter initially.  The form can then open more quickly BUT in the background, all the data that you are eventually going to ask from the server keeps coming.  You thereby avoid loading lag, which can be quite nice.  But one way or another, if you need ALL the data, then the server's got to marshal it, the network has to carry it, and Access has to receive and display it.

Jet/ACE is pretty smart these days when you pull out a SQL sniffer to see what's going on behind the scenes.  The days of Access mindlessly asking for reams of data are pretty much behind us.  For the select union query on the combo box, these days I get the server to do that as a SQL View and then link to it as a table.  The server then optimizes the query, and has it ready, and gets served chunks if and when the dropdown is clicked.  But that's way beyond what you asked.

You wanted a nice filter combobox.  Order's up!
FilterCombo.mdb
0
 

Author Closing Comment

by:marku24
ID: 40366234
Thank you everyone. I was using criteria in a query as opposed to filters.  When i used Null and removed LIKE it seemed to work.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40366684
Nick,
I didn't say filters would not work.  They work just fine.  The problem is that you completely loose the benefit of having a server based BE.  If you are working with tables of only a few thousand rows and you have a high speed network, it doesn't much matter what you do.  However, I work with tables that can have millions of rows and it does matter.

Access forms bound directly to tables or to queries without criteria are like vampires and DBA's hate them.  They open as soon as Access has sufficient data to populate the form.  Then they sit there in the background sucking down data from the server until it is all local.  That is a huge waste of resources since in most cases, the user only wants to see a specific record or perhaps a small set of record.  This is probably the thing DBA's hate the most about Access.  Applications that waste network resources like this are not good citizens because they impact everyone.  

Access attempts to make EVERY query a pass-through query so unless you have done something that gets in the way, Access will send the query to the server and the server will send back only the requested row(s).  If you want to change the criteria, Access sends a new query and retrieves that set of data.  

So, although in most cases there is no need to be concerned about every wasted record read or computer cycle, it benefits you overall to understand what operations are costly and to take measures to make them more efficient.  It takes no more code to use criteria than filters and the advantage is the server does the processing and since only selected records are returned, network load is reduced.  Even if the BE is Jet/ACE, you can still use good client/server techniques and that will make conversion to SQL Server, if it becomes necessary, a trivial process.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40367300
@PatHartman
"Access attempts to make EVERY query a pass-through query so unless you have done something that gets in the way, Access will send the query to the server and the server will send back only the requested row(s).  If you want to change the criteria, Access sends a new query and retrieves that set of data."

The days of Access mindlessly calling for the million rows of a continuous form from SQL Server are behind us.  Access is not nearly so dumb anymore.  It'll load about the first 1000 rows or so, and then wait for user interaction to see if more need to be called for.  It has gotten very smart.  If the end user really does need all the data, then they need it and it makes no difference.

On the other hand, as a developer, you have to know and to pick your pain-point.  My user data-entry forms are ALWAYS very combobox heavy, and the comboboxes conceivably have 10's of thousands of entries in them.   These are NOT the type of object that you blithely requery -- which is what becomes needful if you muck with the underlying query to the form.  Instead, I set the combobox to NOT load their rowsource until they actually get the focus, and I pass in an OpenArgs to apply an initial filter.  And using a SQL sniffer, you don't see boatloads of data being requested that way.
YMMV.
For other forms, I have sprocs that form the recordsource -- right up until someone needs to edit.  The edit command button switches the form to a full-blooded query recordsource.  Yes, you could go with unbound forms and do everything through code and ADO -- but then you lose the whole RAD benefit of Access.  You may as well just go to VB.Net if you're going to go that far.

"Even if the BE is Jet/ACE, you can still use good client/server techniques and that will make conversion to SQL Server, if it becomes necessary, a trivial process. "
It's never a trivial process. SSMA for Access certainly takes most of the pain out of it, but there is still a big learning curve to go.  There's all those queries that wind up calling for a table scan.  Still, once you learn about sprocs and indexed views, and leave loop thinking behind and begin to concentrate on set thinking, it is always worth it.

In fact, I'd go so far as to say that you should go with an Access frontend  SQL Server Express Edition backend right off the nose.  It saves you a LOT of grief later.
But that's me.
YMMV

Nick67
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40368607
Nick,
If you are filtering the data, ALL the data needs to be downloaded to apply the filter since the filter is applied locally.

If you are talking about an .adp, then everything works differently and I would expect Access to be smart enough to send the filter as a query to the server so that only the requested rows are returned.  But .adp's have been deprecated and can no longer be used as of A2013.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40368669
@PatHartman
.mdb's.
Running against SQL Server 2008 R2 Express Edition.
The only way to truly see what is happening is to use a SQL sniffer.
Jet/ACE may surprise you in how it requests data -- both in situation where you THOUGHT Access should be making a simple request and winds up pulling all the tables in a query for local processing, and in situations where you thought Access would request reams of data and yet only requests some of it depending on what the user actually does with the GUI.
It truly is a case of 'your mileage may vary'
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40368750
I have used a SQL sniffer program and the traffic between Access and the server is mind blowing.  But, nevertheless, if you understand the process, you can control the result.  Obviously the server doesn't know anything about VBA/Access functions or your user defined functions so you must be careful where you use them.  In the Select clause, Access applies the query on the result set so there is no problem.  Any other clause and it could cause Access to return the entire contents of every table in the query.  If you join a local table to a server-side table, Access must request the entire server-side table and the list goes on.  Access sends the query as it reconstructs it and then fetches rows in a loop as you need them.  Access definitely does more work on "Access" queries than for those specifically coded as "pass through" but it is setup work to organize the process of retrieval.  It doesn't change the actual rows selected unless Access couldn't "pass-through" the query.  So Select aa, bb, cc from yourtable where id = 123 will return exactly the same rows regardless of whether it is sent via ODBC or as a pass-through.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40368828
Indeed,
And besides the control-freak part of it, DBA's don't like Access because there are so many thoughtless ways to get Access to request reams of data without meaning to.
" In the Select clause, Access applies the query on the result set so there is no problem."
If your join is on a memo field, sometimes a text field and sometimes even an unindexed numeric field,  you can still be in the weeds.
"...and the list goes on."  Indubitably.

" Access sends the query as it reconstructs it and then fetches rows in a loop as you need them."
And that's where we have a small difference of experience.  If I construct a bound continuous form that is bound to a million row table and fire it up unfiltered, I do not find that Access mindlessly calls for all one million rows.  It reacts to what the user does with the form and pulls the data as it is required.  That's my experience in any event.  As a result, I don't share your belief that one should use a parameter query to back a form, change the parameter and then requery to change the displayed records of a form.  Requerying a form can be a highly intensive operation if many comboboxes and subforms are present.  It should be avoided when possible.  Using filters allows for such avoidance.  Moreover, if you pass it a filter via OpenArgs, you can functionally get the best of both worlds -- a form bound to a complete recordsource that opens with a minimum amount of data that will not need to be requeried.
That's my experience, at any rate.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40369357
 I do not find that Access mindlessly calls for all one million rows. 

Open in new window

But what happens when you apply a filter?
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40371079
That depends on a myriad of possibilities:
Has the full recordset already been download?
If Access does a recordcount of the new filtered set, does it match that count on the server--then nothing else is needed.
Is the filter on a memo field?
A text field?
An unindexed field?
dbSeeChanges is in effect, so have any records changed since the initial call?
Has the user changed records since the form loaded?

Lots of possibilities.

My forms are very control-dense.  Requerying them is a last, expensive resort.  Filters permit that.
YMMV

Nick67
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40371262
This discussion is going nowhere so let's end it before we get asked to.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40371384
OK :)
Express Profiler creates quite a blizzard!
In this case, I created a form bound to a 660K row table.
Line 15 is where I Gave it the filter command to filter by JobID = 48610.
Funny thing is, where the sheet claims it took 23K ms to happen, it filters pretty much instantaneously.

But we'll let it go.
we can hash it around here Admin discussion if you are so inclined
Trace.xls
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now