Code Issues

  • Thread starter Thread starter mtrimpin24
  • Start date Start date
M

mtrimpin24

I was trying to do the following:
1. get the user to import a spreadsheet from some location on the drive
(location changes).

2. data from spreadsheet imports to a table named Random

3. data from Random gets filtered. All records that pass the following
rules are moved to tableAppend:

NO RECORDS that have the text "mileage" "mile" or "mlg" in the ReportName
field should be moved.
1 All records where PostedAmount is above 3500
2 All records with Sap Company Code 010, 528, 113, 185 that have a
PostedAmount above 1500.
3 of the remaining records, every 10th record should be included, as long
as it passes the above rules. If the 10th record doesn't pass, include the
11th. if the 11th doesn't pass, include the 12th, and so on, then continue
with adding the 20th, 30th... etc.

Here's the code. I get multiple errors. i've played with it some, i can't
quite get it to work. its connected to a command button. the current error
is
Run-time error '3075':
Syntax error (missing operator) in query expression '(not (
(postedAmount >= 3500) or ((SAP Company Code in
(010, 528,185,113)) and (postedamount >=1500))))True'.

and if i remove that, i get the error run time error 3061,
with CurrentDb.Execute strSQL




Public Function GetExcel(Optional strStartDir As String) As String

Dim strFilter As String
Dim lngFlags As Long

If strStartDir = "" Then
strStartDir = CurrentProject.Path
End If

strFilter = ahtAddFilterItem(strFilter, "Excel file (*.xls)", "*.xls")

GetExcel = ahtCommonFileOpenSave(InitialDir:=strStartDir, _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Select Excel sheet")


End Function
Sub MyExcelImport()

Dim strFile As String
Dim strSQL As String

CurrentDb.Execute "delete * from tableAppend"
CurrentDb.Execute "delete * from Random"
CurrentDb.Execute "delete * from AuditerTable"


strFile = GetExcel()
If strFile = "" Then
Exit Sub
End If

' import excel file...

DoCmd.TransferSpreadsheet acImport, , "Random", strFile, True

strSQL = "INSERT INTO TableAppend " & _
" ([postedamount], [reportname],[SAP Company Code])" & _
" select [postedamount], [reportname], [SAP Company Code]" & _
" from Random " & _
" where (not ( (postedAmount >= 3500) or " & _
" ( (SAP Company Code in (010,528,185,113)) and (postedamount >= 1500) ))
)" _
& (Reportname <> "mile" And Reportname <> "mileage" And Reportname <> _
"mlg")

CurrentDb.Execute strSQL
Dim rstAppend As DAO.Recordset
Dim rstRandom As DAO.Recordset
Dim lngRecordPtr As Long

Set rstAppend = CurrentDb.OpenRecordset(strSQL)
Do While rstAppend.EOF = False
rstRandom.AddNew
rstRandom!PostedAmount = rstAppend!PostedAmount
rstRandom!ReprotName = rstAppend!Reportname
rstRandom!sapcompnaycode = rstAppend!sapcompnaycode
rstRandom.Update
rstAppend.Move 10
Loop



End Sub
 
Honestly,

Rather than troubleshooting the SQL expressions, I'd just write a couple of
Access queries and run them in the VBA code with:

DoCmd.OpenQuery "[insert query name here]"

mtrimpin24 said:
I was trying to do the following:
1. get the user to import a spreadsheet from some location on the drive
(location changes).

2. data from spreadsheet imports to a table named Random

3. data from Random gets filtered. All records that pass the following
rules are moved to tableAppend:

NO RECORDS that have the text "mileage" "mile" or "mlg" in the ReportName
field should be moved.
1 All records where PostedAmount is above 3500
2 All records with Sap Company Code 010, 528, 113, 185 that have a
PostedAmount above 1500.
3 of the remaining records, every 10th record should be included, as long
as it passes the above rules. If the 10th record doesn't pass, include the
11th. if the 11th doesn't pass, include the 12th, and so on, then continue
with adding the 20th, 30th... etc.

Here's the code. I get multiple errors. i've played with it some, i can't
quite get it to work. its connected to a command button. the current error
is
Run-time error '3075':
Syntax error (missing operator) in query expression '(not (
(postedAmount >= 3500) or ((SAP Company Code in
(010, 528,185,113)) and (postedamount >=1500))))True'.

and if i remove that, i get the error run time error 3061,
with CurrentDb.Execute strSQL




Public Function GetExcel(Optional strStartDir As String) As String

Dim strFilter As String
Dim lngFlags As Long

If strStartDir = "" Then
strStartDir = CurrentProject.Path
End If

