SQL VBA -- INNER JOIN Syntax

[amywolfie]

I am trying to write Jet SQL that will open a form where PK = fk, and
FC_County = "FC". I'm getting a syntax error -- here's the code:
================

Dim StrSQL As String


StrSQL = "SELECT dbo_States.State_pk, dbo_County.State_fk,
dbo_County.FC_County " & _
"FROM dbo_County INNER JOIN dbo_States ON dbo_County.State_fk =
dbo_States.State_pk " & _
"WHERE(dbo_County.FC_County = 'FC') AND (dbo_States.State_pk =
dbo_County.State_fk);"


DoCmd.OpenForm "subfrmCounty", , , StrSQL

=====

Thanks.

 

[John W. Vinson]

I am trying to write Jet SQL that will open a form where PK = fk, and
FC_County = "FC". I'm getting a syntax error -- here's the code:
================

Dim StrSQL As String


StrSQL = "SELECT dbo_States.State_pk, dbo_County.State_fk,
dbo_County.FC_County " & _
"FROM dbo_County INNER JOIN dbo_States ON dbo_County.State_fk =
dbo_States.State_pk " & _
"WHERE(dbo_County.FC_County = 'FC') AND (dbo_States.State_pk =
dbo_County.State_fk);"


DoCmd.OpenForm "subfrmCounty", , , StrSQL

=====

Thanks.

If you have the State_fk linked to the State_pk in the JOIN clause, then you
really don't need it in the WHERE clause as well - though it's just redundant
duplication, rather than something which should cause this error. What are the
datatypes of these fields? Is the State_PK a number, or the text abbreviation?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[amywolfie]

Hi John:


PK & fk are standard keys: unique, numeric, not empty, sequential.
When a button is pushed for a given State, I want the related Counties
to appear on another form.
So I can still expunge the FK = pk, since that's the join?

Thanks.
===

 

[amywolfie]

Forgot to mention: FC_County is Text.

The other issue is that all tables are coming from a linked SQL
backend.

Thanks.

===

 

[John W. Vinson]

Hi John:


PK & fk are standard keys: unique, numeric, not empty, sequential.
When a button is pushed for a given State, I want the related Counties
to appear on another form.
So I can still expunge the FK = pk, since that's the join?

If you have the FK=pk in the Join, then no, you do not need it to also appear
in the Where. It's already taken care of.

I think the problem you're having is that you're generating an entire SQL
Query string, and trying to use it in the WhereCondition argument of the
OpenForm method. The fourth argument of OpenForm isn't a complete query;
instead it's a WhereCondition - just the criteria portion of the query without
the word WHERE, e.g.

dbo_County.FC_County = 'FC'

I'm assuming that the form already has a Recordsource.

Hrm. You call it sbfrm - is this in fact a Subform??? If so you don't need to
"open" it at all; a Subform would normally be linked to a Mainform by a
master/child link field (which might be FC_County in this case, or maybe
FC_State) - what are the Recordsources of the mainform and the subform? What
information are you trying to display were?


--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[John Spencer]

I don't think the error is in the SQL statement. I think the error is in the
DoCmd.OpenForm call (one too many commas)

Try
DoCmd.OpenForm "subfrmCounty", , StrSQL

When you get an error, it is a good idea to post the error number and the
error text.

Also, as John Vinson mentioned your SQL string should look like
StrSQL = "SELECT dbo_States.State_pk, dbo_County.State_fk,
dbo_County.FC_County " & _
"FROM dbo_County INNER JOIN dbo_States ON dbo_County.State_fk =
dbo_States.State_pk " & _
"WHERE dbo_County.FC_County = 'FC'"

If you really think the problem is the SQL statement. Add a line to print out
StrSQL in the VBA Immediate window. Then copy the statement into a new query
and see if it works. If it does work then you need to look elsewhere for the
problem.

Dim StrSQL as String
StrSQL = "SELECT ...

Debug.Print StrSQL: STOP 'Temporary line - delete when SQL is working

DoCmd.OpenForm "subFrmCounty",, StrSQL



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[amywolfie]

Thanks, 2 Johns. I worked it out.
===