Field search and comparison

[merlin666]

Is there a way to take two fields and compare them and list the differences. Ie. Compare field one "This is a good day" to field two "Tomorrow is better" and have a report stating what letters are in both fields, what letters are only in field 1 and which are only in field 2. Field could also contain numbers if that makes a difference.

 

[raskew]

Hi -

Technically, with a lot of code, what you're asking is probably doable. But, I've got to ask: Why are you attempting to do this? What's the situation?

Bob

 

[ACMAIN_CHM]

one simply solution like below, you can also use the ucase(col) if not case sensitive.

create table t ( col1 varchar(30),col2 varchar(20))
select col1,col2,
iif(instr(col1,'A')>0,10)+iif(instr(col2,'A')>0,1) as flgA,
iif(instr(col1,'B')>0,10)+iif(instr(col2,'B')>0,1) as flgA,
iif(instr(col1,'C')>0,10)+iif(instr(col2,'C')>0,1) as flgA,
...
iif(instr(col1,'Z')>0,10)+iif(instr(col2,'Z')>0,1) as flgZ,
from t
01 not in COl1, but in Col2
10 in col1, but not in col2
11 both

 

[raskew]

Hello -

Have you tried running that code?

Bob

 

[merlin666]

I am in charge of changing an outdoor marquee(magnetic letters). We have a database that allows me to plan for what will be on the marquee over the coming months, but the report doesn't give any letter changes. I usually have to go through the letters on paper and cross off what I don't need to take out with me, or I could take out all the letters. I found a program called marquee manager that does this but on a change by change basis. I am looking to add similar functionality to the report so if I'm not there someone can quickly look and see what letters need to be taken out for the change.

 

[merlin666]

Please accept my apology as I am somewhat new with access. Where would I put the program in order to run it. Visual Basic editor?

Thanks for all the help,

 

[pono1]

Array madness...

Regards,
Tim

 

[WayneRyan]

Very Nice Tim!

Wayne

 

[merlin666]

Tim, Thank you for the access file. I really appreciate the help.

Mike

 

[merlin666]

One more question on this. Can I call the code from a query or table, using data that is in 2 fields to populate old and new and return just the myNeedResult letters to a third field so I can add that field to a report? Is there a better way to do this. Our table has evens for a 3 or 4 month period and the report I print has all these events listed. I'd like to add the letters needed to the report for each event.

 

[pono1]

Are you asking if you can get "title" data from a table or query -- rather than from a form? If so, the answer is likely a yes.... You might be able to tweak a function here and call it from inside an Access query and then use the query as the record source for your report. You may also be able to get some of what you want from SQL with Group By and a count function (there is info on these in Help). Beyond that, to assist further we would need to know how your tables are set up... And FYI: I don't generally have the time or energy during the week to respond effectively to technical posts here but no worries--there are a lot of experts floating around the forum...

Regards,
Tim

 

[merlin666]

Understandable on the time issues. I've simplified this and tried changing the code to see if I can get this working.

Table has three fields - txtcurrentmarquee, txtnewmarquee, and letters
Query has same three fields.

It looks like I get everything working except returning the value to the query.

See database attached for more info.

Thank you a million times for the help.