Using a constant in a join statement

[BugZap13]

I am trying to get a value from a parameter table which contains many types of parameters differentiated by Parm_Type field.

Select Members.StateCode, ParmTable.Parm_Desc 
From Members 
Left Join ParmTable on Parm_Code = Members.StateCode AND Parm_Type = 'States'

Access is barking about the "[red]Parm_Type = 'States'[/red]" part of the join. How can I use a constant in a join?

TIA Mark

 

[Andrzejek]

By "a constant" do you mean:

Private Const [blue]XYZ[/blue] As String = "'States'"
Dim strSQL As String

strSQL = "Select Members.StateCode, ParmTable.Parm_Desc " & _
    " From Members " & _
" Left Join ParmTable on Parm_Code = Members.StateCode " & _
" AND ParmTable.Parm_Type = " & [blue]XYZ[/blue]

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I don't think you can use a constant in Access SQL like that. You may be stuck using

Select Members.StateCode, ParmTable.Parm_Desc 
From Members 
Left Join ParmTable on Parm_Code = Members.StateCode 
WHERE Parm_Type = 'States'

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[BugZap13]

Andy, this is being used in the query builder not in code.

Duane, I am sure that will work. I am used to MS SQL and this threw me and I assumed there was an easy way that I was not picking up on.

Thanx, Mark

 

[dhookom]

It threw me also since I am used to working with MS SQL Server. There are lots of MS SQL functionalities I would like to see in MS Access queries but I'm not holding my breath.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[BugZap13]

I spoke too soon. Adding the [red]WHERE Parm_Type = 'States'[/red] gives me a "Syntax error in query expression..."

 

[dhookom]

Keep in mind this is MS Access and not SQL Server. Try replace the single with double-quotes.

WHERE Parm_Type = [b][highlight #FCE94F]"[/highlight][/b]States[b][highlight #FCE94F]"[/highlight][/b]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[BugZap13]

Single vs. double quotes made no difference. I use single quotes in other parts of queries without a problem, but I did give it a try. Does Access allow for a subquery as in:

Select Members.StateCode, 
(SELECT Parm_Desc FROM Parmtable WHERE Parm_Type='States' AND Parm_Code=Members.StateCode) AS StateDesc
From Members

I tried it and it gives me a syntax error in the subquery. I had to manually enter it in the SQL view of the query builder.

Mark

 

[dhookom]

This worked for me in the Northwind database. Can we assume the combination of Parm_Type and Parm_Code is unique?

SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, 
(SELECT FirstName FROM Employees E WHERE E.EmployeeID = Orders.EmployeeID AND E.Country = 'USA') AS EmpFirstName
FROM Orders;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[BugZap13]

In an attempt to simplify my question I changed the table names and so on so the query made more sense. The underlining problem turned out to be the use of the reserved word "Parameters". In my examples above the actual table name is "Parameters", not "ParmTable" as shown in my examples. Had I not changed the table name you may have picked up on the error right away.

The following code gives the Syntax error.

SELECT Parameters.Parm_Type, Parameters.Parm_Code, Parameters.Parm_Description
FROM [Parameters]
WHERE (((Parameters.Parm_Type)='Relationship') AND ((Parameters.Parm_Code)='C'));

The following code runs as expected.

SELECT Parameters_1.Parm_Type, Parameters_1.Parm_Code, Parameters_1.Parm_Description
FROM [Parameters] AS Parameters_1
WHERE (((Parameters_1.Parm_Type)='Relationship') AND ((Parameters_1.Parm_Code)='C'));

Sorry to have run you guys around the bush.

Thanx,
Mark

 

[dhookom]

That's okay you just owe Andy and me a virtual beverage

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Out of curiosity....
wouldn't this work just fine since you deal with just one table:

SELECT Parm_Type, Parm_Code, Parm_Description
FROM [Parameters]
WHERE Parm_Type = 'Relationship' AND Parm_Code = 'C';

I'll have Heineken


---- Andy

There is a great need for a sarcasm font.

 

[BugZap13]

Andy your query works fine. So I started playing around with the query now that I am done with the task at hand and get more confused as I go. So I throw a twist at you again.

The following works:

SELECT Parm_Type, Parm_Code, Parm_Description
FROM [Parameters]
WHERE Parameters.Parm_Type='Relationship' AND Parameters.Parm_Code='C';

The following gives a syntax error:

SELECT Parm_Type, Parm_Code, Parm_Description
FROM [Parameters]
WHERE (Parameters.Parm_Type='Relationship') AND (Parameters.Parm_Code='C');

Only difference being the WHERE clause with parenthesis. Is this a bug in the access parsing? Is it getting confused with the "Parameters" you can specify by right clicking in the query designer?

It is no wonder I have no hair
Mark

 

[dhookom]

That's one of the best reasons for using a naming convention: Avoiding reserved words. Allen Browne has a huge list.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Then, how about:

SELECT Parm_Type, Parm_Code, Parm_Description
FROM [Parameters]
WHERE ([highlight #FCE94F][[/highlight]Parameters[highlight #FCE94F][[/highlight].Parm_Type='Relationship') AND ([highlight #FCE94F][[/highlight]Parameters[highlight #FCE94F][[/highlight].Parm_Code='C');

Myself, I hate to use a table name all over the Select statement whet I deal with just 1 table.
With multiple tables, I usually use short aliases.

I am with you Duane


---- Andy

There is a great need for a sarcasm font.