case sensitive in SELECT statement

[johnnyv]

Hello all

I have a Access DB that I am saving and retrieving records to/from. I want to limit the user to save only 1 record per client name per Advisor per day.
Here is my SQL statement

strsql = "SELECT * From INV_Prof Where ([ClientName] = '" & strClientFullName & "') AND ([DateSaved] = #" & sysdate & "#) AND [AdvisorKey] = " & g_udtAdvisorInfo.lngAgentID


This works well except that when I save a record using a client name of
donald d duck
then try to save a second record with a client name of
DONALD D DUCK
I am able to save this second record.

In other words the sql statement as it is, is not seeing these two names as being the same.

How can I fix this?

Thanks

 

[Dimandja]

Change names to all uppercase before saving.

 

[johnnyv]

Thanks for the rreply Dimanja

Unfortunatly this will not work as I need to be able to retrieve and display the records at a later date including the Client Name.

 

[Dimandja]

Maybe I need more details about the problem.

1. The field containing the name must be a unique key field, or you must look for an existing same name record before inserting.

2. For the above to work, the existing record name field and the new record name field must all be compared in the same case (either lower or upper). This is why you must be case consistent when saving.

3. Why do you say Unfortunatly this will not work as I need to be able to retrieve and display the records at a later date including the Client Name? What am I missing here?

 

[johnnyv]

Dimandja

I think you may have answered my question with point #2

The client name is not unique as there may be more than 1 client with the same name. I had hoped to be able to compare names regardless of capital letters but you syggest this is not possible. The problem with converting the names to upper case is that I need to be able to retrieve them at a later date and display them. Because of this I can not convert them to upper case.
John vanStream is not the same as
JOHN VANSTREAM
The Dutch can be a little touchy about misplaced capital letters

I think the problem is not what you are missing its what I am missing, a proper way to provide a unique identifier for each client.

With that said you answered my question
"you must be case consistent when saving"

Thanks again

 

[petermeachem]

Access isn't case sensitive. So, it doesn't matter whether it is saved as upper, lower or both. As far as Access is concerned John vanStream and JOHN VANSTREAM are the same.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[Dimandja]

I think the problem is not what you are missing its what I am missing, a proper way to provide a unique identifier for each client.

That's right. Creating a unique identifier for each client is how this should be handled. You can use their logon ID, for example, or create an ID whenever they first enter your database. When subsequently entering information related to your customers, don't use their name - use their ID instead.

When you need to write out their name, use the ID to retrieve it.