runtime error 2001

E

emquick

I've been scratching my head trying to figure out why the runtime error
occurs when the DAvg (or DMax or DMin) function call is made in CalcAvg (or
CalcMax or CalcMin) function. I try to avoid creating and deleting (query
and recordset) objects since they make the db really bloated while the module
is running.

I appreciate any help. Below is the code.

Thanks.
Elie Quick

Function GetHML_noqry()

Dim db As Database
Dim rs As Recordset, rs1 As Recordset
Dim str As String, ans As String


'import data
'prompt for file name, if file name exists, then existing data in table wil
be wiped to allow for new data
str = InputBox("Enter filename (full path) to be imported")

If IsNull(str) Or str = "" Then
MsgBox ("No filename, exit function")
Exit Function
Else
DoCmd.DeleteObject acTable, "currlist"
DoCmd.CopyObject , "currlist", acTable, "RE_tmplt"
DoCmd.TransferText acImportDelim, "RE_Import_Spec", "currlist", str,
-1
End If

ans = MsgBox("Do you need to import base data?", vbYesNo)
If ans = vbYes Then
DoCmd.DeleteObject acTable, "currbd"
DoCmd.CopyObject , "currbd", acTable, "BD_tmplt"
str = InputBox("Enter filename (full path) to be imported")
DoCmd.TransferText , "REbd_Import_Spec", "currbd", str, -1
End If

'setup query and prepare table for calucation and update
DoCmd.Hourglass True

Set db = CurrentDb
Set rs = db.OpenRecordset("currlist")

rs.MoveFirst

Do While Not rs.EOF

rs.Edit
rs!Average = CalcAvg(rs!ar, rs!rms, rs!br, rs!bth)
rs!High = CalcMax(rs!ar, rs!rms, rs!br, rs!bth)
rs!Low = CalcMin(rs!ar, rs!rms, rs!br, rs!bth)
rs.Update

rs.MoveNext
Loop

DoCmd.Hourglass False


'export data to Excel
str = ""
Do While (IsNull(str) Or str = "")
str = InputBox("Exporting to Excel file format. Please enter
filename (full path)")

If IsNull(str) Or str = "" Then
ans = MsgBox("Filename is required", vbOKOnly)
End If
Loop

DoCmd.TransferSpreadsheet acExport, , "currlist", str, -1


'close db objects and reset table
rs.Close
db.Close

End Function


Function CalcAvg(ByVal area As Double, ByVal rooms As Double, ByVal bed As
Double, ByVal bath As Double) As Double

Dim str As String

str = "[AR]=" & area & " AND [RMS]=" & rooms & " AND [BD]=" & bed & "
AND [BTH]=" & bath

CalcAvg = DAvg("[LP]", "currbd", str)

End Function

Function CalcMax(ByVal area As Double, ByVal rooms As Double, ByVal bed As
Double, ByVal bath As Double) As Double

Dim str As String

str = "[AR]=" & area & " AND [RMS]=" & rooms & " AND [BD]=" & bed & "
AND [BTH]=" & bath

CalcMax = DMax("[LP]", "currbd", str)

End Function

Function CalcMin(ByVal area As Double, ByVal rooms As Double, ByVal bed As
Double, ByVal bath As Double) As Double

Dim str As String

str = "[AR]=" & area & " AND [RMS]=" & rooms & " AND [BD]=" & bed & "
AND [BTH]=" & bath

CalcMin = DMin("[LP]", "currbd", str)

End Function
 
6

'69 Camaro

Hi, Elie.

One can get Run-time error 2001 when using the domain functions with a text
data type and improper syntax for text strings. Ensure that the data types
for all of the fields ([AR], [RMS], [BD], [BTH]) are numbers, not text
strings. If any of them are text strings, then use the proper syntax for
delineating text strings.

For example:

Dim sRegion As String
Dim cAvgSales As Currency

sRegion = "San Francisco"
cAvgSales = DAvg("StoreRevenues", "tblSales", "Region = '" & sRegion & "'")
I appreciate any help.
Click to expand...

Since you asked:

It's not a good idea to use built-in function names as variable names. Str
is a built-in VBA function. And just so that this code will be convertable
to Access 2000 or 2002 without resetting the priority of the libraries, you
should qualify the Recordset object in the variable declaration with
DAO.Recordset to indicate the Recordset object from the DAO library, instead
of the default ADODB library. And rs1 is declared but never used in the
code. And there's no error handling in any of the procedures. And
GetHML_noqry( ) is declared as a function, but doesn't return a value, nor
is it defined to return an explicit data type. (Perhaps it should be
declared as a subroutine, instead.) And if I were nitpicky, I'd tell you
that there are a few typos in the comments. But the comments are all
informative and well-placed, which is very helpful for the maintenance
programmer.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)