strFilter = ahtAddFilterItem(strFilter, "Excel file (*.xls)", "*.xls")

GetExcel = ahtCommonFileOpenSave(InitialDir:=strStartDir, _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Select Excel sheet")


End Function
Sub MyExcelImport()

Dim strFile As String
Dim strSQL As String

CurrentDb.Execute "delete * from tableAppend"
CurrentDb.Execute "delete * from Random"
CurrentDb.Execute "delete * from AuditerTable"


strFile = GetExcel()
If strFile = "" Then
Exit Sub
End If

' import excel file...

DoCmd.TransferSpreadsheet acImport, , "Random", strFile, True

strSQL = "INSERT INTO TableAppend " & _
" ([postedamount], [reportname],[SAP Company Code])" & _
" select [postedamount], [reportname], [SAP Company Code]" & _
" from Random " & _
" where (not ( (postedAmount >= 3500) or " & _
" ( (SAP Company Code in (010,528,185,113)) and (postedamount >= 1500) ))
)" _
& (Reportname <> "mile" And Reportname <> "mileage" And Reportname <> _
"mlg")

CurrentDb.Execute strSQL
Dim rstAppend As DAO.Recordset
Dim rstRandom As DAO.Recordset
Dim lngRecordPtr As Long

Set rstAppend = CurrentDb.OpenRecordset(strSQL)
Do While rstAppend.EOF = False
rstRandom.AddNew
rstRandom!PostedAmount = rstAppend!PostedAmount
rstRandom!ReprotName = rstAppend!Reportname
rstRandom!sapcompnaycode = rstAppend!sapcompnaycode
rstRandom.Update
rstAppend.Move 10
Loop



End Sub
Click to expand...
 
Look closely at:

strSQL = "INSERT INTO TableAppend " & _
" ([postedamount], [reportname],[SAP Company Code])" & _
" select [postedamount], [reportname], [SAP Company Code]" & _
" from Random " & _
" where (not ( (postedAmount >= 3500) or " & _
" ( (SAP Company Code in (010,528,185,113)) and (postedamount >=
1500) )) )" _
& (Reportname <> "mile" And Reportname <> "mileage" And Reportname <>
"mlg")

You're missing quotes on the last line, and you're missing an And or Or to
connect it to the rest of the SQL. The missing quotes are:

& "(Reportname <> 'mile' And Reportname <> 'mileage' And Reportname <>
'mlg')"

(Note that I changed the delimiter around the report names from double
quotes to single quotes to simplify things.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


mtrimpin24 said:
I was trying to do the following:
1. get the user to import a spreadsheet from some location on the drive
(location changes).

2. data from spreadsheet imports to a table named Random

3. data from Random gets filtered. All records that pass the following
rules are moved to tableAppend:

NO RECORDS that have the text "mileage" "mile" or "mlg" in the ReportName
field should be moved.
1 All records where PostedAmount is above 3500
2 All records with Sap Company Code 010, 528, 113, 185 that have a
PostedAmount above 1500.
3 of the remaining records, every 10th record should be included, as long
as it passes the above rules. If the 10th record doesn't pass, include
the
11th. if the 11th doesn't pass, include the 12th, and so on, then
continue
with adding the 20th, 30th... etc.

Here's the code. I get multiple errors. i've played with it some, i can't
quite get it to work. its connected to a command button. the current
error
is
Run-time error '3075':
Syntax error (missing operator) in query expression '(not (
(postedAmount >= 3500) or ((SAP Company Code in
(010, 528,185,113)) and (postedamount >=1500))))True'.

and if i remove that, i get the error run time error 3061,
with CurrentDb.Execute strSQL




Public Function GetExcel(Optional strStartDir As String) As String

Dim strFilter As String
Dim lngFlags As Long

If strStartDir = "" Then
strStartDir = CurrentProject.Path
End If

strFilter = ahtAddFilterItem(strFilter, "Excel file (*.xls)", "*.xls")

GetExcel = ahtCommonFileOpenSave(InitialDir:=strStartDir, _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Select Excel sheet")


End Function
Sub MyExcelImport()

Dim strFile As String
Dim strSQL As String

CurrentDb.Execute "delete * from tableAppend"
CurrentDb.Execute "delete * from Random"
CurrentDb.Execute "delete * from AuditerTable"


strFile = GetExcel()
If strFile = "" Then
Exit Sub
End If

' import excel file...

DoCmd.TransferSpreadsheet acImport, , "Random", strFile, True

strSQL = "INSERT INTO TableAppend " & _
" ([postedamount], [reportname],[SAP Company Code])" & _
" select [postedamount], [reportname], [SAP Company Code]" & _
" from Random " & _
" where (not ( (postedAmount >= 3500) or " & _
" ( (SAP Company Code in (010,528,185,113)) and (postedamount >= 1500) ))
)" _
& (Reportname <> "mile" And Reportname <> "mileage" And Reportname <> _
"mlg")

