Database Design - Query

S

Squid

What is wrong with this procedure? I get an syntax error.

Private Sub cboFunds_AfterUpdate()
txtFundLocal.Value = & _
"SELECT tblLocalUnion.LocalUnionID, tblRelationshipType.RelTypeID,
tblFundLocal.FundLocalID" & _
"FROM tblLocalUnion INNER JOIN(tblFundOffice INNER JOIN (tblRelationshipType
INNER JOIN tblFundLocal" & _
"ON tblRelationshipType.RelTypeID = tblFundLocal.RelTypeID)" & _
"ON tblFundOffice.FundOfficeID = tblFundLocal.FundOfficeID)" & _
"ON tblLocalUnion.LocalUnionID = tblFundLocal.LocalUnionID" & _
"WHERE (((tblLocalUnion.LocalUnionID)=[Forms]![frmEntry2]![cboLU])" & _
"AND ((tblRelationshipType.RelTypeID)=[Forms]![frmEntry2]![cboFunds]));"

End Sub
 
S

Squid

Procedure should be this: I am trying to populate the FundLocal text box
with the value that results from the below query. I made the query in
design view then swiched to SQL view to get the below SQL.

Private Sub cboFunds_AfterUpdate()
txtFundLocal.Value = & _
"SELECT tblFundLocal.FundLocalID" & _
"FROM tblLocalUnion INNER JOIN(tblFundOffice INNER JOIN
(tblRelationshipType INNER JOIN tblFundLocal" & _
"ON tblRelationshipType.RelTypeID = tblFundLocal.RelTypeID)" & _
"ON tblFundOffice.FundOfficeID = tblFundLocal.FundOfficeID)" & _
"ON tblLocalUnion.LocalUnionID = tblFundLocal.LocalUnionID" & _
"WHERE (((tblLocalUnion.LocalUnionID)=[Forms]![frmEntry2]![cboLU])" & _
"AND ((tblRelationshipType.RelTypeID)=[Forms]![frmEntry2]![cboFunds]));"

End Sub
Click to expand...

Squid said:
What is wrong with this procedure? I get an syntax error.

Private Sub cboFunds_AfterUpdate()
txtFundLocal.Value = & _
"SELECT tblLocalUnion.LocalUnionID, tblRelationshipType.RelTypeID,
tblFundLocal.FundLocalID" & _
"FROM tblLocalUnion INNER JOIN(tblFundOffice INNER JOIN
(tblRelationshipType INNER JOIN tblFundLocal" & _
"ON tblRelationshipType.RelTypeID = tblFundLocal.RelTypeID)" & _
"ON tblFundOffice.FundOfficeID = tblFundLocal.FundOfficeID)" & _
"ON tblLocalUnion.LocalUnionID = tblFundLocal.LocalUnionID" & _
"WHERE (((tblLocalUnion.LocalUnionID)=[Forms]![frmEntry2]![cboLU])" & _
"AND ((tblRelationshipType.RelTypeID)=[Forms]![frmEntry2]![cboFunds]));"

End Sub
Click to expand...
 
D

Dirk Goldgar

Squid said:
What is wrong with this procedure? I get an syntax error.

Private Sub cboFunds_AfterUpdate()
txtFundLocal.Value = & _
"SELECT tblLocalUnion.LocalUnionID, tblRelationshipType.RelTypeID,
tblFundLocal.FundLocalID" & _
"FROM tblLocalUnion INNER JOIN(tblFundOffice INNER JOIN
(tblRelationshipType INNER JOIN tblFundLocal" & _
"ON tblRelationshipType.RelTypeID = tblFundLocal.RelTypeID)" & _
"ON tblFundOffice.FundOfficeID = tblFundLocal.FundOfficeID)" & _
"ON tblLocalUnion.LocalUnionID = tblFundLocal.LocalUnionID" & _
"WHERE (((tblLocalUnion.LocalUnionID)=[Forms]![frmEntry2]![cboLU])" &
_ "AND
((tblRelationshipType.RelTypeID)=[Forms]![frmEntry2]![cboFunds]));"

End Sub
Click to expand...

You're getting a syntax error because of the initial "&" after the "="
sign. Instead of this ...
txtFundLocal.Value = & _
Click to expand...

.... this would be legal syntax:

txtFundLocal.Value = _

You are aware, I hope, that this is just going to assign that SQL string
as a text value to to txtFundLocal? That it's not actually going to
execute that query and return a result value?
 
D

Dirk Goldgar

Squid said:
Procedure should be this: I am trying to populate the FundLocal text
box with the value that results from the below query. I made the
query in design view then swiched to SQL view to get the below SQL.

Private Sub cboFunds_AfterUpdate()
txtFundLocal.Value = & _
"SELECT tblFundLocal.FundLocalID" & _
"FROM tblLocalUnion INNER JOIN(tblFundOffice INNER JOIN
(tblRelationshipType INNER JOIN tblFundLocal" & _
"ON tblRelationshipType.RelTypeID = tblFundLocal.RelTypeID)" & _
"ON tblFundOffice.FundOfficeID = tblFundLocal.FundOfficeID)" & _
"ON tblLocalUnion.LocalUnionID = tblFundLocal.LocalUnionID" & _
"WHERE (((tblLocalUnion.LocalUnionID)=[Forms]![frmEntry2]![cboLU])"
& _ "AND
((tblRelationshipType.RelTypeID)=[Forms]![frmEntry2]![cboFunds]));"

End Sub
Click to expand...
Click to expand...

That won't work as written. Even after you correct the syntax error in
the assignment, and fix some errors in the SQL statement, you still have
to open a recordset on the query. Try this:

'----- start of suggested code -----
Private Sub cboFunds_AfterUpdate()

Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = _
"SELECT tblFundLocal.FundLocalID " & _
"FROM tblLocalUnion " & _
"INNER JOIN(tblFundOffice " & _
"INNER JOIN (tblRelationshipType " & _
"INNER JOIN tblFundLocal " & _
"ON tblRelationshipType.RelTypeID=tblFundLocal.RelTypeID) " & _
"ON tblFundOffice.FundOfficeID=tblFundLocal.FundOfficeID) " & _
"ON tblLocalUnion.LocalUnionID=tblFundLocal.LocalUnionID " & _
"WHERE (((tblLocalUnion.LocalUnionID)=" & _
[Forms]![frmEntry2]![cboLU] & _
") AND ((tblRelationshipType.RelTypeID)=" & _
[Forms]![frmEntry2]![cboFunds] & "));"

Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
If .EOF Then
txtFundLocal.Value = Null
Else
txtFundLocal.Value = .Fields(0)
End If
.Close
End With

End Sub
'----- end of suggested code -----
 
S

Squid

I am receiving a run-time error of "data type mismatch in criteria
expression" in the following line of code > Set rs =
CurrentDb.OpenRecordset(strSQL)
I enabled the Microsoft DAO 3.6 Object Library reference. I could
understand the data type mismatch if the query didnt work in the query by
form.. but it did there.



Dirk Goldgar said:
Squid said:
Procedure should be this: I am trying to populate the FundLocal text
box with the value that results from the below query. I made the
query in design view then swiched to SQL view to get the below SQL.

Private Sub cboFunds_AfterUpdate()
txtFundLocal.Value = & _
"SELECT tblFundLocal.FundLocalID" & _
"FROM tblLocalUnion INNER JOIN(tblFundOffice INNER JOIN
(tblRelationshipType INNER JOIN tblFundLocal" & _
"ON tblRelationshipType.RelTypeID = tblFundLocal.RelTypeID)" & _
"ON tblFundOffice.FundOfficeID = tblFundLocal.FundOfficeID)" & _
"ON tblLocalUnion.LocalUnionID = tblFundLocal.LocalUnionID" & _
"WHERE (((tblLocalUnion.LocalUnionID)=[Forms]![frmEntry2]![cboLU])"
& _ "AND
((tblRelationshipType.RelTypeID)=[Forms]![frmEntry2]![cboFunds]));"

End Sub
Click to expand...
Click to expand...

That won't work as written. Even after you correct the syntax error in
the assignment, and fix some errors in the SQL statement, you still have
to open a recordset on the query. Try this:

'----- start of suggested code -----
Private Sub cboFunds_AfterUpdate()

Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = _
"SELECT tblFundLocal.FundLocalID " & _
"FROM tblLocalUnion " & _
"INNER JOIN(tblFundOffice " & _
"INNER JOIN (tblRelationshipType " & _
"INNER JOIN tblFundLocal " & _
"ON tblRelationshipType.RelTypeID=tblFundLocal.RelTypeID) " & _
"ON tblFundOffice.FundOfficeID=tblFundLocal.FundOfficeID) " & _
"ON tblLocalUnion.LocalUnionID=tblFundLocal.LocalUnionID " & _
"WHERE (((tblLocalUnion.LocalUnionID)=" & _
[Forms]![frmEntry2]![cboLU] & _
") AND ((tblRelationshipType.RelTypeID)=" & _
[Forms]![frmEntry2]![cboFunds] & "));"

Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
If .EOF Then
txtFundLocal.Value = Null
Else
txtFundLocal.Value = .Fields(0)
End If
.Close
End With

End Sub
'----- end of suggested code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Squid said:
I am receiving a run-time error of "data type mismatch in criteria
expression" in the following line of code > Set rs =
CurrentDb.OpenRecordset(strSQL)
I enabled the Microsoft DAO 3.6 Object Library reference. I could
understand the data type mismatch if the query didnt work in the
query by form.. but it did there.
Click to expand...

The way I'm embedding the values of the form controls assumes that both
tblLocalUnion.LocalUnionID and tblRelationshipType.RelTypeID are numeric
fields. If they are text fields, quotes must be embedded into the SQL
string surrounding the values that are coming from the form controls.
That isn't necessary when Access runs the query, because Access knows
how to resolve the control references. However, it's necessary when
using DAO to run the query, because DAO doesn't know about the controls
so we have to embed literal values in the SQL statement.

Which of these fields is a text field?
 
D

Dirk Goldgar

Squid said:
LocalUnionID is text and RelTypeID is numeric (long integer)
Click to expand...

Okay, then build the SQL statement like this:

strSQL = _
"SELECT tblFundLocal.FundLocalID " & _
"FROM tblLocalUnion " & _
"INNER JOIN(tblFundOffice " & _
"INNER JOIN (tblRelationshipType " & _
"INNER JOIN tblFundLocal " & _
"ON tblRelationshipType.RelTypeID=tblFundLocal.RelTypeID) " & _
"ON tblFundOffice.FundOfficeID=tblFundLocal.FundOfficeID) " & _
"ON tblLocalUnion.LocalUnionID=tblFundLocal.LocalUnionID " & _
"WHERE (((tblLocalUnion.LocalUnionID)=" & _
Chr(34) & [Forms]![frmEntry2]![cboLU] & Chr(34) & _
") AND ((tblRelationshipType.RelTypeID)=" & _
[Forms]![frmEntry2]![cboFunds] & "));"
 
M

Mike

Thank you very much.

If in the future I am writing a SQL statement which requires retrieving a
date from a control... would I need to add '#' like we needed for the
string?



Dirk Goldgar said:
Squid said:
LocalUnionID is text and RelTypeID is numeric (long integer)
Click to expand...

Okay, then build the SQL statement like this:

strSQL = _
"SELECT tblFundLocal.FundLocalID " & _
"FROM tblLocalUnion " & _
"INNER JOIN(tblFundOffice " & _
"INNER JOIN (tblRelationshipType " & _
"INNER JOIN tblFundLocal " & _
"ON tblRelationshipType.RelTypeID=tblFundLocal.RelTypeID) " & _
"ON tblFundOffice.FundOfficeID=tblFundLocal.FundOfficeID) " & _
"ON tblLocalUnion.LocalUnionID=tblFundLocal.LocalUnionID " & _
"WHERE (((tblLocalUnion.LocalUnionID)=" & _
Chr(34) & [Forms]![frmEntry2]![cboLU] & Chr(34) & _
") AND ((tblRelationshipType.RelTypeID)=" & _
[Forms]![frmEntry2]![cboFunds] & "));"


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Mike said:
Thank you very much.

If in the future I am writing a SQL statement which requires
retrieving a date from a control... would I need to add '#' like we
needed for the string?
Click to expand...

Yes. It's also best if you ensure that the date literal that gets
embedded in the SQL string is either in US standard format --
mm/dd/yyyy -- or in a totally unambiguous format. I recommend something
like ...

" WHERE [DateField] = " & Format([YourDate], "\#mm/dd/yyyy\#")
 
Top