emquick said:
I've been scratching my head trying to figure out why the runtime error
occurs when the DAvg (or DMax or DMin) function call is made in CalcAvg (or
CalcMax or CalcMin) function. I try to avoid creating and deleting (query
Click to expand...
and recordset) objects since they make the db really bloated while the module
is running.

I appreciate any help. Below is the code.

Thanks.
Elie Quick

Function GetHML_noqry()

Dim db As Database
Dim rs As Recordset, rs1 As Recordset
Dim str As String, ans As String


'import data
'prompt for file name, if file name exists, then existing data in table wil
be wiped to allow for new data
str = InputBox("Enter filename (full path) to be imported")

If IsNull(str) Or str = "" Then
MsgBox ("No filename, exit function")
Exit Function
Else
DoCmd.DeleteObject acTable, "currlist"
DoCmd.CopyObject , "currlist", acTable, "RE_tmplt"
DoCmd.TransferText acImportDelim, "RE_Import_Spec", "currlist", str,
-1
End If

ans = MsgBox("Do you need to import base data?", vbYesNo)
If ans = vbYes Then
DoCmd.DeleteObject acTable, "currbd"
DoCmd.CopyObject , "currbd", acTable, "BD_tmplt"
str = InputBox("Enter filename (full path) to be imported")
DoCmd.TransferText , "REbd_Import_Spec", "currbd", str, -1
End If

'setup query and prepare table for calucation and update
DoCmd.Hourglass True

Set db = CurrentDb
Set rs = db.OpenRecordset("currlist")

rs.MoveFirst

Do While Not rs.EOF

rs.Edit
rs!Average = CalcAvg(rs!ar, rs!rms, rs!br, rs!bth)
rs!High = CalcMax(rs!ar, rs!rms, rs!br, rs!bth)
rs!Low = CalcMin(rs!ar, rs!rms, rs!br, rs!bth)
rs.Update

rs.MoveNext
Loop

DoCmd.Hourglass False