CurrentDb.Execute strSQL
Dim rstAppend As DAO.Recordset
Dim rstRandom As DAO.Recordset
Dim lngRecordPtr As Long

Set rstAppend = CurrentDb.OpenRecordset(strSQL)
Do While rstAppend.EOF = False
rstRandom.AddNew
rstRandom!PostedAmount = rstAppend!PostedAmount
rstRandom!ReprotName = rstAppend!Reportname
rstRandom!sapcompnaycode = rstAppend!sapcompnaycode
rstRandom.Update
rstAppend.Move 10
Loop



End Sub
Click to expand...
 
I'd considered that . i can't get the queries to perform that action either.
i STILL don't know the proper SQL to put into the criteria for each of those
rules.


DCPan said:
Honestly,

Rather than troubleshooting the SQL expressions, I'd just write a couple of
Access queries and run them in the VBA code with:

DoCmd.OpenQuery "[insert query name here]"

mtrimpin24 said:
I was trying to do the following:
1. get the user to import a spreadsheet from some location on the drive
(location changes).

2. data from spreadsheet imports to a table named Random

3. data from Random gets filtered. All records that pass the following
rules are moved to tableAppend:

NO RECORDS that have the text "mileage" "mile" or "mlg" in the ReportName
field should be moved.
1 All records where PostedAmount is above 3500
2 All records with Sap Company Code 010, 528, 113, 185 that have a
PostedAmount above 1500.
3 of the remaining records, every 10th record should be included, as long
as it passes the above rules. If the 10th record doesn't pass, include the
11th. if the 11th doesn't pass, include the 12th, and so on, then continue
with adding the 20th, 30th... etc.

Here's the code. I get multiple errors. i've played with it some, i can't
quite get it to work. its connected to a command button. the current error
is
Run-time error '3075':
Syntax error (missing operator) in query expression '(not (
(postedAmount >= 3500) or ((SAP Company Code in
(010, 528,185,113)) and (postedamount >=1500))))True'.

and if i remove that, i get the error run time error 3061,
with CurrentDb.Execute strSQL




Public Function GetExcel(Optional strStartDir As String) As String

Dim strFilter As String
Dim lngFlags As Long

If strStartDir = "" Then
strStartDir = CurrentProject.Path
End If

strFilter = ahtAddFilterItem(strFilter, "Excel file (*.xls)", "*.xls")

GetExcel = ahtCommonFileOpenSave(InitialDir:=strStartDir, _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Select Excel sheet")


End Function
Sub MyExcelImport()

Dim strFile As String
Dim strSQL As String

CurrentDb.Execute "delete * from tableAppend"
CurrentDb.Execute "delete * from Random"
CurrentDb.Execute "delete * from AuditerTable"


strFile = GetExcel()
If strFile = "" Then
Exit Sub
End If

' import excel file...

DoCmd.TransferSpreadsheet acImport, , "Random", strFile, True

strSQL = "INSERT INTO TableAppend " & _
" ([postedamount], [reportname],[SAP Company Code])" & _
" select [postedamount], [reportname], [SAP Company Code]" & _
" from Random " & _
" where (not ( (postedAmount >= 3500) or " & _
" ( (SAP Company Code in (010,528,185,113)) and (postedamount >= 1500) ))
)" _
& (Reportname <> "mile" And Reportname <> "mileage" And Reportname <> _
"mlg")

CurrentDb.Execute strSQL
Dim rstAppend As DAO.Recordset
Dim rstRandom As DAO.Recordset
Dim lngRecordPtr As Long

Set rstAppend = CurrentDb.OpenRecordset(strSQL)
Do While rstAppend.EOF = False
rstRandom.AddNew
rstRandom!PostedAmount = rstAppend!PostedAmount
rstRandom!ReprotName = rstAppend!Reportname
rstRandom!sapcompnaycode = rstAppend!sapcompnaycode
rstRandom.Update
rstAppend.Move 10
Loop



End Sub
Click to expand...
Click to expand...
 
Blah . now it keeps telling me it expects the end of the statement.
where should the and/or go in order to fulfill my conditions?

