How to set a case sensitive criteria for a query

[LionelSpratt]

Hello everyone....

I've googled this problem, but I couldn't figure out non of the results that I got, since I'm very very new to Access and also don't know nothing of VBA

My problem is simple,
I have a table with a field called "Score". In this field, users are going to put either "m" or "M" (I dunno why they choose to work with m and M....but that's how it is)
Now I've created a search form, that would run a query based on what's in the form.
In the "Score", users will have to either type "m" or "M" to see how many entries contain either m or M

But when I put m....if gives me all entries.....both m and M

So in the query I need to create a criteria for it to distinguish m's from M's
I've read that access is case insensitive, but can anybody slowly and carefully explain this to me, without it getting too complicated how I can get my way around this problem ?

[ssanfu]

If you look at an ASCII chart (http://www.asciitable.com/), you will see that a uppercase m has a decimal value of 77 and a lower case m has a decimal value of 109.

The function to use is ASC()

Public Sub ascii_Test()
   MsgBox Asc("m")
   MsgBox Asc("M")

End Sub

Something like
IIF(ASC(FieldName)= 77,1,0) 'tests for M

IIF(ASC(FieldName)= 109,1,0) 'tests for m

[LionelSpratt]

Thx ssanfy

Now, may I know where do I write that ???

I know in Excel there's a button for Visual Basic
But I don't see that in access.

[ssanfu]

Press <Ctrl-G>, Then insert a new module. Paste the code. Click anywhere in the sub, then press the <F5> key. this is just an example.

In a query, you would use something like the IF() example.

I don't know what you are trying to do in the query, so these are just examples.

[LionelSpratt]

I would like to use that If() example.....but I can't seem to get it

At the moment in the query design view, I have in the "Score" field, the following criteria:
Like "*" & [Forms]![SearchF]![Score] & "*"

This is not exactly helping me.....since when I type m, and run the query (In the form that I created, which is called "SearchF")
If gives me all the results that contain both m and M, which is not correct, cause i asked only for lower case m's and not upper case M's

Now, if you can tell me how exactly should my criteria look like with the IF() function, I would appreciate it

[June7]

Access is NOT case sensitive (did you omit the NOT in your original post).

Try this:

1. create a field in query with expression: Asc([fieldname])

2. apply criteria to that constructed field: =Asc([Forms]!formname!textboxname])

If users accidentally enter several characters in the textbox, the Asc() function will work with only the first, including number values.

[LionelSpratt]

June 7

That one worked
However, as soon as I follow your instructions, other fields in the form gives me an error for whatever I type in, saying that the expression is typed incorrectly or is too complex to be evaluated.
Grrrr

Do you know how to write that If() function criteria ??

[June7]

If the simple Asc() expressions I suggest cause error, I expect an IIf using Asc() will also.

The Asc() function will error if field is Null or empty string as well if the textbox is left empty. Handle Null with Nz() function:

Asc(Nz([fieldname],0))