'export data to Excel
str = ""
Do While (IsNull(str) Or str = "")
str = InputBox("Exporting to Excel file format. Please enter
filename (full path)")

If IsNull(str) Or str = "" Then
ans = MsgBox("Filename is required", vbOKOnly)
End If
Loop

DoCmd.TransferSpreadsheet acExport, , "currlist", str, -1


'close db objects and reset table
rs.Close
db.Close

End Function


Function CalcAvg(ByVal area As Double, ByVal rooms As Double, ByVal bed As
Double, ByVal bath As Double) As Double

Dim str As String

str = "[AR]=" & area & " AND [RMS]=" & rooms & " AND [BD]=" & bed & "
AND [BTH]=" & bath

CalcAvg = DAvg("[LP]", "currbd", str)

End Function

Function CalcMax(ByVal area As Double, ByVal rooms As Double, ByVal bed As
Double, ByVal bath As Double) As Double

Dim str As String

str = "[AR]=" & area & " AND [RMS]=" & rooms & " AND [BD]=" & bed & "
AND [BTH]=" & bath

CalcMax = DMax("[LP]", "currbd", str)

End Function

Function CalcMin(ByVal area As Double, ByVal rooms As Double, ByVal bed As
Double, ByVal bath As Double) As Double

Dim str As String

str = "[AR]=" & area & " AND [RMS]=" & rooms & " AND [BD]=" & bed & "
AND [BTH]=" & bath

CalcMin = DMin("[LP]", "currbd", str)

End Function
Click to expand...
 
E

emquick

Gunny,

Thanks for the reply (and comments). I make sure that the data types
for[AR], [RMS], [BR], and [BTH] are numbers. I even checked the tables to
make sure all data types match up. When debugging, the string looks fine,
but the error is occurs.

I made some modifications to the module so the the criteria and the function
call all happen within the loop. If you or anyone has any other thoughts on
this, I like to hear them.

Thanks, Elie

Set db = CurrentDb
Set rs = db.OpenRecordset("currlist")

rs.MoveFirst

Do While Not rs.EOF
txtstr = "[AR]=" & rs!AR & " AND [RMS]=" & rs!RMS & " AND [BD]=" &
rs!BR & " AND [BTH]=" & rs!BTH

rs.Edit
rs!Average = DAvg("[LP]", "currbd", txtstr)
rs!High = DMax("[LP]", "currbd", txtstr)
rs!Low = DMin("[LP]", "currbd", txtstr)
rs.Update

rs.MoveNext
Loop

'69 Camaro said:
Hi, Elie.

One can get Run-time error 2001 when using the domain functions with a text
data type and improper syntax for text strings. Ensure that the data types
for all of the fields ([AR], [RMS], [BD], [BTH]) are numbers, not text
strings. If any of them are text strings, then use the proper syntax for
delineating text strings.

For example:

Dim sRegion As String
Dim cAvgSales As Currency

sRegion = "San Francisco"
cAvgSales = DAvg("StoreRevenues", "tblSales", "Region = '" & sRegion & "'")
I appreciate any help.
Click to expand...

Since you asked:

It's not a good idea to use built-in function names as variable names. Str
is a built-in VBA function. And just so that this code will be convertable
to Access 2000 or 2002 without resetting the priority of the libraries, you
should qualify the Recordset object in the variable declaration with
DAO.Recordset to indicate the Recordset object from the DAO library, instead
of the default ADODB library. And rs1 is declared but never used in the
code. And there's no error handling in any of the procedures. And
GetHML_noqry( ) is declared as a function, but doesn't return a value, nor
is it defined to return an explicit data type. (Perhaps it should be
declared as a subroutine, instead.) And if I were nitpicky, I'd tell you
that there are a few typos in the comments. But the comments are all
informative and well-placed, which is very helpful for the maintenance
programmer.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)


emquick said:
I've been scratching my head trying to figure out why the runtime error
occurs when the DAvg (or DMax or DMin) function call is made in CalcAvg (or
CalcMax or CalcMin) function. I try to avoid creating and deleting (query
Click to expand...
and recordset) objects since they make the db really bloated while the module
is running.

I appreciate any help. Below is the code.

Thanks.
Elie Quick

Function GetHML_noqry()

Dim db As Database
Dim rs As Recordset, rs1 As Recordset
Dim str As String, ans As String


'import data
'prompt for file name, if file name exists, then existing data in table wil
be wiped to allow for new data
str = InputBox("Enter filename (full path) to be imported")

If IsNull(str) Or str = "" Then
MsgBox ("No filename, exit function")
Exit Function
Else
DoCmd.DeleteObject acTable, "currlist"
DoCmd.CopyObject , "currlist", acTable, "RE_tmplt"
DoCmd.TransferText acImportDelim, "RE_Import_Spec", "currlist", str,
-1
End If

ans = MsgBox("Do you need to import base data?", vbYesNo)
If ans = vbYes Then
DoCmd.DeleteObject acTable, "currbd"
DoCmd.CopyObject , "currbd", acTable, "BD_tmplt"
str = InputBox("Enter filename (full path) to be imported")
DoCmd.TransferText , "REbd_Import_Spec", "currbd", str, -1
End If

'setup query and prepare table for calucation and update
DoCmd.Hourglass True

Set db = CurrentDb
Set rs = db.OpenRecordset("currlist")

rs.MoveFirst

Do While Not rs.EOF

rs.Edit
rs!Average = CalcAvg(rs!ar, rs!rms, rs!br, rs!bth)
rs!High = CalcMax(rs!ar, rs!rms, rs!br, rs!bth)
rs!Low = CalcMin(rs!ar, rs!rms, rs!br, rs!bth)
rs.Update

rs.MoveNext
Loop

DoCmd.Hourglass False


