ACESS 2010 Query Criteria

Your description is a bit confusing.

My understanding is that you have a table with the field [AlphaPharm02]

and you want to be able to filter the records in that table based on the value of this field.  In a query you would do this with a WHERE clause that looks like:

WHERE [AlphaPharm02] = "Bur-2"

or if you wanted all of the records where [AlphaPharm02] starts with "Bur-2" you would use:

WHERE [AlphaPharm02] LIKE "Bur-2*"

use of the asterisk in this syntax tells Access (Jet actually) to include all record which start with "Bur-2" but could include characters after that.

If you are trying to do this in a form, where you enter the search criteria in a control (generally in the form header or footer) and then filter the records displayed in the form, I would generally do this by filtering the form, using the AfterUpdate event of the control, something like:

Private Sub txt_Filter_AlphaPharm02_AfterUpdate

   if me.txt_Filter_AlphaPharm02 = "" Then
       me.Filter = ""
   elseif instr(me.txt_Filter_AlphaPharm02 , "*") Then
       me.filter = "([AlphaPharm02] LIKE '" & me.txt_Filter_AlphaPharm02 & "')"
   else
       me.Filter = "([AphpaPharm02] = '" & me.txt_Filter_AlphaPharm02 & "')"
   end if

   me.FilterOn = Len(me.Filter) > 0

End Sub

Select allOpen in new window

Note that the last pair of arguments for Switch above is not strictly necessary; without them Switch simply returns Null if the first two conditions are false.  Since any (in)equality operation in which at least one operand is Null always returns Null, the query would omit that record.

The problem is that you are using a wildcard search to look for a wildcard character.

If you want to search for a string that contains an asterisk, try this:

Where SomeField Like "*[*]*"

The first and last * are wildcards and the [*] is the explicit character you are searching for somewhere in the string.  You have to enclose the value you are searching for within square brackets if the value is itself a wildcard character.

Hi Experts,

I didn't see any notifications for any of these comments.

I will take a look at them and respond accordingly

Thanks,
Bob C.

Hi Dale,

I am doing this filtering in a Query.  When I am searching with asterisk I always want all records.  The issue I am having problems with is that my Key Fields are right padded with asterisks.  This was done to make it easier to see the number of characters following the actual data in the Key Fields when viewing them in a Table by me.  Unfortunately, as Pat Hartman pointed out this was a very poor choice.  At some point I will change them to something else during a new release.  e.g. Tilde '~' since it isn't a Meta Character.

Patrick / Pat,
Thanks for your suggestions.  I'll try them out and update this question accordingly.

Thanks,
Bob C.

Hi Pat / Patrick,

I don't think that Pats' solution will meet my needs as the criteria will always be:
- For all records meeting the criteria in which case the criteria will be a single asterisk or
- Records meeting a Specific Criteria.  e.g. BUR-2* or BUR-20.  i.e. I will never want to use BUR-2* to select both BUR-2* and BUR-20.

Therefore the Switch solution looks to be the one for me.  I have reviewed Patricks' solution including his linked page but due to my poor understanding of syntax I can't figure out how to code it for the above.  i.e. What would the code be for where these values would be contained in the field AlphaParm02 of a Table.:
- All Records.
- BUR-2* only
- BUR-20 only

Thanks,
Bob C.

To search for BUR-2*, use BUR-2[*]

You have to enclose the "wildcard" in square brackets.

If you are never searching for partial strings, you should not use LIKE at all.

Where (fldA = Forms!yourform!fldA OR Forms!yourform!FldA Is Null)
AND (fldB = Forms!yourform!fldB OR Forms!yourfour!FldB Is Null)
And ....

This allows you to have multiple optional parameters.

Like makes no sense unless you are actually doing partial value searches because it frequently prevents the query engine from optimizing the query and forces a full table scan.

Hi Pat,

Thanks for the update.  I have updated my 'Like' reference documentation for future reference.

Hi Patrick,
I have been able to figure out the syntax when Switch is used in a form I am just going to see if I can get it right in a Query.

Thanks,
Bob C.

Hi Patrick,

I'm having trouble with the Query Syntax.

The name of the field I am querying is PERIOD_EVENT_GRP_REG_KEY and contains various values all of which have the first 10 columns containing 2017BB2017**** with columns 11-16 containing various combinations of characters including numbers and special characters.

