×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Using a constant in a join statement

Using a constant in a join statement

Using a constant in a join statement

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

CODE

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 "Parm_Type = 'States'" part of the join. How can I use a constant in a join?

TIA Mark

RE: Using a constant in a join statement

By "a constant" do you mean:

CODE

Private Const XYZ 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 = " & XYZ 


---- Andy

There is a great need for a sarcasm font.

RE: Using a constant in a join statement

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

CODE --> SQL

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

RE: Using a constant in a join statement

(OP)
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

RE: Using a constant in a join statement

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

RE: Using a constant in a join statement

(OP)
I spoke too soon. Adding the WHERE Parm_Type = 'States' gives me a "Syntax error in query expression..."



RE: Using a constant in a join statement

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

CODE --> sql

WHERE Parm_Type = "States" 

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

RE: Using a constant in a join statement

(OP)
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:

CODE

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

RE: Using a constant in a join statement

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

CODE --> SQL

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

RE: Using a constant in a join statement

(OP)
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.

CODE

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.

CODE

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

RE: Using a constant in a join statement

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

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

RE: Using a constant in a join statement

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

CODE

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

I'll have Heineken smile


---- Andy

There is a great need for a sarcasm font.

RE: Using a constant in a join statement

(OP)
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:

CODE

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:

CODE

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 bigsmile
Mark

RE: Using a constant in a join statement

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

RE: Using a constant in a join statement

Then, how about:

CODE

SELECT Parm_Type, Parm_Code, Parm_Description
FROM [Parameters]
WHERE ([Parameters[.Parm_Type='Relationship') AND ([Parameters[.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 smile


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close