Data format

[robinrd]

I am trying to run a query between 2 tables based on an account number. The
problem I am having is that in one table, the account number is listed as
###-####, where as in the other, the account number is simple a string of 7
numbers. How do I either remove the dash in the first table or run a query
that will allow me to match the 2 account numbers even though they are in
different forms.

I also need to match 2 other tables on account numbers. The problem here is
that one field reads, for example, "C 123 4567 2.50 01/10/2007" where as
the other reads 1234567. I need to mathc the 123 4567 with the 1234567.

Any help would be GREATLY appreciated!

 

[Jackie L]

A lot will depend on if this is the structure of the fields all the time or
if this is just an example of a record. For instance if the account number
is always ###-####, you can do a query to strip out the dash

NewAcctNumber: Left([AccountNumber],3)&Right([AccountNumber],4)

If the dash moves to different positions, then you will have to do a formula
like

NewAcctNumber: Replace([AccountNumber],"-","")

Either way, it would work best if you added a field to your table where the
account number has the dash and do an update query to populate that field
with the new account number so that you do not continually have to have a
query to link the two tables.

The same is true for your second example. If the data in your field is
always in the same structure, you can create the correct account number with:

NewAcctNumber: Mid([FieldName],3,3)&Mid([FieldName],7,4)

If the data is not always the same, we would need to look into it further to
find the common factor (i.e. it always follows the letter C)

Hope this helps,
Jackie

 

[robinrd]

Thank you! This looks like exactly what I needed.

A lot will depend on if this is the structure of the fields all the time or
if this is just an example of a record. For instance if the account number
is always ###-####, you can do a query to strip out the dash

NewAcctNumber: Left([AccountNumber],3)&Right([AccountNumber],4)

If the dash moves to different positions, then you will have to do a formula
like

NewAcctNumber: Replace([AccountNumber],"-","")

Either way, it would work best if you added a field to your table where the
account number has the dash and do an update query to populate that field
with the new account number so that you do not continually have to have a
query to link the two tables.

The same is true for your second example. If the data in your field is
always in the same structure, you can create the correct account number with:

NewAcctNumber: Mid([FieldName],3,3)&Mid([FieldName],7,4)

If the data is not always the same, we would need to look into it further to
find the common factor (i.e. it always follows the letter C)

Hope this helps,
Jackie

I am trying to run a query between 2 tables based on an account number. The
problem I am having is that in one table, the account number is listed as
###-####, where as in the other, the account number is simple a string of 7
numbers. How do I either remove the dash in the first table or run a query
that will allow me to match the 2 account numbers even though they are in
different forms.

I also need to match 2 other tables on account numbers. The problem here is
that one field reads, for example, "C 123 4567 2.50 01/10/2007" where as
the other reads 1234567. I need to mathc the 123 4567 with the 1234567.

Any help would be GREATLY appreciated!