Case Sensitive Update

[ogrerugby]

I have an update query and it looks as if it was not case sensitive. Is that true or do I need to specify case sensitive in the query?

Here is the query that ran:

UPDATE Variations INNER JOIN EmailIndexes ON [Variations].[Variations]=[EmailIndexes].[MailTo] SET EmailIndexes.UserNameTo = [Variations]![Name]
WHERE ((([EmailIndexes].[MailTo])=[Variations]![Variations]));


I just need to be sure that the values in my where clause are matching case.



thanks for the help.

 

[dcx693]

I got most of this info from Access Cookbook:

Access normally performs case-insensitive string comparisons. You can use Option Compare Binary in a VBA module as discussed here: rst.FindFirst - Case Sensitive for String field to force VBA to do case-sensitive comparisons inside that particular module, but it won't affect your queries (unless they use custom functions that call functions within those VBA modules with Option Compare Binary).

Easier workaround: use the VBA StrComp() function, which defaults to a case-sensitive comparison of strings.

 

[ogrerugby]

Can I do it in a query....

Or do I have to write a module??? Sorry, new to access.

 

[ogrerugby]

Can I do it in a query....

Or do I have to write a module??? Sorry, new to access.

 

[dcx693]

First, are you sure those are supposed to be ! characters in your query? And not . characters?

You will either have to write a module with a custom function that does the case-sensitive comparison for you, or you can try modifying the query to be based on a matching process that uses the StrComp() function.

Just off the top of my head, you could try altering your query so that this piece:
WHERE ((([EmailIndexes].[MailTo])=[Variations].[Variations]))
becomes:
WHERE StrComp([EmailIndexes].[MailTo],[Variations].[Variations],0)=True

Try it out. I've never done it before myself.

 

[ogrerugby]

Thanks, looks like it will work

I just did a quick test vs 2 test tables and I think the results will be correct now. Now I get to run it vs my 1+million row table.

thanks again.