Intelligent String Compare?

Jan 31, 2005

A weird question, maybe an impossible one.

Is there a way do something like an intelligent string compare.

What I have is two tables.
I need to find all matching record in both tables based solely on the name.
However it is very common for minor spelling errors and punctuation differences to occur.
e.g. 20,000 instead of 20000 or Name: Whatever instead of Name - Whatever
Is there any way to do a string comparision on this sort of thing and return a liklihood of the strings being the same? Maybe a third party program that can be accessed via VBA? I must be able to control it by VBA as the program needs to be as automated as possible.

I know this may be asking too much but I would appreciate any input or ideas

Thanks

View Replies

Compare SQL String

Jan 4, 2007

Hi, all.

I have string: strSQL = SELECT SUM column FROM table

I need compare it with constant number, for Example:

If strSQL > 50000 Then ....

its not working, any Ideas?

Thank You in advance

View 4 Replies View Related

Please Help! Compare Weird Date String

Nov 8, 2007

Hi all,

I currently have two strings that look like this 070707 and 020709. What i need to do is compare these two dates and see which one is greater.
Is there any easy way to do this since the year is just yy instead of yyyy?

Thanks in advance

View 2 Replies View Related

Modules & VBA :: Concatenate String In A Loop To Generate A Dynamic SQL To Compare 2 Tables?

Jul 11, 2014

I am trying to concatinate string in a loop to generate a dynamic SQL to compare 2 tables. But I am not getting the result I want

Code:
Set rs = CurrentDb.OpenRecordset("r1")
Set rs2 = CurrentDb.OpenRecordset("r1_old")
columnCount = CurrentDb.TableDefs("r1").Fields.count
strSQL = ""
For I = 0 To columnCount

[Code] .....

I am trying to achieve something that reads like the following

r1.Field(0) = r2.Field(0) AND
r1.Field(1) = r2.Field(1) AND
r1.Field(2) = r2.Field(2) AND
.....

View 2 Replies View Related

Looking For "intelligent" WHERE Statement

Aug 29, 2007

Hi there,

I wonder whether the following problem can be solved with a Query or if I have to adjust my database.

I want to take records of my stock market trades and among others I have created two tables: The first one, called tblTransactions, contains transaction-related data and the columns are Date, Ticker, Quantity and Price.

Based on this the following query gives me then for any requested date the actual portfolio holdings for any requested date, e.g. March 9, 2007:

SELECT T.Ticker, sum(T.Quantity)
FROM tblTransactions AS T
WHERE T.Date <=#3/09/2007#
GROUP BY T.Ticker
HAVING sum(T.Quantity)>0
ORDER BY T.Ticker;

Additionally, I have a table called tblStops with columns Date, Ticker and StopPrice. The function of the stop price is, that if the last price quoted for a stock is below that price the stock is sold immediately at the market. However, this table does only include a price if it is changed, i.e. a price for a certain ticker is valid as long as a new stop is entered at a later point of time.

Example
Date. Ticker. StopPrice
03/09/07 XY 27.23
04/01/07 XY 28.89
04/15/07 XY 30.56
04/30/07 XY 31.23

In a next step I want to have a query, which gives me for a certain point in time the actual portfolio positions and additionally the stop prices which were in place at that certain date.

If I take again March 9, 2007 the task is simple:

