Dealing With # In Imported Data 2

[BTilson]

I am working on designing a database to manage tool calibrations. This task was previously handled by a very unwieldy spreadsheet. I have created a master table for the tool data and a linked table to store calibration data for each tool. This is a one-to-many setup. This all works fine for about 95% of the data. However, some of the serial numbers of the tools I am logging have the # character in them. Examples would be "#24" or "SN#556".

The problem is that for some reason, the data from main form to subform (Main form to display tool data, subform displays calibrations for that tool) won't link on the fields that have # in them. The tool displays in the main form fine, but none of the linked calibrations show up. I am confident this has something to do with Access viewing the # as the date format indicator, but I can't figure out a practical way around it.

I thought about writing a function to loop through the table and replace any instance of # with some other character, but I'd really rather avoid such a blunt approach, if possible. Does anyone have any ideas what can be done about this?

Thanks so much for your time.

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[sxschech]

If your form is based on queries, then you could use the replace function in the query for that field and this won't change the underlying data, only how it is displayed.

 

[dhookom]

I agree with sxschech,
Your form and subform record sources should be queries where you can add a calculated column like:

LinkSerialNumber: Replace([Serial Number],"#","~")

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[BTilson]

Thank you both very much! The form and subform record sources are indeed queries, so I will try this approach.

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[BTilson]

Just wanted to let you know this approach works perfectly. It now behaves exactly as desired across all records. Thank you so much!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[sxschech]

Glad it worked and sorry, I should have provided an example as Duane did. Thanks Duane.