Error on query defs.

[MICKI0220]

I created this code and I get an error at
set qdf = currentdb.querydefs("")

Can anyone tell me what the problem may be. I use a similiar code for another report to alter the sql statement and it works fine.


strsql = "SELECT dbo_VP_TIMESHEETITMV42.PERSONFULLNAME, dbo_VP_TIMESHEETITMV42.PERSONNUM," _
& "dbo_VP_TIMESHEETITMV42.TIMEINSECONDS, HISTORYJOBS.JOB, dbo_LABORLEVELENTRY_JOB.DESCRIPTION," _
& "dbo_VP_TIMESHEETITMV42.LABORLEVELNAME4, dbo_LABORLEVELENTRY_OP.DESCRIPTION, ([timeinseconds]/3600)" _
& "AS Hours, HISTORYJOBS.TEAM, HISTORYJOBS.MONTH" _
& "FROM HISTORYJOBS INNER JOIN ((dbo_VP_TIMESHEETITMV42 INNER JOIN dbo_LABORLEVELENTRY_JOB" _
& "ON dbo_VP_TIMESHEETITMV42.LABORLEVELNAME3 = dbo_LABORLEVELENTRY_JOB.NAME)" _
& "INNER JOIN dbo_LABORLEVELENTRY_OP ON" _
& "dbo_VP_TIMESHEETITMV42.LABORLEVELNAME4 = dbo_LABORLEVELENTRY_OP.NAME)" _
& "ON HISTORYJOBS.JOB = dbo_VP_TIMESHEETITMV42.LABORLEVELNAME3" _
& "WHERE (((HISTORYJOBS.TEAM)=[Forms]![Response]![Text7])" _
& "AND ((HISTORYJOBS.MONTH)=[Forms]![Response]![txtmth]));"

If Trim(strtype & "") <> "" Then
strsql = strsql & "AND history.JOB NOT IN (" & Mid(strtype, 3) & """)"
End If

strsql = strsql
'& Mid(strtype, 3) & """)"

'Check if a query called query1 exists
'If it does not exist, create it.
'If it does exist, permanently change it
If DLookup("Name", "MSysObjects", "Name= 'teamstandardsquery'") <> "" Then
Set qdf = CurrentDb.QueryDefs("")
qdf.SQL = strsql
Else
Set qdf = CurrentDb.CreateQueryDef("teamstandardsquery", strsql)
End If
'Open the query
'DoCmd.OpenQuery "detailbonusbyteam"
stDocName = "team standards detail"
DoCmd.OpenReport stDocName, acPreview

 

[Ed2020]

You need to change the line to:

Set qdf = CurrentDb.QueryDefs("teamstandardsquery")

Please do not feed the trolls.....

 

[MICKI0220]

thanks, but now I get an error on the From clause

run-time error '3131'
syntax error in from clause


I have played with the parens and continuation, but don't know the answer, can you tell me what looks off.

strsql = "SELECT dbo_VP_TIMESHEETITMV42.PERSONFULLNAME, dbo_VP_TIMESHEETITMV42.PERSONNUM, " _
& "dbo_VP_TIMESHEETITMV42.TIMEINSECONDS, HISTORYJOBS.JOB, dbo_LABORLEVELENTRY_JOB.DESCRIPTION, " _
& "dbo_VP_TIMESHEETITMV42.LABORLEVELNAME4, dbo_LABORLEVELENTRY_OP.DESCRIPTION, ([timeinseconds]/3600) " _
& "AS Hours, HISTORYJOBS.TEAM, HISTORYJOBS.MONTH " _
& "FROM (HISTORYJOBS INNER JOIN dbo_VP_TIMESHEETITMV42 " _
& "INNER JOIN dbo_LABORLEVELENTRY_JOB " _
& "ON dbo_VP_TIMESHEETITMV42.LABORLEVELNAME3 = dbo_LABORLEVELENTRY_JOB.NAME) " _
& "INNER JOIN dbo_LABORLEVELENTRY_OP ON " _
& "dbo_VP_TIMESHEETITMV42.LABORLEVELNAME4 = dbo_LABORLEVELENTRY_OP.NAME " _
& "ON HISTORYJOBS.JOB = dbo_VP_TIMESHEETITMV42.LABORLEVELNAME3 " _
& "WHERE (((HISTORYJOBS.TEAM)=[Forms]![Response]![Text7]) " _
& "AND ((HISTORYJOBS.MONTH)=[Forms]![Response]![txtmth]) "

If Trim(strtype & "") <> "" Then
strsql = strsql & "AND history.JOB NOT IN (" & Mid(strtype, 3) & """)"
End If

strsql = strsql
'& Mid(strtype, 3) & """)"

'Check if a query called teamstandardsquery exists
'If it does not exist, create it.
'If it does exist, permanently change it
If DLookup("Name", "MSysObjects", "Name= 'teamstandardsquery'") <> "" Then
Set qdef = CurrentDb.QueryDefs("teamstandardsquery")
qdef.SQL = strsql

 

[bevsz]

strsql = "SELECT dbo_VP_TIMESHEETITMV42.PERSONFULLNAME, dbo_VP_TIMESHEETITMV42.PERSONNUM, " _
& "dbo_VP_TIMESHEETITMV42.TIMEINSECONDS, HISTORYJOBS.JOB, dbo_LABORLEVELENTRY_JOB.DESCRIPTION, " _
& "dbo_VP_TIMESHEETITMV42.LABORLEVELNAME4, dbo_LABORLEVELENTRY_OP.DESCRIPTION, ([timeinseconds]/3600) " _
& "AS Hours, HISTORYJOBS.TEAM, HISTORYJOBS.MONTH " _
& "FROM (HISTORYJOBS INNER JOIN dbo_VP_TIMESHEETITMV42 " _
& "INNER JOIN dbo_LABORLEVELENTRY_JOB " _
& "ON dbo_VP_TIMESHEETITMV42.LABORLEVELNAME3 = dbo_LABORLEVELENTRY_JOB.NAME) " _
& "INNER JOIN dbo_LABORLEVELENTRY_OP ON " _
& "dbo_VP_TIMESHEETITMV42.LABORLEVELNAME4 = dbo_LABORLEVELENTRY_OP.NAME " _
& "ON HISTORYJOBS.JOB = dbo_VP_TIMESHEETITMV42.LABORLEVELNAME3 " _
& "WHERE (((HISTORYJOBS.TEAM)=[Forms]![Response]![Text7]) " _
& "AND ((HISTORYJOBS.MONTH)=[Forms]![Response]![txtmth])) "

Looks to me like you are missing your last closing paramenter.... try this and see if it likes it better.

 

[MICKI0220]

No it didn't work. It still says the same error.