SELECT a "TWO WORD" field

[doci4a]

Hi guys,

I have an Oracle DB and I am trying to run a simple SELECT query.
The problem is that the field I want to select is named "REPORTER NAME", so two words obviously...


I've tried quite a few different ways to do it but I still get a syntax error.

So how should this look like?

SELECT TABLE1.REPORTER NAME


Thx

[Rixxe]

select [table1].[Reporter name]

You can enclose all names with spaces using [ ], but most names should just be together like ReporterName or just 1 word.

[doci4a]

That didn't do it either...still opens the pop up window in Access, asking for a parameter value for TABLE1.REPORTERNAME (I tried to put the words together). Otherwise I get the same pop up with REPORTER NAME instead...
Here's what I've already used...

[PUBLIC_VW_HR_JOB_POSITION].[REPORTERNAME]

[PUBLIC_VW_HR_JOB_POSITION].[REPORTER NAME]

[PUBLIC_VW_HR_JOB_POSITION].['REPORTER' 'NAME']

[PUBLIC_VW_HR_JOB_POSITION].["REPORTER" "NAME"]

[PUBLIC_VW_HR_JOB_POSITION].['REPORTER' || 'NAME']

[Rixxe]

If the field in the table is called: REPORTER NAME
Then it should just be enclosed like : [REPORTER NAME]
Also make sure the table name is spelt correctly and that the field name is in caps (Not sure if Access is Case Sensitive)
Post the whole SQL statement if you can't get it to work.

[doci4a]

Table name is correct, Access is not case sensitive but I wrote it properly anyway. The field name is REPORTER NAME

Here is the whole thing. It actually runs like this:

SELECT PUBLIC_HR_ASSOCIATE_DATA.ASSOCIATE_ID, PUBLIC_HR_ASSOCIATE_DATA.NAME_FIRSTNAME, PUBLIC_HR_ASSOCIATE_DATA.HIRING_DATE, PUBLIC_HR_ASSOCIATE_DATA.TRIAL_PERIOD, PUBLIC_HR_ASSOCIATE_DATA.DEPARTMENT, IIf([trial_period]<>0,CalcEvalDate(1,[Hiring_Date],[trial_period]),"trial period is over") AS FIRST_EVALUATION, IIf([trial_period]=12,CalcEvalDate(2,[Hiring_Date],[trial_period]),"") AS SECOND_EVALUATION, IIf([trial_period]<>0,CalcEvalDate(3,[Hiring_Date],[trial_period]),"") AS THIRD_EVALUATION
FROM PUBLIC_HR_ASSOCIATE_DATA;

Now, what I want to have is:

SELECT PUBLIC_VW_JOB_POSITION.REPORTER NAME,
PUBLIC_HR_ASSOCIATE_DATA.ASSOCIATE_ID, PUBLIC_HR_ASSOCIATE_DATA.NAME_FIRSTNAME, PUBLIC_HR_ASSOCIATE_DATA.HIRING_DATE, PUBLIC_HR_ASSOCIATE_DATA.TRIAL_PERIOD, PUBLIC_HR_ASSOCIATE_DATA.DEPARTMENT, IIf([trial_period]<>0,CalcEvalDate(1,[Hiring_Date],[trial_period]),"trial period is over") AS FIRST_EVALUATION, IIf([trial_period]=12,CalcEvalDate(2,[Hiring_Date],[trial_period]),"") AS SECOND_EVALUATION, IIf([trial_period]<>0,CalcEvalDate(3,[Hiring_Date],[trial_period]),"") AS THIRD_EVALUATION
FROM PUBLIC_HR_ASSOCIATE_DATA;

[Rixxe]

SELECT PUBLIC_VW_JOB_POSITION.REPORTER_NAME,
PUBLIC_HR_ASSOCIATE_DATA.ASSOCIATE_ID, PUBLIC_HR_ASSOCIATE_DATA.NAME_FIRSTNAME, PUBLIC_HR_ASSOCIATE_DATA.HIRING_DATE, PUBLIC_HR_ASSOCIATE_DATA.TRIAL_PERIOD, PUBLIC_HR_ASSOCIATE_DATA.DEPARTMENT, IIf([trial_period]<>0,CalcEvalDate(1,[Hiring_Date],[trial_period]),"trial period is over") AS FIRST_EVALUATION, IIf([trial_period]=12,CalcEvalDate(2,[Hiring_Date],[trial_period]),"") AS SECOND_EVALUATION, IIf([trial_period]<>0,CalcEvalDate(3,[Hiring_Date],[trial_period]),"") AS THIRD_EVALUATION
FROM PUBLIC_HR_ASSOCIATE_DATA;


Change the field name to match this REPORTER_NAME

Also maybe i'm blind, but are you joining this table at any point? PUBLIC_VW_JOB_POSITION

[doci4a]

Ooooooooops

[Rixxe]

In that case, Change the field name to match this:
[PUBLIC_VW_JOB_POSITION].[REPORTER NAME]

Regarding the Access DB Relationship manager, IF it still doesnt work, then try and join within the query. and see

[Rixxe]

Ooooooooops

Does this mean you solved the problem?

[doci4a]

Yup, I was so stuck on the syntax I forgot to add the second table in the FROM clause

So that's okay.

But after the join, it turned out I have a field called HIRING_DATE in both tables, which I use to calculate. And since there are two fields with the same name now...it crashes. Still trying to find a way around this.

What I did was to try to add the correct table in front but no results still...

IIf([trial_period]<>0,CalcEvalDate(1,[PUBLIC_HR_ASSOCIATE_DATA].[Hiring_Date],[trial_period]),"trial period is over") AS FIRST_EVALUATION,
IIf([trial_period]=12,CalcEvalDate(2,[PUBLIC_HR_ASSOCIATE_DATA].[Hiring_Date],[trial_period]),"") AS SECOND_EVALUATION,
IIf([trial_period]<>0,CalcEvalDate(3,[PUBLIC_HR_ASSOCIATE_DATA].[Hiring_Date],[trial_period]),"") AS THIRD_EVALUATION

[doci4a]

Problem solved though! Thanks for your help!

[Rixxe]

Nice to see you solved it.