strSQL = "INSERT INTO TableAppend " & _
" ([postedamount], [reportname],[SAP Company Code])" & _
" select [postedamount], [reportname], [SAP Company Code]" & _
" from Random " & _
" where (not ( (postedAmount >= 3500) or " & _
" ( (SAP Company Code in (010,528,185,113)) and (postedamount >= 1500) ))
)" & _
"( Reportname <> "mile" And Reportname <> "mileage" And Reportname
<>"mlg")")


Douglas J. Steele said:
Look closely at:

strSQL = "INSERT INTO TableAppend " & _
" ([postedamount], [reportname],[SAP Company Code])" & _
" select [postedamount], [reportname], [SAP Company Code]" & _
" from Random " & _
" where (not ( (postedAmount >= 3500) or " & _
" ( (SAP Company Code in (010,528,185,113)) and (postedamount >=
1500) )) )" _
& (Reportname <> "mile" And Reportname <> "mileage" And Reportname <>
"mlg")

You're missing quotes on the last line, and you're missing an And or Or to
connect it to the rest of the SQL. The missing quotes are:

& "(Reportname <> 'mile' And Reportname <> 'mileage' And Reportname <>
'mlg')"

(Note that I changed the delimiter around the report names from double
quotes to single quotes to simplify things.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


mtrimpin24 said:
I was trying to do the following:
1. get the user to import a spreadsheet from some location on the drive
(location changes).

2. data from spreadsheet imports to a table named Random

3. data from Random gets filtered. All records that pass the following
rules are moved to tableAppend:

NO RECORDS that have the text "mileage" "mile" or "mlg" in the ReportName
field should be moved.
1 All records where PostedAmount is above 3500
2 All records with Sap Company Code 010, 528, 113, 185 that have a
PostedAmount above 1500.
3 of the remaining records, every 10th record should be included, as long
as it passes the above rules. If the 10th record doesn't pass, include
the
11th. if the 11th doesn't pass, include the 12th, and so on, then
continue
with adding the 20th, 30th... etc.

Here's the code. I get multiple errors. i've played with it some, i can't
quite get it to work. its connected to a command button. the current
error
is
Run-time error '3075':
Syntax error (missing operator) in query expression '(not (
(postedAmount >= 3500) or ((SAP Company Code in
(010, 528,185,113)) and (postedamount >=1500))))True'.

and if i remove that, i get the error run time error 3061,
with CurrentDb.Execute strSQL




Public Function GetExcel(Optional strStartDir As String) As String

Dim strFilter As String
Dim lngFlags As Long

If strStartDir = "" Then
strStartDir = CurrentProject.Path
End If

strFilter = ahtAddFilterItem(strFilter, "Excel file (*.xls)", "*.xls")

GetExcel = ahtCommonFileOpenSave(InitialDir:=strStartDir, _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
DialogTitle:="Select Excel sheet")


End Function
Sub MyExcelImport()

Dim strFile As String
Dim strSQL As String

CurrentDb.Execute "delete * from tableAppend"
CurrentDb.Execute "delete * from Random"
CurrentDb.Execute "delete * from AuditerTable"


strFile = GetExcel()
If strFile = "" Then
Exit Sub
End If

' import excel file...

DoCmd.TransferSpreadsheet acImport, , "Random", strFile, True

strSQL = "INSERT INTO TableAppend " & _
" ([postedamount], [reportname],[SAP Company Code])" & _
" select [postedamount], [reportname], [SAP Company Code]" & _
" from Random " & _
" where (not ( (postedAmount >= 3500) or " & _
" ( (SAP Company Code in (010,528,185,113)) and (postedamount >= 1500) ))
)" _
& (Reportname <> "mile" And Reportname <> "mileage" And Reportname <> _
"mlg")

CurrentDb.Execute strSQL
Dim rstAppend As DAO.Recordset
Dim rstRandom As DAO.Recordset
Dim lngRecordPtr As Long

Set rstAppend = CurrentDb.OpenRecordset(strSQL)
Do While rstAppend.EOF = False
rstRandom.AddNew
rstRandom!PostedAmount = rstAppend!PostedAmount
rstRandom!ReprotName = rstAppend!Reportname
rstRandom!sapcompnaycode = rstAppend!sapcompnaycode
rstRandom.Update
rstAppend.Move 10
Loop



End Sub
Click to expand...
Click to expand...
 
Try:

" And ( Reportname <> 'mile' And Reportname <> 'mileage' And Reportname
<>'mlg')"

You must change the double quotes inside there to single quotes, as I've
done, or else you have to double up the double quotes:

" And ( Reportname <> ""mile"" And Reportname <> ""mileage""' And Reportname
<>""mlg"")"

