Run time error 3075

[maheumann]

Salutations,

This is my first time posting, so I'm sorry if I don't explain the problem
throughly the first time or if I posted this correctly.

About me, I've been coding in VBA for about 2 years, still learning and
trying out different things.

The code that I'm trying to use right now is in access and I keep getting a
Run-time error '3075

The code:

Dim rst As Recordset, StrSQL As String
StrSQL = "SELECT tblDepotCfgIBPDCs.* FROM tblDepotCfgIBPDCs;"
Set rst = CurrentDb.OpenRecordset(StrSQL)
If rst.RecordCount > 0 Then
rst.MoveFirst
Do Until rst.EOF
ResetTableLinkDSN rst!customdsn

DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO [I8000 and I8100 code usage] (
Warehouse, Logdate, Laborcode, Hours )SELECT DV_HOST.WAREHOUSEID,
DV_USEREDITVIEW.LOGDATE, DV_USEREDITVIEW.LABORCODEBK, Sum(DV_USEREDITVIEW.
DECIMALHOURS) AS SumOfDECIMALHOURS FROM DV_USEREDITVIEW, DV_HOST GROUP BY
DV_HOST.WAREHOUSEID, DV_USEREDITVIEW.LOGDATE, DV_USEREDITVIEW.LABORCODEBK,
DV_USEREDITVIEW.REPORTLABORTOHOSTFLAG HAVING (((DV_USEREDITVIEW.LOGDATE)
Between #3/3/2008# And #3/30/2008#) AND ((DV_USEREDITVIEW.LABORCODEBK) Like
*I8000) AND ((DV_USEREDITVIEW.REPORTLABORTOHOSTFLAG)=1)) OR ((
(DV_USEREDITVIEW.LOGDATE) Between #3/3/2008# And #3/30/2008#) AND (
(DV_USEREDITVIEW.LABORCODEBK) Like *I8100) AND ((DV_USEREDITVIEW.
REPORTLABORTOHOSTFLAG)=1));"


DoCmd.SetWarnings True
rst.MoveNext
Loop
End If

End Sub

My guess is that the problem is after the HAVING portion of the VBA. This is
looping through our PDC's and selecting from them the required data in the
required ranges.

Apologies if I haven't been clear enough, please let me know if there is any
additional information that I can give to make the problem clearer.

Thanks in advance.
MH

 

[Allen Browne]

The 2 cases where you used the LIKE operator need quotes around the values.

Ignoring the line wraps:

