I noticed several postings about issues related to "run-time error
3061", and I observed that the solutions to these issues appear to
involve correcting something within the SQL code. I'm encountering
the "run-time error 3061" issue, but I'm not sure how to go about
correcting the SQL. My hunch is that I have not properly constructed
things to identify a date field, but I don't know how to correct this
issue. In addition to not understanding how to correct the issue, I
do not understand why the query works when it is manually invoked, but
it fails to work when it is invoked within VBA code...seems as though
it would never work if I had incorrectly constructed the query.
My questions are:
1) what changes can I make to the query to avoid this error?
2) why does the query work when invoked from the query tab, but not
work when invoked within VBA code?
The stuff below provides more details.
- Running Access 2000
- created a 2-field query qryFDateInPast
- query relies on the contents of a field from a form
- when the form is open, and the field populated, am able to run the
query from the query tab within Access
- when the form is open and the field populated, receive the '3061'
error when running the VBA code below
- if I modify the query so that the second field is a non-date field
from tblDocumentSetup, then I do not get the error...this is what
leads me to believe that I'm not formatting things correctly to
identify a date field
Within the query, the second field is defined as:
FDateInPast: IIf([FromDate]<[Forms]![frmSwitchboardAdmin]![txtMonthEnding],"Y","N")
The SQL view of the query is:
SELECT tblDocumentSetup.ID,
IIf([FromDate]<[Forms]![frmSwitchboardAdmin]![txtMonthEnding],"Y","N")
AS FDateInPast
FROM tblDocumentSetup;
The field FromDate in tblDocumentSetup is defined as a Date/Time
field.
The field [frmSwitchboardAdmin]!txtMonhtEnding is a text box with
format of short date. I have been filling in this text box field with
the value 05/16/2004.
VBA code:
Dim db As Database
Dim rstZZZ As Recordset
Set db = CurrentDb()
Set rstZZZ = db.OpenRecordset("qryFDateInPast")
I get the same result if I try:
Dim db As Database
Dim quoteChar as String
quoteChar = """"
Dim strSQL as String
Dim rstZZZ As Recordset
Set db = CurrentDb()
strSQL = ""
strSQL = strSQL & "SELECT "
strSQL = strSQL & "tblDocumentSetup.ID, "
strSQL = strSQL & "IIf([FromDate]<[Forms]![frmSwitchboardAdmin]!"
strSQL = strSQL & "[txtMonthEnding],
strSQL = strSQL & quoteChar & "Y" & quoteChar & ","
strSQL = strSQL & quoteChar & "N" & quoteChar & ") AS FDateInPast "
strSQL = strSQL & "FROM tblDocumentSetup"
Set rstZZZ = db.OpenRecordset(strSQL)