How to get exact match in SELECT

[DioPally]

I am new to coldfusion and am coming from a VBScript/ASP background using MySQL.

In this project I am doing I need to use MS Access. I have a table with a 50 character text field that contains a randomly generated "code" such as 8LGfkOsLrBKOaKJZo3fxglKSpBGydxKou66x9qF6LIPQIJLxfe.

I need to do an exact match on that code using MS Access and SELECT. I have a function thats purpose to see if the code already exists in the table. The is as follows:

<cffunction name="IsJobKeyUnique" access="public" returntype="boolean" output="false">
<cfargument name="jk" type="string" required="yes">
<cfset ret=True>
<cfquery name="temp" datasource="lrs">
SELECT * FROM jobkeys WHERE jobkey="#ARGUMENTS.jk#"
</cfquery>
<cfdump var="temp">
<cfif temp.RecordCount NEQ 0>
<cfset ret=False>
</cfif>
<cfreturn ret>
</cffunction>

However, the query is not case sensitive. In testing, I changed the last to characters from "fe" to "FE" and it still determines the strings to be equal.

I checked in MS Access to see if I could change the field type of BINARY or soemthing other than simply text but didn't see any options to do so.

Any help would be appreciated.

[craigkaminsky]

Would CF's Compare() function work for you? It does a case-sensitive comparison of strings but I can see where it might be an issue since you're looking to do the comparison during the select.

Here's a link to an MS article that might help for doing case-sensitive string comparisons at the DB level:
http://support.microsoft.com/?id=209674

Based on that article, something like this might be worth trying (I don't have Access available to test so this may completely bomb!):
<cfquery name="temp" datasource="lrs">
SELECT * FROM jobkeys WHERE StrComp(jobkey,"#ARGUMENTS.jk#") = 0
</cfquery>

The thing I noted from the MS URL above is that the Asc() function seems pretty useless. It only grabs the ASCII value for the first character in a string -- not too helpful in a lot of cases! StrComp() seems to be what you want but I don't know if it will run inside of CFQUERY or if you have to add a function in Access to accomplish this.