Sql Statement errors

[LeftyLeo via AccessMonster.com]

Hi all,

I know this should be easy but I can not get this sql statement to work. It
gives a compile error at the end of the statement. I am not sure how many
qoutes to place with the semi-colon when have more than 1 lookup variable(?)

Here is the statement:

"SELECT tblMasterTable.PrimaryKey, tblMasterTable.fldDate, tblMasterTable.
fldLocation, tblMasterTable.fldZone, tblMasterTable.fldPeriod, tblMasterTable.
fldYear, tblMasterTable.[Beginning Balance], tblMasterTable.fldOrganization,
tblMasterTable.fldAddress, tblMasterTable.fldDescription, tblMasterTable.[Req
Letter on File], tblMasterTable.[501 (c) 3 Number], tblMasterTable.[Focus
Area], tblMasterTable.[Donation Description], tblMasterTable.fldAmount,
tblMasterTable.fldCity, tblMasterTable.fldState, tblMasterTable.fldZip FROM
tblMasterTable WHERE (tblMasterTable.fldLocation)='" & DLookup("[fldLocID]",
"tblCurrentUserInfo") And ((tblMasterTable.fldYear) ='" & strYear & "';"

What am I doing wrong? Any help would be appreciated.

 

[Paolo]

Hi Lefty,

.....And ((tblMasterTable.fldYear) ='" & strYear & "';"
just one parenthesis before tblmastertable.fldyear.................

HTH Paolo

 

[Paolo]

I forgot something before.

WHERE (tblMasterTable.fldLocation)='" & DLookup("[fldLocID]",
"tblCurrentUserInfo") & "' And (tblMasterTable.fldYear) ='" & strYear & "'"

That should be correct.
Be careful, if you have more than one record in tblcurrentuserinfo you must
put a condition also on the dlookup function otherwise the function will
return you the first available fldlocid.

Cheers

Hi Lefty,

....And ((tblMasterTable.fldYear) ='" & strYear & "';"
just one parenthesis before tblmastertable.fldyear.................

HTH Paolo

Hi all,

I know this should be easy but I can not get this sql statement to work. It
gives a compile error at the end of the statement. I am not sure how many
qoutes to place with the semi-colon when have more than 1 lookup variable(?)

Here is the statement:

"SELECT tblMasterTable.PrimaryKey, tblMasterTable.fldDate, tblMasterTable.
fldLocation, tblMasterTable.fldZone, tblMasterTable.fldPeriod, tblMasterTable.
fldYear, tblMasterTable.[Beginning Balance], tblMasterTable.fldOrganization,
tblMasterTable.fldAddress, tblMasterTable.fldDescription, tblMasterTable.[Req
Letter on File], tblMasterTable.[501 (c) 3 Number], tblMasterTable.[Focus
Area], tblMasterTable.[Donation Description], tblMasterTable.fldAmount,
tblMasterTable.fldCity, tblMasterTable.fldState, tblMasterTable.fldZip FROM
tblMasterTable WHERE (tblMasterTable.fldLocation)='" & DLookup("[fldLocID]",
"tblCurrentUserInfo") And ((tblMasterTable.fldYear) ='" & strYear & "';"

What am I doing wrong? Any help would be appreciated.

 

[Douglas J. Steele]

Actually, the semi-colon is always optional in Access queries, so you can
safely ignore it.

 

[LeftyLeo via AccessMonster.com]

I forgot something before.

WHERE (tblMasterTable.fldLocation)='" & DLookup("[fldLocID]",
"tblCurrentUserInfo") & "' And (tblMasterTable.fldYear) ='" & strYear & "'"

That should be correct.
Be careful, if you have more than one record in tblcurrentuserinfo you must
put a condition also on the dlookup function otherwise the function will
return you the first available fldlocid.

Cheers

Hi Lefty,

[quoted text clipped - 22 lines]

Thanks for your responses Paolo and Doug,

I am still getting errors. [fldLocID] is really a text field could that be
the problem?

 

[LeftyLeo via AccessMonster.com]

I forgot something before.

[quoted text clipped - 13 lines]
Thanks for your responses Paolo and Doug,

I am still getting errors. [fldLocID] is really a text field could that be
the problem?

I am getting the Runtime 424 object needed error.

 

[John Spencer]

"SELECT tblMasterTable.PrimaryKey, tblMasterTable.fldDate
, tblMasterTable.fldLocation, tblMasterTable.fldZone,
tblMasterTable.fldPeriod,
tblMasterTable.fldYear, tblMasterTable.[Beginning Balance],
tblMasterTable.fldOrganization,
tblMasterTable.fldAddress, tblMasterTable.fldDescription,
tblMasterTable.[Req Letter on File], tblMasterTable.[501 (c) 3 Number]
, tblMasterTable.[Focus Area], tblMasterTable.[Donation Description],
tblMasterTable.fldAmount,
tblMasterTable.fldCity, tblMasterTable.fldState, tblMasterTable.fldZip
FROM tblMasterTable
WHERE tblMasterTable.fldLocation='" &
DLookup("[fldLocID]","tblCurrentUserInfo") & "' And tblMasterTable.fldYear
='" & strYear & "'"

Assumes that fldLocation is a text field and that fldYear is a text field.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Douglas J. Steele]

