is there some easy way to do a compare values (obviously pull the access info into a excel sheet would do part of it) between a access database and a excel spread sheet?

for example i need to compare (see if they are = ) the last 4 digits of a column in the database with a column in the excel (again last 4 digits) directly compare a few columns, and potentialy convert one column in the access from:
a1
a2
a3
a4
a5
a6
a7
a8
a9
b1
b2
etc into
1
2
3
4
5
6
7
8
9
10
11
respectively

any ideas? (my brain seems to be locked down for some reason today)

5/1/2009 12:08:57 PM

You could export the data as CSV and write a script in your language of choice. Or I'm sure there's a way to handle this with VBA.

5/1/2009 12:17:52 PM

as long as you are pulling the Access data into Excel first, there should be no problem. You can basically just use formulas like =IF(A1=B1,"same","different"). You can also use conditional formatting to highlight cells that are different.

If you need to compare only parts of a cell, create an intermediate column and use =RIGHT(A1,4) to pull out the last 4 digits of a column. Or, in the IF statement, something like =IF(A1=RIGHT(B1,4),"same","different")

unless you post any specifics that are more complicated, then I'm assuming the IF statement should cover most of your cases

5/1/2009 12:29:22 PM

yeah i think that's the way to go, sorry i had a major brain lock this morning...

5/1/2009 12:48:51 PM