Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Dealing With # In Imported Data

Dealing With # In Imported Data

Dealing With # In Imported Data

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.

RE: Dealing With # In Imported Data

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.

RE: Dealing With # In Imported Data

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

CODE --> NewColumn

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

Hook'D on Access
MS Access MVP 2001-2016

RE: Dealing With # In Imported Data

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.

RE: Dealing With # In Imported Data

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.

RE: Dealing With # In Imported Data

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close