simple report question - MS Access 2000

[kowalsky]

hi all,
I need to replace a field in a Detail section with just a portion of it, and based on that portion I need to bring back another field from some table and either concatenate to this field or add a new one.

I hope the info above is enough, the report is quite a bit more complicated than that but in principle this is my question:

the report looks like that:

ID field1 field2
-- ----- -----
A1B2C3 1000 ABCD
A1B2C3 1500 WXYZ

Now, there is a table that maps 2C (a portion of the ID field) to some description, suppose the table is called desc; so for 2C my description in the desc table is "two cee".

I need to modify the above report to read:

ID field1 field2
-- ----- -----
2C two cee 1000 ABCD
2C two cee 1500 WXYZ

The 2C part was simple: Right(Left(ID, 5), 2). But now, based on this value I need to retrieve the "two cee" value from the desc table.
Is it possible to write a formula in my ID field in the report that runs a query with a parameter that hapens to be just the Right(Left(ID, 5), 2) value?

Thanks,
kowalsky

 

[pbaldy]

For starters, you can use the Mid() function rather than both the Right() and Left() functions. You could use a DLookup with the resulting value to get your second value. You could probably even join the 2 tables, though it would be a join that couldn't be represented in design view.

 

[kowalsky]

Thanks a lot pbaldy,
it worked like a charm!
Can you recommend any good advanced training for Access 2000 - I will be buried in "legacy" stuff for some time and I need to brush back through these things long forgotten ...
Thanks again,
kowalsky