Query input from open form (case-sensitive input issue)

[munroe47]

I am not a programmer, but I use many built-in Access functions/tools to automate my databases. I have queries that derive their input from text boxes in an open form. The user enters search criteria into those text boxes, and the query extracts and uses that input to isolate records matching the criteria. In the query, I use the following syntax in the criteria slot: Like[FORMS]![<Form Name>]![<Text Box Label>]. It works perfectly to isolate the records that fit the search criteria IF the case of the search criteria exactly matches the case of the field contents. I can't find a way to set it up so that the case of the input is irrelevant? Most of the time, I am familiar with the case format of my field contents, but sometimes I have to go back to the table to determine if the field contents were entered as uppercase, lowercase or a combination of both. Even worse, if any data was originally entered inconsistent with the normal format or with a capitalization error, those records are never found! Is there a way that the query can be made to "ignore" the case when matching search criteria to field contents? I really don't want to go back to the tables to check hundreds (in some cases thousands) of records for inconsistent data format!

[June7]

If you don't use the * wildcard with LIKE operator, might as well just use = sign.

LIKE [FORMS]![<Form Name>]![<Text Box Label>] & "*"

or

LIKE "*" & [FORMS]![<Form Name>]![Text Box Label>] & "*"

Upper or lower case is irrelevant in Access by default - "A" = "a". That can be changed but I never have.

[munroe47]

Thanks June7

I didn't mention before that I used "**" as the default input mask in all of the data entry text boxes in the input form. I enter my search criteria string (or a part of the string) between the asterisks. However, the correct case of that string entry has been necessary to return the correct records.

This database is for a stamp collection. The table being searched is essentially a catalog of all US stamps with each of their characteristics in a separate field. Each of these fields is of type Short Text. The unique catalog number of the stamp is the primary index, and since it never exceeds four digits, all numbers below 1000 have been entered with leading zeros for proper sorting order. The search feature I described is used to identify a stamp by entering some or all of it's characteristics into the appropriate text boxes in the input form. For example, if I enter "11" in the Perforation box, the query will return a list of all stamps in the database with an 11-gauge perforation. If I enter a color, a denomination, etc. into the other text boxes in that same input form, the returned list is further reduced until eventually the stamp is identified by process of elimination. The input mask of "**" makes it unnecessary to put something into each of the text boxes, when only a few characteristics are the important ones. I created this because there were stamps issued in the early 20th century that are virtually identical except for these minor differences, and they are scattered throughout several years of issue. It is difficult to systematically figure out which is which except by this database or by flipping back and forth through pages and pages of a stamp catalog, so I spent many hours inputting the catalog info into this database. I use it for much more than just identification, and have never regretted the time investment; my several levels of backup testify to this!

One of the criteria is "Design Type" which is always designated by Roman Numeral I thru VII. The last time I use this query, I didn't bother to capitalize my search input in this field, and I got an empty list; by capitalizing, I got the desired results.

You showed me two different versions of what I could have used in the criteria slot in the query design. I wonder if my use of "**" as an input mask renders the wildcard in your two suggestions unnecessary? You also said that case is irrelevant by default in Access, but since it seems to matter in my situation, I guess my question is narrowed down to what do you think I need to change either in my query or my input mask so that the non-capitalized input will return the same list as the capitalized input?

[NTC]

why are these symbols part of the syntax? <><

[June7]

NTC, I think OP was using them to indicate those were generic names, not the actual names - threw me off for a minute also.

I've never used * in input mask. I didn't think it was valid for input mask. It's not included in the reference list of input mask characters. http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

I don't know why your query is case sensitive, review http://support.microsoft.com/kb/209674

Could wrap everything in UCase() function.

VBA can force case sensitive http://msdn.microsoft.com/en-us/libr...(v=vs.60).aspx

I don't know any way to globally set the entire db to case sensitive.

[NTC]

case sensitive is a binary compare and is available in Access but not as a default nor a simple option; so it isn't clear how one has gotten themselves into apparently a case sensitive situation - it isn't easy to do... I'm also a little confused by the reference to: Text Box Label - - referencing the label? .... things are a bit odd here....

[munroe47]

NTC - Sorry for the confusion about the >< symbols. June 7 is exactly right; I thought I remembered (from my Pascal programming days) that enclosing a fieldname in <> was the proper notation to indicate that variable input was expected in that field. Never used in the code, only used in the documentation. Considering the confusion I created, apparently, the >< symbols are not universally recognized for that use, just one of my many flawed memories. as far as "text box label" is concerned, each text box in the form used for input has a unique name. That name is referenced in each field of the query to pull the input from the appropriate text box. So, in my attempt to explain my syntax in the query, I used <text box label> and <Form Name> as generic references. I thought that the specific name of my input form and the names I had given each text box in that form would create confusion.

June7 - I can't remember how I came to know I could use the ** in the input mask. Possibly I just assumed that it would function as the standard wildcard character, and apparently it has!

Thanks everyone for the help and suggestions. I can get along without fixing this case issue. I have been using this search query that I created for several years, and until now, I didn't even realize that case mattered. I know how to input the criteria because I input all of the original data in the database and was careful to use a consistent format. I just didn't bother to capitalize the Roman Numerals for this latest search and discovered that case did matter. If I do have any capitalization inconsistencies somewhere in my data, I guess those records will not turn up in a search, but I likely will discover those errors, one at a time, somewhere along the way. But then again, maybe I entered everything with perfection. Yeah, right!

It seems my problems always turn out to be mysteries! Story of my life???