The field ALPHA_PARM_3 either contains the value in the PERIOD_EVENT_GRP_REG_KEY  or '*'.

The query criteria is:
Switch(Mid([ALPHA_PARM_3],1,1)<>"*",[ALPHA_PARM_3],Mid([ALPHA_PARM_3],1,1)="*","*")

If the value of ALPHA_PARM_3 is '*' no records are selected.  i.e. All Records.

If the value in   is '2017BB2017****BUR-2*' the selection occurs correctly.  i.e. 1 Record - exact match.

Why doesn't the All Records code work?

Thanks,
Bob C.

Hi Patrick,

Can you take a look at my last post and let me know why the 'All' isn't working?  I assume that it is a syntax error on my part but I can't figure it out.

Thanks,
Bob C.

Hi Experts,

I have now tried ALL of the suggestions above and cannot get it to work to select one record or all records.

As mentioned in my last post to Patrick a couple of days ago the Switch 'single  record syntax' works but the 'all records syntax' doesn't select anything.

Can anyone provide additional solutions to try?

This is the only outstanding issue holding up publishing this application so I would like to get it resolved ASAP.

Thanks,
Bob C.

Please post the code you used when you tried the *[*]* solution.

Hi Pat,

I am posting the code along with a Word Document about what I am trying to do etc.

I have tried your suggestion above without success.  It is included in the Database Parameter Table.

Thanks,
Bob C.
EE-Query---Parameter.docx
EE-Query---Parameter.accdb

Hi Pat,

Now that you have a better understanding of what I am trying to achieve and the method I was trying to use to accomplish it, I would certainly appreciate any solutions you may have to do it.

Obviously I would like to use the method I have supplied (with whatever tweaks are necessary) but with your wealth of experience you may well have other suggestions that I could try.

Thanks,
Bob C.

Hi Pat,

Thanks for your analysis / explanations and just great comments.  They are really appreciated.

With regard to mass changing the 'Filler Character' from '*' to '~', thanks for the confirmation that the tilde '~' is a good choice.  Unfortunately I have been using '*' for a long time and therefore it is all over my application.  Fortunately at this time there are only a handful of copies in use.  My method of providing new versions is to run VB Code to copy all of the data from the previous version to the latest version.  This will allow me to mass change the Filler Character throughout the application with the next release.

While awaiting your response I decided that I would replace many of the queries for reports with VBA Code thereby adopting another of your suggestions.  To facilitate this I have created a generic Report Data Table.  It contains a record of a concatenated field made up of User Id, Parent Object Id and Child Object Id. The rest of the record contains 99 Text Fields of 250 characters each.  I simply load the table with VBA Code, run a query to provide the data to a report and then delete the data based on the applicable Concatenated Key fields.  This is working exceptionally well.  One great side benefit is that a report that used to take at least 30 seconds on a local PC (took minutes with the BE DB on Drop Box) to run (very complicated) now runs in 3 seconds!

So in summary thanks again for all your work in addressing this question.
Bob C.

Thanks all again.
Bob C.

did you say BE database on DropBox?

Probably not an ideal situation, especially if you have multiple people trying to simultaneously access the BE.

Dale.

Hi Dale,

Yes I did say Back End DB on Drop Box.

I am well aware that allowing more than one user to access the BE at a time will result in loss of data integrity and a 'Conflicted Copy' being created.

My application checks to see if there is a .laccdb in existence before opening the BE.  If there is, an Error Message displays advising that the application is already I  use and then it won't allow the additional User access.  I have also provided an Option within the application to specify whether the BE is on in a sharing location like Drop Box or on a Server etc. in which case multiple Users are allowed.

I have tried to find a Sharing Site at a reasonable cost that works correctly with multiple Users but haven't had any success.  Can you suggest any?

Thanks,
Bob C.

you're welcome.

"Can you suggest any?"

None that I have personally tried.

Hi Dale,

I have checked out a number of sites but they all work as though the BE Files are files, not databases.  As a result when one accesses them you are working on a local copy not the actual data database in the cloud.  Hence the 'Conflicted Copy' message.

Thanks for getting back to me.
Bob C.