Problem solve Get help with specific problems with your technologies, process and projects.

Concatenating a literal in Microsoft Access

How do I join tables with different data formats in the field? For example in ACCESS 97: One field has 1AA10 in...

one table but in the other table I have AA10. I need to join the tables together. The 1 in 1AA10 is for fiscal year 2001 and next fiscal year it will read 2AA10 for fiscal year 2002. (1 must be the fiscal year.) The portion of the field I need to join is AA10. Please help an SQL newbie!

The SQL depends on where the 1 and 2 will be coming from. If you will be hardcoding the values into the query as literals, you can use

select * 
  from oneTable, otherTable 
where oneTable.ID = '1' + otherTable.ID

Note that the above uses the Microsoft Access syntax for concatenating character fields; the standard SQL syntax uses double pipes, i.e.

'1' || otherTable.ID

If you wanted to obtain the '1' or '2' from an actual fiscal year number, you would need to convert the year number to a string, then take the rightmost character of it. Here's an example using the year of the current date, which in Microsoft Access is given by the Now function --

select * 
  from oneTable, otherTable 
where oneTable.ID = Right(Str(Year(Now)),1) + otherTable.ID

This was last published in August 2001

Dig Deeper on Oracle DBA jobs, training and certification



Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.