Problem with Concatenation of Field 2

[WebGodiva]

I'm a newbie and I'm trying to do something that is most likely way over my head. But here goes.

I have two queries that I am trying to join into one with one field being concatenated together. I read the FAQ and created the module as suggested, compiled and saved. Now, when I run the query I get that their is an ambiguous expression in the query which prevents it from working. The statement for the concatenated portion is below:

JoinedReg1: Concatenate("SELECT CertificationTitle & Chr(58) & Space(2) & StateIssued & Space(1) & Chr(40) & Chr(35) & RegNumber & Chr(44) & Space(1) & 'expires' & Space(1) & RegExpires & Chr(41) & Space(2) & FirstIssued FROM qryEngRegistrationsWTitle WHERE Emp_No=" & [Emp_No])

It was working great yesterday, I didn't change anything, my system locked up today due to a power surge (always happens) and now it won't work at all. And yes, we have surge protectors - they are just so strong they get through everything when they happen.

I just keep getting the ambiguous name in query expression error.

Any help would be appreciated.



"One of the secrets of life is to make stepping stones out of stumbling blocks." Jack Penn

 

[PHV]

What happen if you create a brand new query with this SQL code ?
SELECT CertificationTitle & Chr(58) & Space(2) & StateIssued & Space(1) & Chr(40) & Chr(35) & RegNumber & Chr(44) & Space(1) & 'expires' & Space(1) & RegExpires & Chr(41) & Space(2) & FirstIssued
FROM qryEngRegistrationsWTitle

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

PHV,
Good test of the SQL.

WebGodiva,
This function resides in a query. Is there more than one table in the query that has the field [Emp_No]? Is there more than one [Emp_No] field in qryEngRegistrationsWTitle?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[WebGodiva]

Wow, thanks for the replys. Sorry it took so long to get back to the question, i got stuck in a meeting.

Ok, there are two tables in the query and only one Emp_No field in each table....it is the primaryID for both tables...

What I'm trying to accomplish is to have the records in qryEngReistrationsWTitle (which combines all of the records for one employee at a time) actually end up combined into one field for a merge with Word.

The Second table is tblCertsID - which has misc. certifications that do not qualify in our industry as registrations but still need to be listed (the info is completely different from the registrations or i would have them in the same table.

the fields are:

qryEngRegistrationsWTitle - this qry comes from a table which is for employee type 1 only.

Emp_No (primary ID)
RegID
RegFull
RegNumber
ExpDate
IssueDate
IssueAgency

tblCertsID - this table is utlized by two different types of employees 1 and 2.

Emp_No (Primary ID)
CertID
CertFull
CertNumber
CertQualifier (Memo Field)
CertExpDate
CertIssueDate

This table is shared with two different types of employees hence the separation.

I appreciate any assistance that is offered and I will do my best to understand each explanation. I will try to make the SQL query in another file and see what happens.

Thanks for all of your help.





"One of the secrets of life is to make stepping stones out of stumbling blocks." Jack Penn

 

[PHV]

My guess:
JoinedReg1: Concatenate("SELECT CertificationTitle & Chr(58) & Space(2) & StateIssued & Space(1) & Chr(40) & Chr(35) & RegNumber & Chr(44) & Space(1) & 'expires' & Space(1) & RegExpires & Chr(41) & Space(2) & FirstIssued FROM qryEngRegistrationsWTitle WHERE Emp_No=" & [!]tblCertsID.[/!][Emp_No])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[WebGodiva]

Ok, so i've tested the SQL and it works fine as PHV suggested. It gives me each record joined into it's own field. What I need to do now, is get each of those joined into a field so I end up with one field with all of the information in it.

I tried adding the tblCertsID.[Emp_No]) line and i still get the same error. It's weird, If I take out the Concatenate part and just run the SQL it runs fine. Not sure what I'm missing.





"One of the secrets of life is to make stepping stones out of stumbling blocks." Jack Penn

 

[PHV]

Could you please post the SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[WebGodiva]

Here it is:

SELECT qryEngRegistrationsWTitle.Emp_No, Concatenate("SELECT CertificationTitle & Chr(58) & Space(2) & StateIssued & Space(1) & Chr(40) & Chr(35) & RegNumber & Chr(44) & Space(1) & 'expires' & Space(1) & RegExpires & Chr(41) & Space(2) & FirstIssued FROM qryEngRegistrationsWTitle WHERE qryEngRegistrationsWTitle.Emp_No=" & [Emp_No]) AS JoinedReg1
FROM qryEngRegistrationsWTitle;

Gosh, if you were local I'd buy you a beer for all of your help....



"One of the secrets of life is to make stepping stones out of stumbling blocks." Jack Penn

 

[PHV]

And the SQL code of qryEngRegistrationsWTitle ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[WebGodiva]

Sorry, here it is...

SELECT tblEngCertID.CertIDNo, tblEngCertID.CertificationTitle, tblEngCertID.Prim_Id, tblEngRegist.REGPID, tblEngRegist.Emp_No, tblEngRegist.REGID, tblEngRegist.FirstIssued, tblEngRegist.StateIssued, tblEngRegist.RegExpires, tblEngRegist.RegNumber, tblEngRegist.DateModified
FROM tblEngCertID LEFT JOIN tblEngRegist ON tblEngCertID.CertIDNo=tblEngRegist.REGID
WHERE (((tblEngRegist.Emp_No)=100));

Many thanks for all of your time...



"One of the secrets of life is to make stepping stones out of stumbling blocks." Jack Penn

 

[PHV]

So, you may try this:
SELECT tblEngRegist.Emp_No, Concatenate("SELECT CertificationTitle & Chr(58) & Space(2) & StateIssued & Space(1) & Chr(40) & Chr(35) & RegNumber & Chr(44) & Space(1) & 'expires' & Space(1) & RegExpires & Chr(41) & Space(2) & FirstIssued FROM qryEngRegistrationsWTitle WHERE qryEngRegistrationsWTitle.Emp_No=" & tblEngRegist.Emp_No) AS JoinedReg1
FROM tblEngRegist;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[WebGodiva]

nope, still getting the ambiguous name error....i'm about lost for the day....i'm starting to see dots all over the place from staring at this the entire day.

I'm beginning to wonder if there is something wrong with the module i copied from the FAQ. I ran the debug and it was fine but it just doesn't seem to be working - the SQL is good.

I appreciate all of your help...I'm going to have to battle this tomorrow when I can think clearer. I truly do appreciate your help and I will let you know if I figure it out...I just can't look at it any more today. I don't know how you do it and continue to help others.

Thanks bunches and have a great night...I will live to fight this another day.




"One of the secrets of life is to make stepping stones out of stumbling blocks." Jack Penn

 

[dhookom]

Try just the function in the immediate window. Press Ctrl+G and type:

?Concatenate("SELECT CertificationTitle & ' expires '  & RegExpires FROM qryEngRegistrationsWTitle WHERE qryEngRegistrationsWTitle.Emp_No=xxx")

where xxx is a legitimate Emp_No (the field is numeric isn't it).

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[WebGodiva]

that works - it then asks for the employee number in a parameter box.

I'm going to start from square one this morning with copying over your block of code, compile, debug and re-write the select query to see if i can figure it out - i've been over it so many times, it just doesn't make sense.

I've been able to concatenate the fields in one record just fine. What i need to do is concatenate the records for one employee into one field. I know it's possible, just got to make it work - maybe starting with a clear head will help.

Many thanks for all of your attempts and help..i've learned much here and hopefully i can make it work.





"One of the secrets of life is to make stepping stones out of stumbling blocks." Jack Penn