Query design help Access

Try:
'10' & (Left([AptNum],2) & Nz("-" & UCase(Right([tblListAptLetter.AptLetter],1)),'')

Note that the last two characters before the final paren are two single quotes.

So it says its missing a ([ or |
i cant figure out where it should go.

'10' & Left([AptNum],2) & Nz("-" & UCase(Right([tblListAptLetter.AptLetter],1)),'')

Doug
Unfortunately I couldn't make your solution work

Rey
I don't know where to paste the Sql code?

Might it be better to make this join in the table itself rather than from a query?

I have fixed it for you
HCPV.zip

Again, your main problem is - as Pat has mentioned before in another question - your table structure. You should always ensure that you can join between fields where the one-side is a straight AutoNumber. If not, you will continue to encounter issues like this.

/gustav

@beatified

see the revised query in this sample db
DB_28921385.accdb

I'm not sure I have been clear enough to everyone about my issue.

@Rey Obrero
You seem to have nailed it.
I'm just curious how you were able to enter the sql code. was that by right clicking and selecting build?

Also would it be better to do this as a combined field in a table vs as a query?

@Rey
Just figured it out right click the tab and select SQL

<I'm just curious how you were able to enter the sql code. was that by right clicking and selecting build? >

create a new query, do not select any table
in the Design icon on the upper LEFT corner, select SQL from the dropdown
you will see a white screen with

SELECT

here is where you will paste the SQL statement that you copied.. you have to overwrite the "SELECT" word shown.

@beatified
don't forget to close the thread.

I will just one more question on the whole issue of me creating the tables wrong.

@Gustav
Is there a problem with my table structure. From what I can tell @Pat had mentioned that I should not be allowing duplicates under certian circumstances and not in others but rather archive certain records and allow duplicates in the archived table and do not allow duplicates in the non archived area. What he was talking about was a normalization issue. And I completely agree with him on that. It seems like it is this thread you are talking about.
https://www.experts-exchange.com/questions/28920913/Allow-duplicates-under-certain-circumstances-in-access.html

@Rey Please feel free to mention if you see any issues relating to my table structure if you do simply say yes and I will create another question.

it is better that you create another thread for a different topic.

Ok will do.

Excellent help and took me through it step by step which I need as a noob.