G
Guest
Access 2k, Win 2k
Hi,
I have 2 tables, each from different sources that contain information on network devices, routers & switches. The first table's information comes from a Perl script that telnets into these devices and pulls back information on them, model name, device name, serial#, ect... The second table's data is entered by hand. These 2 sets of data should be the same, but in fact they are not. I need to create a report that identifies the differences. That's simple enough, but I need something further.
The first report my project mgr wants to see is a list of model name differences, since those are what drive internal billing charges. Take these few examples.
generated data - hand typed data
--------------------------------------------
cisco7206VXR - cisco7206
cisco1721 - cisco1721-vpn/k9
Both examples have the same device name, and both really are the same model name. However, my query flags both of those as an error.
I need to know how to refine my search, or how to strip my strings to allow for a better search.
What would work is to strip "cisco" and anything after the 4-digit model number, but I can’t get something like to work. The devices aren't all cisco's, but those don't matter right now, so imagine my data is all cisco.
Here is how I have been stripping ‘cisco’:
Expr1: LCase(REPLACE([Model],'cisco',''))
The results:
7206VXR - 7206
1721 - 1721-vpn/k9
I have tried to run another query that uses the SUBSTRING function to get the chars after the model #, but, Access doesn't have the SUBSTRING function in its library.
I have tried writing my own version of that function using a module, but I learned VB.Net and many of the properties and functions I want to use are not available in VBA 6. However, I was successful in hardcoding different string endings and getting them removed from the string in my procedure, however, I could NOT get that cut up string to return to the query. I passed the initial string as ByRef.
As you can tell, I'm a person who likes to play with different options and I have tried more than I've said. I would really appreciate any help I can get. I have found Access SQL and VBA 6 to be very limiting and that really annoys me.
Thanks,
Tyler
Hi,
I have 2 tables, each from different sources that contain information on network devices, routers & switches. The first table's information comes from a Perl script that telnets into these devices and pulls back information on them, model name, device name, serial#, ect... The second table's data is entered by hand. These 2 sets of data should be the same, but in fact they are not. I need to create a report that identifies the differences. That's simple enough, but I need something further.
The first report my project mgr wants to see is a list of model name differences, since those are what drive internal billing charges. Take these few examples.
generated data - hand typed data
--------------------------------------------
cisco7206VXR - cisco7206
cisco1721 - cisco1721-vpn/k9
Both examples have the same device name, and both really are the same model name. However, my query flags both of those as an error.
I need to know how to refine my search, or how to strip my strings to allow for a better search.
What would work is to strip "cisco" and anything after the 4-digit model number, but I can’t get something like to work. The devices aren't all cisco's, but those don't matter right now, so imagine my data is all cisco.
Here is how I have been stripping ‘cisco’:
Expr1: LCase(REPLACE([Model],'cisco',''))
The results:
7206VXR - 7206
1721 - 1721-vpn/k9
I have tried to run another query that uses the SUBSTRING function to get the chars after the model #, but, Access doesn't have the SUBSTRING function in its library.
I have tried writing my own version of that function using a module, but I learned VB.Net and many of the properties and functions I want to use are not available in VBA 6. However, I was successful in hardcoding different string endings and getting them removed from the string in my procedure, however, I could NOT get that cut up string to return to the query. I passed the initial string as ByRef.
As you can tell, I'm a person who likes to play with different options and I have tried more than I've said. I would really appreciate any help I can get. I have found Access SQL and VBA 6 to be very limiting and that really annoys me.
Thanks,
Tyler