Rabu, 16 November 2016

Re: [MS_AccessPros] Re: Search anywhere in a field



Two separate fields sounds like a good idea.  But if you want to match any part, you'll need to stop using combo boxes.  Use unbound text boxes instead and use LIKE "*" & value & "*" in your searches.  Or, you could keep the combo boxes for when the use wants to select a specific number, but also supply a companion text box that's clearly marked to enter digits for a partial search.  The combo box gives the user a clue about what's valid for a given customer, etc., but the text box lets them enter a partial search.

Also consider using a way to let the user "clear" any of the combo boxes.  I typically do this with a Row Source using a Union query, like this:

SELECT NullLong As CustomerID, "<All Customers>" As CustomerName
FROM ztblNulls
SELEC CustomerID, CustomerName FROM MyTable

And ztblNulls is a table with one row that has one column for each data type you might want to use in this way and that contains a Null value in all the columns.  The dummy table is necessary because trying to simply using a Null literal value in the first SELECT surfaces an old bug in Access that ends up with junk in that column because the Null doesn't have a data type.

John Viescas, Author
Effective SQL
SQL Queries for Mere Mortals 
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
(Paris, France)

On Nov 16, 2016, at 2:31 PM, nigel@pegasusconsulting.co.uk [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Thanks John,

Batch number length varies depending on the product, and serial number may not be entered in some instances! The inclusion of the serial number in the batch number field has only started recently!

In both bases the maximum number of digits is 10.

I think having 2 separate fields (BatchNumber and SerialNumber) will be the first step. Then I'll need to be able to match any part of of the string with the user's entered text, in either or both of those fields. How would the code look if I did this? 


Posted by: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (6)

Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.



Tidak ada komentar:

Posting Komentar