Dim strSql As String
strSql = "INSERT INTO [I8000 and I8100 code usage]
(Warehouse, Logdate, Laborcode, Hours )
SELECT DV_HOST.WAREHOUSEID,
DV_USEREDITVIEW.LOGDATE,
DV_USEREDITVIEW.LABORCODEBK,
Sum(DV_USEREDITVIEW.DECIMALHOURS) AS SumOfDECIMALHOURS
FROM DV_USEREDITVIEW, DV_HOST
GROUP BY DV_HOST.WAREHOUSEID,
DV_USEREDITVIEW.LOGDATE,
DV_USEREDITVIEW.LABORCODEBK,
DV_USEREDITVIEW.REPORTLABORTOHOSTFLAG
HAVING ((DV_USEREDITVIEW.LOGDATE Between #3/3/2008# And #3/30/2008#)
AND (DV_USEREDITVIEW.LABORCODEBK Like ""*I8000"")
AND (DV_USEREDITVIEW.REPORTLABORTOHOSTFLAG = 1))
OR ((DV_USEREDITVIEW.LOGDATE Between #3/3/2008# And #3/30/2008#)
AND (DV_USEREDITVIEW.LABORCODEBK Like ""*I8100"")
AND (DV_USEREDITVIEW.REPORTLABORTOHOSTFLAG = 1));"
Debug.Print strSql

If it still fails, open the Immediate Window (Ctrl+G) and see what came out
there. Compare it to what you see in the SQL View of a query.

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Salutations,

This is my first time posting, so I'm sorry if I don't explain the problem
throughly the first time or if I posted this correctly.

About me, I've been coding in VBA for about 2 years, still learning and
trying out different things.

The code that I'm trying to use right now is in access and I keep getting
a
Run-time error '3075

The code:

Dim rst As Recordset, StrSQL As String
StrSQL = "SELECT tblDepotCfgIBPDCs.* FROM tblDepotCfgIBPDCs;"
Set rst = CurrentDb.OpenRecordset(StrSQL)
If rst.RecordCount > 0 Then
rst.MoveFirst
Do Until rst.EOF
ResetTableLinkDSN rst!customdsn

DoCmd.SetWarnings False

DoCmd.RunSQL "INSERT INTO [I8000 and I8100 code usage] (
Warehouse, Logdate, Laborcode, Hours )SELECT DV_HOST.WAREHOUSEID,
DV_USEREDITVIEW.LOGDATE, DV_USEREDITVIEW.LABORCODEBK, Sum(DV_USEREDITVIEW.
DECIMALHOURS) AS SumOfDECIMALHOURS FROM DV_USEREDITVIEW, DV_HOST GROUP BY
DV_HOST.WAREHOUSEID, DV_USEREDITVIEW.LOGDATE, DV_USEREDITVIEW.LABORCODEBK,
DV_USEREDITVIEW.REPORTLABORTOHOSTFLAG HAVING (((DV_USEREDITVIEW.LOGDATE)
Between #3/3/2008# And #3/30/2008#) AND ((DV_USEREDITVIEW.LABORCODEBK)
Like
*I8000) AND ((DV_USEREDITVIEW.REPORTLABORTOHOSTFLAG)=1)) OR ((
(DV_USEREDITVIEW.LOGDATE) Between #3/3/2008# And #3/30/2008#) AND (
(DV_USEREDITVIEW.LABORCODEBK) Like *I8100) AND ((DV_USEREDITVIEW.
REPORTLABORTOHOSTFLAG)=1));"


DoCmd.SetWarnings True
rst.MoveNext
Loop
End If

End Sub

My guess is that the problem is after the HAVING portion of the VBA. This
is
looping through our PDC's and selecting from them the required data in the
required ranges.

Apologies if I haven't been clear enough, please let me know if there is
any
additional information that I can give to make the problem clearer.

Thanks in advance.
MH

 

[maheumann via AccessMonster.com]

Thank you so much works like a charm.

Also thank you for the link to the quotes when I get a chance I will read up
on it.

Have a great day.

The 2 cases where you used the LIKE operator need quotes around the values.

Ignoring the line wraps:

Dim strSql As String
strSql = "INSERT INTO [I8000 and I8100 code usage]
(Warehouse, Logdate, Laborcode, Hours )
SELECT DV_HOST.WAREHOUSEID,
DV_USEREDITVIEW.LOGDATE,
DV_USEREDITVIEW.LABORCODEBK,
Sum(DV_USEREDITVIEW.DECIMALHOURS) AS SumOfDECIMALHOURS
FROM DV_USEREDITVIEW, DV_HOST
GROUP BY DV_HOST.WAREHOUSEID,
DV_USEREDITVIEW.LOGDATE,
DV_USEREDITVIEW.LABORCODEBK,
DV_USEREDITVIEW.REPORTLABORTOHOSTFLAG
HAVING ((DV_USEREDITVIEW.LOGDATE Between #3/3/2008# And #3/30/2008#)
AND (DV_USEREDITVIEW.LABORCODEBK Like ""*I8000"")
AND (DV_USEREDITVIEW.REPORTLABORTOHOSTFLAG = 1))
OR ((DV_USEREDITVIEW.LOGDATE Between #3/3/2008# And #3/30/2008#)
AND (DV_USEREDITVIEW.LABORCODEBK Like ""*I8100"")
AND (DV_USEREDITVIEW.REPORTLABORTOHOSTFLAG = 1));"
Debug.Print strSql

If it still fails, open the Immediate Window (Ctrl+G) and see what came out
there. Compare it to what you see in the SQL View of a query.

If those quotes don't make sense, see:
Quotation marks within quotes
at:
http://allenbrowne.com/casu-17.html

Salutations,

[quoted text clipped - 51 lines]

Thanks in advance.
MH