Query Question - removing characters from fields

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MsCotman - 14 Aug 2006 16:37 GMT

I have eight tables. Half of the tables show shortage debits (SEs) created
for the months of April - July. The other half of the tables show add backs
(ABSEs) to the shortages created for the months of April - July. I was told
that I should be able to remove the AB from the ABSEs and do a match query to
see how many of the shortages we paid back. The Director called it a "mask"
but I do not believe that is the correct terminology. I plan to compare one
SE table to the four ABSE tables.

If anyone has any idea how I am about to do this I would appreciate any
assistance.

Thanks.

Ms Cotman

Reply to this Message

KARL DEWEY - 14 Aug 2006 18:44 GMT

First you should have only one table instead of eight. Use a field for date
and another for transaction type.

To work with what you have create a select query and add a calculated output
field the removes the 'AB' using one of two methods.
You can use Replace function --
New Field: Replace([YourFieldName],"AB","")
Or like this if they are all 5 characters in lenght --
New Field: Right([YourFieldName],3)

Use the query instead of the table to match it with the other table in a
query.

> I have eight tables. Half of the tables show shortage debits (SEs) created
> for the months of April - July. The other half of the tables show add backs
[quoted text clipped - 10 lines]
>
> Ms Cotman

Reply to this Message