How to compare hyphenated to non-hyphenated numbers in Access?

Question

I have a list of serial numbers that are formatted "&&-&&&&&". I need to compare them against an electronic report of S/Ns that do not have the dash. Both are contained in tables in Access. How do i write the query for this?

Answer 1

Contrary to what others are saying, you MAY NOT need to do any string manipulation at all!!



It really depends on whether or not the numbers in the TableB were entered with an Input Mask (where TableA contains numbers from the electronic report, and TableB is the other table).



If NO Input Mask was used for TableB's data entry -- AND a dash was NOT entered, then no string manipulation is necessary. (stating the obvious)



If an Input Mask WAS used for TableB's data entry -- AND its second section was a 1 (or not specified), then no string manipulation is necessary. That is because the Format property for the field is for display only (and is superceded by the Input Mask). The dash does NOT get stored along with the digits.



If an Input Mask was used for TableB's data entry -- AND its second section was a 0, then the dash DOES get stored along with the digits (and some string manipulation WILL be necessary).



FYI - I have tested this several different ways in Access 2003.



§

Answer 2

The first poster was correct - if you want to compare the numbers, you will probably need to do some string manipulation in the VBA code, as well as create a query that returns a recordset containing both numbers for each row. Are you familiar with the ADO library? If not, you will probably need to look into it so you can get familiar with recordset objects. If you were developing in SQL Server, or Oracle, you might be able to create a stored procure that could format and compare the numbers. I don't think Access has those capabilities though. If you need more assistance, feel free to email me.



UPDATE : Ok - so I learned something new today. You CAN do advanced string manipulations in the SQL in Access, Check this link out :



http://www.databasedev.co.uk/access-sql-string-functions.html



It would take some serious manipulation, but it can be done in the query. I learned something new today - thanks!

Answer 3

You would have to use Substring and String concatenation functions in Access.

I am not familiar with Access so I can't give you the code.