UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To Compare 2 Fields Alphabetically?, Access 2016    
 
   
appro
post Feb 15 2020, 09:01 PM
Post#1



Posts: 203
Joined: 23-January 05



I have 2 combo boxes, "Equipment 1" and "Equipment 2" on a bound data entry form.

The Row Source of both combo boxes is from a single "Equipment Lookup" table. Is there a simple way to flag to the user who is entering data if the "Equipment 2" entry is "earlier" alphabetically than "Equipment 1"? I would want this to happen on the "After Update" event of "Equipment 2".

So....if "Dozer" is entered into "Equipment 1" and "Grader" is entered into "Equipment 2", the user can proceed. But if "Grader" is entered into "Equipment 1" and "Dozer" is entered into "Equipment 2" a message box would appear alerting the user.

I've been Googling this for hours and have found nothing. Hopefully it is relatively simple.
Go to the top of the page
 
GroverParkGeorge
post Feb 15 2020, 09:27 PM
Post#2


UA Admin
Posts: 36,970
Joined: 20-June 02
From: Newcastle, WA


This is kind of unusual, isn't it?

I'm used to seeing this kind of comparison for dates. A similar process should work for comparison of strings, though.


Here's a simple, simple example. It might give you some ideas.

Attached File  CompareStrings.zip ( 20.39K )Number of downloads: 5


I would imagine that a full solution will be a bit more complex, but this is the basic idea. Note that I used Option Compare Binary in the module rather than Option Compare Database to ensure that "grader" is returned as less than "Grader".

Also, note that it doesn't handle the case where the two words are the same, e.g. "grader" in text box one and also in text box two. If that is an issue, you'll need to address that option as well.

Also, I used text boxes, but combo boxes would work the same. However, you'll need to make sure you're comparing the "text" portions of the combo boxes rather than a surrogate key if the combo box is bound to that.
This post has been edited by GroverParkGeorge: Feb 15 2020, 09:29 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
appro
post Feb 15 2020, 09:31 PM
Post#3



Posts: 203
Joined: 23-January 05



Thanks very much George. Much appreciated.
Go to the top of the page
 
GroverParkGeorge
post Feb 15 2020, 09:51 PM
Post#4


UA Admin
Posts: 36,970
Joined: 20-June 02
From: Newcastle, WA


Here's a better version using combo boxes.

Attached File  CompareStrings.zip ( 24.89K )Number of downloads: 10

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
appro
post Feb 15 2020, 09:54 PM
Post#5



Posts: 203
Joined: 23-January 05



Thanks again George. I'm about to implement it on my form now.
Go to the top of the page
 
GroverParkGeorge
post Feb 16 2020, 09:34 AM
Post#6


UA Admin
Posts: 36,970
Joined: 20-June 02
From: Newcastle, WA


Continued success with your project.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
appro
post Feb 16 2020, 04:46 PM
Post#7



Posts: 203
Joined: 23-January 05



I've implemented it and it works really well. I've added code to take into account if "Equipment 2" is "NA" and ignore it, which is often the case because most of the time only one Equipment Type is involved.

I've also added code to switch the 2 Equipment Types around automatically if they are entered in the wrong order. smile.gif
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    31st March 2020 - 06:07 PM