'export data to Excel
str = ""
Do While (IsNull(str) Or str = "")
str = InputBox("Exporting to Excel file format. Please enter
filename (full path)")

If IsNull(str) Or str = "" Then
ans = MsgBox("Filename is required", vbOKOnly)
End If
Loop

DoCmd.TransferSpreadsheet acExport, , "currlist", str, -1


'close db objects and reset table
rs.Close
db.Close

End Function


Function CalcAvg(ByVal area As Double, ByVal rooms As Double, ByVal bed As
Double, ByVal bath As Double) As Double

Dim str As String

str = "[AR]=" & area & " AND [RMS]=" & rooms & " AND [BD]=" & bed & "
AND [BTH]=" & bath

CalcAvg = DAvg("[LP]", "currbd", str)

End Function

Function CalcMax(ByVal area As Double, ByVal rooms As Double, ByVal bed As
Double, ByVal bath As Double) As Double

Dim str As String

str = "[AR]=" & area & " AND [RMS]=" & rooms & " AND [BD]=" & bed & "
AND [BTH]=" & bath

CalcMax = DMax("[LP]", "currbd", str)

End Function

Function CalcMin(ByVal area As Double, ByVal rooms As Double, ByVal bed As
Double, ByVal bath As Double) As Double

Dim str As String

str = "[AR]=" & area & " AND [RMS]=" & rooms & " AND [BD]=" & bed & "
AND [BTH]=" & bath

CalcMin = DMin("[LP]", "currbd", str)

End Function
Click to expand...
Click to expand...
 
E

emquick

Gunny,

Soon as I posted my reply, I spotted a couple of things, corrected them, and
it works!!

Thanks so much for your help. Elie

emquick said:
I've been scratching my head trying to figure out why the runtime error
occurs when the DAvg (or DMax or DMin) function call is made in CalcAvg (or
CalcMax or CalcMin) function. I try to avoid creating and deleting (query
and recordset) objects since they make the db really bloated while the module
is running.

I appreciate any help. Below is the code.

Thanks.
Elie Quick

Function GetHML_noqry()

Dim db As Database
Dim rs As Recordset, rs1 As Recordset
Dim str As String, ans As String


'import data
'prompt for file name, if file name exists, then existing data in table wil
be wiped to allow for new data
str = InputBox("Enter filename (full path) to be imported")

If IsNull(str) Or str = "" Then
MsgBox ("No filename, exit function")
Exit Function
Else
DoCmd.DeleteObject acTable, "currlist"
DoCmd.CopyObject , "currlist", acTable, "RE_tmplt"
DoCmd.TransferText acImportDelim, "RE_Import_Spec", "currlist", str,
-1
End If

ans = MsgBox("Do you need to import base data?", vbYesNo)
If ans = vbYes Then
DoCmd.DeleteObject acTable, "currbd"
DoCmd.CopyObject , "currbd", acTable, "BD_tmplt"
str = InputBox("Enter filename (full path) to be imported")
DoCmd.TransferText , "REbd_Import_Spec", "currbd", str, -1
End If

'setup query and prepare table for calucation and update
DoCmd.Hourglass True

Set db = CurrentDb
Set rs = db.OpenRecordset("currlist")

rs.MoveFirst

Do While Not rs.EOF

rs.Edit
rs!Average = CalcAvg(rs!ar, rs!rms, rs!br, rs!bth)
rs!High = CalcMax(rs!ar, rs!rms, rs!br, rs!bth)
rs!Low = CalcMin(rs!ar, rs!rms, rs!br, rs!bth)
rs.Update

rs.MoveNext
Loop

DoCmd.Hourglass False


'export data to Excel
str = ""
Do While (IsNull(str) Or str = "")
str = InputBox("Exporting to Excel file format. Please enter
filename (full path)")

If IsNull(str) Or str = "" Then
ans = MsgBox("Filename is required", vbOKOnly)
End If
Loop

DoCmd.TransferSpreadsheet acExport, , "currlist", str, -1


'close db objects and reset table
rs.Close
db.Close

End Function


Function CalcAvg(ByVal area As Double, ByVal rooms As Double, ByVal bed As
Double, ByVal bath As Double) As Double

Dim str As String

str = "[AR]=" & area & " AND [RMS]=" & rooms & " AND [BD]=" & bed & "
AND [BTH]=" & bath

CalcAvg = DAvg("[LP]", "currbd", str)

End Function

Function CalcMax(ByVal area As Double, ByVal rooms As Double, ByVal bed As
Double, ByVal bath As Double) As Double

Dim str As String

str = "[AR]=" & area & " AND [RMS]=" & rooms & " AND [BD]=" & bed & "
AND [BTH]=" & bath

CalcMax = DMax("[LP]", "currbd", str)

End Function

Function CalcMin(ByVal area As Double, ByVal rooms As Double, ByVal bed As
Double, ByVal bath As Double) As Double

Dim str As String

str = "[AR]=" & area & " AND [RMS]=" & rooms & " AND [BD]=" & bed & "
AND [BTH]=" & bath

CalcMin = DMin("[LP]", "currbd", str)

End Function
Click to expand...
 
6

'69 Camaro

You're welcome! Glad it helped.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)


emquick said:
Gunny,

Soon as I posted my reply, I spotted a couple of things, corrected them, and
it works!!

Thanks so much for your help. Elie

emquick said:
I've been scratching my head trying to figure out why the runtime error
occurs when the DAvg (or DMax or DMin) function call is made in CalcAvg (or
CalcMax or CalcMin) function. I try to avoid creating and deleting (query
and recordset) objects since they make the db really bloated while the module
is running.

I appreciate any help. Below is the code.

Thanks.
Elie Quick

Function GetHML_noqry()

Dim db As Database
Dim rs As Recordset, rs1 As Recordset
Dim str As String, ans As String


'import data
'prompt for file name, if file name exists, then existing data in table wil
be wiped to allow for new data
str = InputBox("Enter filename (full path) to be imported")

If IsNull(str) Or str = "" Then
MsgBox ("No filename, exit function")
Exit Function
Else
DoCmd.DeleteObject acTable, "currlist"
DoCmd.CopyObject , "currlist", acTable, "RE_tmplt"
DoCmd.TransferText acImportDelim, "RE_Import_Spec", "currlist", str,
-1
End If

ans = MsgBox("Do you need to import base data?", vbYesNo)
If ans = vbYes Then
DoCmd.DeleteObject acTable, "currbd"
DoCmd.CopyObject , "currbd", acTable, "BD_tmplt"
str = InputBox("Enter filename (full path) to be imported")
DoCmd.TransferText , "REbd_Import_Spec", "currbd", str, -1
End If

'setup query and prepare table for calucation and update
DoCmd.Hourglass True

Set db = CurrentDb
Set rs = db.OpenRecordset("currlist")

rs.MoveFirst

Do While Not rs.EOF

rs.Edit
rs!Average = CalcAvg(rs!ar, rs!rms, rs!br, rs!bth)
rs!High = CalcMax(rs!ar, rs!rms, rs!br, rs!bth)
rs!Low = CalcMin(rs!ar, rs!rms, rs!br, rs!bth)
rs.Update

rs.MoveNext
Loop

DoCmd.Hourglass False


'export data to Excel
str = ""
Do While (IsNull(str) Or str = "")
str = InputBox("Exporting to Excel file format. Please enter
filename (full path)")

If IsNull(str) Or str = "" Then
ans = MsgBox("Filename is required", vbOKOnly)
End If
Loop

DoCmd.TransferSpreadsheet acExport, , "currlist", str, -1


'close db objects and reset table
rs.Close
db.Close

End Function


Function CalcAvg(ByVal area As Double, ByVal rooms As Double, ByVal bed As
Double, ByVal bath As Double) As Double

Dim str As String

str = "[AR]=" & area & " AND [RMS]=" & rooms & " AND [BD]=" & bed & "
AND [BTH]=" & bath

CalcAvg = DAvg("[LP]", "currbd", str)

End Function

Function CalcMax(ByVal area As Double, ByVal rooms As Double, ByVal bed As
Double, ByVal bath As Double) As Double

Dim str As String

str = "[AR]=" & area & " AND [RMS]=" & rooms & " AND [BD]=" & bed & "
AND [BTH]=" & bath

CalcMax = DMax("[LP]", "currbd", str)

End Function

Function CalcMin(ByVal area As Double, ByVal rooms As Double, ByVal bed As
Double, ByVal bath As Double) As Double

Dim str As String

str = "[AR]=" & area & " AND [RMS]=" & rooms & " AND [BD]=" & bed & "
AND [BTH]=" & bath

CalcMin = DMin("[LP]", "currbd", str)

End Function
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cell Selection for update 55 columns 2

Top