Using Not In works too:


" And ( Reportname not in ('mile', 'mileage','mlg'))"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


mtrimpin24 said:
Blah . now it keeps telling me it expects the end of the statement.
where should the and/or go in order to fulfill my conditions?

strSQL = "INSERT INTO TableAppend " & _
" ([postedamount], [reportname],[SAP Company Code])" & _
" select [postedamount], [reportname], [SAP Company Code]" & _
" from Random " & _
" where (not ( (postedAmount >= 3500) or " & _
" ( (SAP Company Code in (010,528,185,113)) and (postedamount >= 1500) ))
)" & _
"( Reportname <> "mile" And Reportname <> "mileage" And Reportname
<>"mlg")")


Douglas J. Steele said:
Look closely at:

strSQL = "INSERT INTO TableAppend " & _
" ([postedamount], [reportname],[SAP Company Code])" & _
" select [postedamount], [reportname], [SAP Company Code]" & _
" from Random " & _
" where (not ( (postedAmount >= 3500) or " & _
" ( (SAP Company Code in (010,528,185,113)) and (postedamount >=
1500) )) )" _
& (Reportname <> "mile" And Reportname <> "mileage" And Reportname <>
"mlg")

You're missing quotes on the last line, and you're missing an And or Or
to
connect it to the rest of the SQL. The missing quotes are:

& "(Reportname <> 'mile' And Reportname <> 'mileage' And Reportname <>
'mlg')"

(Note that I changed the delimiter around the report names from double
quotes to single quotes to simplify things.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


mtrimpin24 said:
I was trying to do the following:
1. get the user to import a spreadsheet from some location on the drive
(location changes).

2. data from spreadsheet imports to a table named Random

3. data from Random gets filtered. All records that pass the
following
rules are moved to tableAppend:

NO RECORDS that have the text "mileage" "mile" or "mlg" in the
ReportName
field should be moved.
1 All records where PostedAmount is above 3500
2 All records with Sap Company Code 010, 528, 113, 185 that have a
PostedAmount above 1500.
3 of the remaining records, every 10th record should be included, as
long
as it passes the above rules. If the 10th record doesn't pass, include
the
11th. if the 11th doesn't pass, include the 12th, and so on, then
continue
with adding the 20th, 30th... etc.

Here's the code. I get multiple errors. i've played with it some, i
can't
quite get it to work. its connected to a command button. the current
error
is
Run-time error '3075':
Syntax error (missing operator) in query expression '(not (
(postedAmount >= 3500) or ((SAP Company Code in
(010, 528,185,113)) and (postedamount >=1500))))True'.

and if i remove that, i get the error run time error 3061,
with CurrentDb.Execute strSQL




Public Function GetExcel(Optional strStartDir As String) As String

Dim strFilter As String
Dim lngFlags As Long

If strStartDir = "" Then
strStartDir = CurrentProject.Path
End If

strFilter = ahtAddFilterItem(strFilter, "Excel file (*.xls)",
"*.xls")

GetExcel = ahtCommonFileOpenSave(InitialDir:=strStartDir, _
Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags,
_
DialogTitle:="Select Excel sheet")


End Function
Sub MyExcelImport()

Dim strFile As String
Dim strSQL As String

CurrentDb.Execute "delete * from tableAppend"
CurrentDb.Execute "delete * from Random"
CurrentDb.Execute "delete * from AuditerTable"


strFile = GetExcel()
If strFile = "" Then
Exit Sub
End If

' import excel file...

DoCmd.TransferSpreadsheet acImport, , "Random", strFile, True

strSQL = "INSERT INTO TableAppend " & _
" ([postedamount], [reportname],[SAP Company Code])" & _
" select [postedamount], [reportname], [SAP Company Code]" & _
" from Random " & _
" where (not ( (postedAmount >= 3500) or " & _
" ( (SAP Company Code in (010,528,185,113)) and (postedamount >=
1500) ))
)" _
& (Reportname <> "mile" And Reportname <> "mileage" And Reportname <> _
"mlg")

CurrentDb.Execute strSQL
Dim rstAppend As DAO.Recordset
Dim rstRandom As DAO.Recordset
Dim lngRecordPtr As Long

Set rstAppend = CurrentDb.OpenRecordset(strSQL)
Do While rstAppend.EOF = False
rstRandom.AddNew
rstRandom!PostedAmount = rstAppend!PostedAmount
rstRandom!ReprotName = rstAppend!Reportname
rstRandom!sapcompnaycode = rstAppend!sapcompnaycode
rstRandom.Update
rstAppend.Move 10
Loop



End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top