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

Dig Deeper on Oracle DBA jobs, training and certification

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.

Please create a username to comment.