SELECT T.Ticker, sum(T.Quantity), X.Stop
FROM tblTransactions AS T INNER JOIN [SELECT P.Ticker, P.Stop FROM tblStops AS P WHERE P.Date=#3/09/2007#]. AS X ON T.Ticker=X.Ticker
WHERE T.Date <=#3/09/2007#
GROUP BY T.Ticker, X.Stop
HAVING sum(T.Quantity)>0
ORDER BY T.Ticker;

My question: Is it possible to write a query which "understands" that if there is no stop price for the requested date it has to look up the one which is in the nearest past before the requested date? That is, when requesting the portfolio composition for April 13, 2007 it should recognize that the valid stop price for stock XY is 28.89 (i.e. that of April 1, 2007, the date it was changed the last time before April 13).
Or do I have to adjust my database, i.e. take tblStops and fill up the dates and corresponding stop prices in between?

At the end of the day I want to request the data from Excel via VBA and ADO, i.e. an VBA solution would be helpful as well...

I hope I was clear enough and would be more than happy for any hint that could lead to a solution.

Best regards
JapanFreak

View 2 Replies View Related

Modules & VBA :: Separate Numbers And String From Alpha-numeric String

Jun 7, 2013

MS-Access VBA code to separate numbers and string from an alphanumeric string.

Example:

Source: 598790abcdef2T
Output Required: 598790

Source: 5789065432abcdefghijklT
Output Required: 5789065432

View 13 Replies View Related

String Validation (string Must Start With Http://)

Mar 12, 2007

Hi all,

I was looking for some help. I am trying to setup a table with a field for web address. People are entering www.website.com etc however I need them to make sure it starts with http:// Is their any way I can put validation on the field to make sure that this is entered? Or maybe I could use an input mask?

Any suggestions would be gratefully recieved.

Andy.

View 3 Replies View Related

Search For A String Within A String

Oct 27, 2006

I have a column called CPU_S within a table called workstation that contains sample text like P111 933

I want to use the update command to search the CPU_S column for entries that contain this in there string then add P3 to a column called CPU_N

So far I have the code below but I don't know how to search a column entry for a specific string within a string. Can this be done and how?

UPDATE workstations SET CPU_N = "P4"
WHERE CPU_S = ;

View 3 Replies View Related

Compare VBA

May 9, 2007

Is there some free software somewhere to allow me to compare the vba in two differnat access dbs highlighting the differances??

THanks

View 2 Replies View Related

Compare

Dec 21, 2006

Hi All:

I need a mechanism that takes the excel spreadsheet or table and determine if there are any new deal groups. (In this spreadsheet I allready have a deal group field) I will be using Book and DealGroup as combination primary key to compare against the table "8 -- YTD Principal Investments" which will hose the YTD numbers and serve as a historical table we will update throghout the year.If there is a new deal group the append into table "8 - YTD Principal Investments"

Thank you

View 1 Replies View Related

How To Compare Before And After

Apr 26, 2007

Please help with query/report. I have the following problem.

One student evaluates course before and after. There is several questions related to this evaluation (all are same before and after) and for one question there is 5 possible answer (stored in option group)

I would like to build a report or a query where I can compare data from each student (before and after) for all questions. Is this possible to do in access.

So, StudentA: questionOn: answer: one columen: before, second column: after�. Next question.

View 1 Replies View Related

Compare Digits

Mar 8, 2007

Hi there!
I'm bulding this database where the user will have to insert a full phone number. What I would like to know is? if is there any way where Access can pick the first 5 digits of the number to compare with information already imputed in. by example if the user input the number 01942789789, access pick the number 01942 to look for a match? I know I could do with two diferents fields one for the code number and other for the number, but my boss only want one field. Is there anyone that could help me

Thanks

www.joaobeca.co.uk

View 1 Replies View Related

Compare Query

Aug 19, 2005

I have a table that shows Part Numbers. Each part number can be listed multiple times depending on how man Build ID's it is associated to.
Example:
Part Nubmer 123456 could be associated with:
Build Id's F187, A910, F001, C810, E010, C809
or it might only be associated to 1, 2, 3, 4,or 5 of those Build Id's.
What I want my query to do is find any Part Number that is only associated with anything less than all six of the Build Id's.
Example:
Part Number 123456 is only associated with:
Build Id's F187 & A910 or only F001 & C809 or only E010.
If this was the case I would like for that Part Number to show up in the Query along with whatever Build Id's it is associated with as long as it is not associated with all six Build Id's. The Build Id Field is also part of the table.

Any help on how to do this.

Hope all of this makes sense.

Thanks!

View 1 Replies View Related

Compare 2 Tabels

Feb 8, 2006

Hi,

I've made 2 tabels tblIn and tblOut,
Now I want to make a query which get the differences between the number of product A in tblIn and the number of product A in tblOut the same for product B,.... It's possible that there is nothing in tblOut from product X so the query just has to show the number of tblIn of product X, I've tried a few things but i can't make the query can anybody help me???

thx

View 4 Replies View Related

Compare Two Lists

Mar 5, 2007

I have two list's of names. I'm able to run a query to find the matches but the ones that don't match i also need to id?
For ex. List a has 200 names List b has 130 names, how can i find the 70 names that don't match?

View 4 Replies View Related

Compare 2 Tables

May 31, 2007

Situation:

Have 2 Tables that contain the same information, one table was used during registration and had the Data changed

Question:

How do I pull a comparison query to match the registration table to the Main table and pull only the data that is different so I can update the main table with the new Information? :confused:

View 2 Replies View Related

Compare Strings In SQL

Jul 10, 2007

Hello,
I would like to put a restriction on the SQL statement such that if one particular field contains the character "MO", then we take in the record, else we ignore them. May I know how could this be done in the "WHERE" part of the SQL statement? I do not think SQL would read things like Instr(). Thanks!

Regards,
Anyi

View 13 Replies View Related

Compare And Delete

May 1, 2008

I'm trying to setup a delete query that cleans out the records in one table that are also in another table. I was trying to use the address field (would use name but one table splits it First and Last and the other doesn't) but I just can't seem to run the delete query when using a join

anyone have any ideas on how I could setup this delete query?

View 2 Replies View Related

Compare Date

Jul 12, 2005

Hello all
Ihave tblyear contain ( B_date and E_date )
in anoter form have feald ( invdate ) i wnat ( Invdate) is between B_date and E_date of the tblyear.
Thank u

View 10 Replies View Related

Compare Database

Dec 21, 2004

Is there a tool that is not to expensive that can be used to compare access databases for differences.

View 2 Replies View Related

Compare And Update

Dec 28, 2004

I need to campare two tables. if there are new records from table A would like to add it to table B.
Also if there are any changes in a field of record i would like to see the change in table B.
thank you
Mark

View 1 Replies View Related

How To Compare Records

Feb 6, 2004

Hello Ladies and Gentlemen,

My Access-table contains a Destination and Time field, i want to compare records for Time field with next records of same field.How can I do it..? Let me tell u an exp..
Time field is having a record like 06:23:45 AM for the destination - 'A', so i want to get all the records where the difference is upto 5 Seconds (i.e - 06:23:50 AM) and the Destination is same. It might be possible that there are one more records having time value 06:23:55 AM.

View 6 Replies View Related

Compare Tables From Diff Db's

May 5, 2005

hello, i have checked out the forum for pevious Q's to this problem and found the below.

http://www.access-programmers.co.uk/forums/showthread.php?t=80692&highlight=compare+tables

only problem is thats its writen at a bit too technicle a level from where i am. i've done some investigation into the problem though.

found out i need to use the docmd.transferdatabase function to import the tables i need to compare into my current db.

my question is this. how does it import them, does it create new tables within the current db or is it put somewhere in temp memory for use only while db is open, not sure how it would work. ideally i would like it to import tables, do the table comparison then create a report of the differences or make a new table showing the differences, then it reverts back to how it was before i imported it. i.e. he current databse is unchanged except for maybe a new report or new table (the results).

thank you for any advice.

Neil

View 1 Replies View Related

Compare Value With Nextrecord In Query

Oct 3, 2006

Is there a way to compare the value in one field with the value of the same field in the next record? I can do it in Excel by using the following formula:

=If(A1=A2,"Match","No Match")

But I need to be able to do this in an Access query?

Any ideas?

View 6 Replies View Related

Compare Memo Fields

Feb 5, 2008

Can someone tell me if this can be done somehow.

I have a date field, ID field and a memo field (I have to have this memo field).
I want to compare the memo fields by two certain dates and find out what doesn't match and if the ID field is blank. I have been trying with quaries but no luck.

I am not sure what forum this would fall under so I put it in general. feel free to move.

View 7 Replies View Related

How To Compare Structure Of 2 Databases

Sep 10, 2006

I want to compare the database-structure of 2 Access databases. I have a database which is used for developing (added some fields, changed some field lengths, added some tables) and a live database.

Can I export the structure to some text-file of both databases, and use a compare-tool to see the differences? Or are there tools available for such compare job?

View 1 Replies View Related