I forgot something before.

[quoted text clipped - 13 lines]

What am I doing wrong? Any help would be appreciated.

Thanks for your responses Paolo and Doug,

I am still getting errors. [fldLocID] is really a text field could that
be
the problem?

I am getting the Runtime 424 object needed error.

What are you doing with the SQL statement when the error arises?

 

[LeftyLeo via AccessMonster.com]

I forgot something before.
[quoted text clipped - 8 lines]
the problem?

I am getting the Runtime 424 object needed error.

What are you doing with the SQL statement when the error arises?

I have actually changed the code to this because I want to delete certain
records from the mastertable:

Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Access.OLEDB.10.0"
.Properties("Data Provider").Value = "SQLOLEDB"
.Properties("Data Source").Value = "SQL16"
.Properties("Initial Catalog").Value = "DatabaseName"
.Properties("User Id").Value = "adminid"
.Properties("Password").Value = "adminpw"
.Open
End With

strSql = "DELETE tblMasterTable.PrimaryKey, tblMasterTable.fldLocation,
tblMasterTable.fldZone, tblMasterTable.fldPeriod, tblMasterTable.fldYear,
tblMasterTable.[Beginning Balance], tblMasterTable.fldOrganization,
tblMasterTable.fldAddress, tblMasterTable.fldDescription, tblMasterTable.[Req
Letter on File], tblMasterTable.[501 (c) 3 Number], tblMasterTable.[Focus
Area], tblMasterTable.[Donation Description], tblMasterTable.fldAmount,
tblMasterTable.fldCity, tblMasterTable.fldState, tblMasterTable.fldZip FROM
tblMasterTable WHERE tblMasterTable.fldLocation='" & DLookup("[fldLocId]",
"tblCurrentUserInfo") & "' And tblMasterTable.fldYear ='" & strYear & "" ';""
cnn.Execute strSql

 

[Douglas J. Steele]

Your closing quotes are wrong. As well, since you're deleting, there's no
reason to enumerate the fields: Delete deletes the entire row, not just
specific fields.

That means all you need is

strSql = "DELETE FROM tblMasterTable " & _
"WHERE tblMasterTable.fldLocation='" & _
DLookup("[fldLocId]","tblCurrentUserInfo") & _
"' And tblMasterTable.fldYear ='" & strYear & "';"

This assumes fldYear is text. If it's a numeric field (as would probably
make more sense, that last line should be

"' And tblMasterTable.fldYear =" & strYear & ";"

or, since the semi-colon is optional in Access,

"' And tblMasterTable.fldYear =" & strYear

 

[LeftyLeo via AccessMonster.com]

Your closing quotes are wrong. As well, since you're deleting, there's no
reason to enumerate the fields: Delete deletes the entire row, not just
specific fields.

That means all you need is

strSql = "DELETE FROM tblMasterTable " & _
"WHERE tblMasterTable.fldLocation='" & _
DLookup("[fldLocId]","tblCurrentUserInfo") & _
"' And tblMasterTable.fldYear ='" & strYear & "';"

This assumes fldYear is text. If it's a numeric field (as would probably
make more sense, that last line should be

"' And tblMasterTable.fldYear =" & strYear & ";"

or, since the semi-colon is optional in Access,

"' And tblMasterTable.fldYear =" & strYear

I have actually changed the code to this because I want to delete certain
records from the mastertable:

[quoted text clipped - 23 lines]

';""
cnn.Execute strSql

Thanks so much Doug it works....perfectly (sp)!