Set rs = db.OpenRecordset(Domain)
strSQL = "SELECT *FROM [qryLeftListView]WHERE [TestDate]>" & DateOfVisit
Debug.Print strSQL
Yes, it is working from the query. The code to fill the ListView was copy/pasted.I thought the Domain should either be the table, or a query that the data was coming from but putting either in that space was unacceptable.I'm quite rusty at the moment and so I'm scrambling trying to remember how all of these pieces fit together.I appreciate your help.--
On Mon, Sep 9, 2013 at 10:43 AM, Robert Peterson <bob@alternatefinishing.com> wrote:Sorry I missed that it was an argument of the function.
So you have this string that is an SQL statement but it never gets applied to the “Domain” to filter it.
Whatever is the “qryLeftListView” is what the rest of your function is working from.
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Patricia Mapes
Sent: Monday, September 09, 2013 10:07 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters
It is the "qryLeftListView"...
On Mon, Sep 9, 2013 at 8:23 AM, Robert Peterson <bob@alternatefinishing.com> wrote:
You never answered Graham. What is “Domain” ? I don’t see it declared anywhere.
I think you are doing things in the wrong order. This -
Set rs = db.OpenRecordset(Domain)
'select all events
strSQL = "SELECT * FROM [diagnosis] WHERE (CDate(EndDate))>#" & DateOfVisit & "#"
should be –
'select all events
strSQL = "SELECT * FROM [diagnosis] WHERE (CDate(EndDate))>#" & DateOfVisit & "#"
Set rs = db.OpenRecordset(strSQL)
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Patricia Mapes
Sent: Sunday, September 08, 2013 8:23 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters
I know, very confusing. I'm so sorry. Taking you at your word, this is the entire module code. As you can see it is designed to populate a ListView.
Option Compare Database
Option Explicit
Function FillList(Domain As String, LV As Object) As Boolean
'==================================================================
' Purpose: to fill a ListView control with data from a table or a query
' Arguments: a Domain which is the name of the table or query, and a ListView control object
' Returns: A Boolean value to indicate if the function was successful
'==================================================================
Dim db As DAO.Database
Dim rs As DAO.recordSet
Dim intTotCount As Integer
Dim intCount1 As Integer
Dim intCount2 As Integer
Dim colNew As ColumnHeader
Dim NewLine As ListItem
Dim strSQL As String
Dim DateOfVisit As Date
Dim TestStart As DateDim db As DAO.Database
On Error GoTo Err_Man
DateOfVisit = CDate(Forms!Select_Diagnosis![VisitList])
' Clear the ListView control.
LV.ListItems.Clear
LV.ColumnHeaders.Clear
' Set Variables.
Set db = CurrentDb
Set rs = db.OpenRecordset(Domain)
'select all events
strSQL = "SELECT * FROM [diagnosis] WHERE (CDate(EndDate))>#" & DateOfVisit & "#"
Debug.Print strSQL
' Set Column Headers.
For intCount1 = 0 To rs.Fields.Count - 1
Set colNew = LV.ColumnHeaders.Add(, , CStr(rs(intCount1).Name), 4580)
Next intCount1
LV.View = 3 ' Set View property to 'Report'.
LV.GridLines = True 'Show gridlines
LV.Enabled = True 'Allow records to be selected
LV.FullRowSelect = True 'Allows entire row to be selected
' Set Total Records Counter.
rs.MoveLast
intTotCount = rs.RecordCount
rs.MoveFirst
' Loop through recordset and add Items to the control.
For intCount1 = 1 To intTotCount
If IsNumeric(rs(0).Value) Then
Set NewLine = LV.ListItems.Add(, , Str(rs(0).Value))
Else
Set NewLine = LV.ListItems.Add(, , CStr(rs(0).Value))
End If
For intCount2 = 1 To rs.Fields.Count - 1
NewLine.SubItems(intCount2) = rs(intCount2).Value
Next intCount2
rs.MoveNext
Next intCount1
Exit Function
Err_Man:
' Ignore Error 94 which indicates you passed a NULL value.
If Err = 94 Then
Resume Next
Else
' Otherwise display the error message.
MsgBox "Error: " & Err.Number & Chr(13) & _
Chr(10) & Err.Description
End If
End Function
On Sun, Sep 8, 2013 at 7:59 PM, Graham Mandeno <graham@mandeno.com> wrote:
Hi Patty
Please forgive me, but I would like to return to your original question. You said:
<quote>
I have the following code, failing on the last line:
DateOfVisit = CDate(Forms!Select_Diagnosis![VisitList])
Set db = CurrentDb
Set rs = db.OpenRecordset(Domain)
'select all events
strSQL = "SELECT * FROM [diagnosis] WHERE (CDate(StartDate)) >" & DateOfVisit</quote>
The ONLY line that could possibly raise that error is this one:
Set rs = db.OpenRecordset(Domain)
And the problem would be in the value of the (presumably String) variable Domain, and nothing to do with StartDate.
So, like Duane, I am also confused :-)
Could you please post the code for the entire procedure which is failing? Or at least EVERYTHING (including declarations) up to the line which is failing.
Best wishes,
Graham
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Patricia Mapes
Sent: Monday, 9 September 2013 11:40
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters
It is a ListView Duane.
On Sun, Sep 8, 2013 at 7:23 PM, Duane Hookom <duanehookom@hotmail.com> wrote:
I'm not sure how all of the code fits together. You are using the SQL statement to create a recordset for something. What is that something that apparently shows more records than expected?
Duane Hookom MVP
MS Access
________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: patty.mapes@gmail.com> Date: Sun, 8 Sep 2013 18:59:28 -0400
> Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters
>
>
>> I'm so sorry Duane. It isn't enough I've confused myself but now I'm
> doing it to you! You are right. I had just lost my way trying to find
> the solution.
> This statement works when I put it into a query, and it sorts the
> records as needed.
> strSQL = "SELECT * FROM diagnosis.diag, diagnosis.icd9 FROM diagnosis
> WHERE (((CDate([StartDate]))>
> [Forms]![Select_Diagnosis]![VisitList]))GROUP BY diagnosis.diag,
> diagnosis.icd9"
>
> In the module, however, it returns all records, although it is giving
> the date of the variable (VisitList).
>
> I do appreciate your help and suggestions very much.
> Patty
>
>
> On Sun, Sep 8, 2013 at 6:02 PM, Duane Hookom> <duanehookom@hotmail.com<mailto:duanehookom@hotmail.com>> wrote:
>
>
> Patty,
>
> Now I'm really confused. I thought your code used the SQL statement
> " SELECT * FROM [diagnosis] WHERE (CDate([StartDate]))>#11/18/2011# "
>
> How/where do the more recent SQL statements come from? I don't see any
> code that mentions these last SQL statements.
>
>
> Duane Hookom MVP
> MS Access
>
> ________________________________
>> To:> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>
>> From: patty.mapes@gmail.com<mailto:patty.mapes@gmail.com>>> Date: Sun, 8 Sep 2013 17:43:42 -0400
>> Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters
>>
>> When I put this Select statement into a query I get the correct data:
>> SELECT diagnosis.diag, diagnosis.icd9, diagnosis.diag_id FROM diagnosis
>> WHERE (((CDate([StartDate]))>[forms]![select_Diagnosis]![VisitList])
>> AND ((diagnosis.diag_id)>0)) GROUP BY diagnosis.diag, diagnosis.icd9,
>> diagnosis.diag_id;
>>
>> When I have it in my module, with what I think are the correct syntax
>> changes I get incorrect data, as in all of the records whether their
>> StartData is before or after the DateOfVisit. No error message.
>>
>> This is my "corrected" string:
>> strSQL = "SELECT * FROM diagnosis.diag, diagnosis.icd9,
>> diagnosis.diag_id FROM diagnosis WHERE (((CDate([StartDate]))>#" &
>> [Forms]![select_Diagnosis]![VisitList] & "#) AND
>> ((diagnosis.diag_id)>0)) GROUP BY diagnosis.diag, diagnosis.icd9,
>> diagnosis.diag_id"
>>
>> I know it looks messy... sorry..
>>
>> Patty
>>
>> On Sun, Sep 8, 2013 at 4:59 PM, Duane Hookom
>>> <duanehookom@hotmail.com<mailto:duanehookom@hotmail.com><mailto:duanehookom@hotmail.com<mailto:duanehookom@hotmail.com>>>
> wrote:
>>
>>
>> Patty,
>>>> Are you suggesting that when you view the datasheet of the query, you
>> are seeing records with a StartDate value < 11/18/2011?
>>
>>
>> Duane Hookom MVP
>> MS Access
>>
>> ________________________________>>> From:
> patty.mapes@gmail.com<mailto:patty.mapes@gmail.com>
>>>
>>> I think all that it showed me is that my WHERE statement isn't work at
>>> all. This is the sql:
>>>
>>> strSQL = "SELECT * FROM [diagnosis] WHERE (CDate([StartDate]))>#" &
>>> DateOfVisit & "#"
>>>
>>> And this is the debug:
>>>
>>> SELECT * FROM [diagnosis] WHERE (CDate([StartDate]))>#11/18/2011#
>>>
>>> The WHERE statement should eliminate about 1/2 of the records but I am
>>> getting all of the records displayed in my ListView. It is picking of
>>> the correct DateOfVisit.
>>>
>>>
>>> On Sun, Sep 8, 2013 at 4:28 PM, Duane Hookom> wrote:
>>>
>>>
>>> Patty,
>>>
>>> How did the debugging of the SQL statement look? Is it what you expected?
>>>
>>> The debugging is the first step I would have taken to see what is going
>>> wrong.
>>>
>>>
>>> Duane Hookom MVP
>>> MS Access
>>>
>>> ________________________________>>>> From:
>>
> patty.mapes@gmail.com<mailto:patty.mapes@gmail.com>
>>>> Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters
>>>>
>>>> This is my actual code John.
>>>>
>>>> strSQL = "SELECT * FROM [diagnosis] WHERE (CDate([StartDate]))>&" &
>>>> DateOfVisit & "&"
>>>>
>>>> On Sun, Sep 8, 2013 at 3:47 PM, John Viescas
>>
> <JohnV@msn.com<mailto:JohnV@msn.com><mailto:JohnV@msn.com<mailto:JohnV@msn.com>>>>>>
>>>>>>>> Patty-
>>>>
>>>> Did you see my suggestion about putting brackets around [StartDate] ??
>>>>
>>>> John Viescas, Author
>>>>>>>> From:
>>>>
>>>> Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters
>>>>
>>>> I tried that Duane.
>>>> This is what I got:
>>>>
>>>> SELECT * FROM [diagnosis] WHERE (CDate(StartDate))>&7/5/2013&
>>>>
>>>> So it does look like the (CDate(StartDate)) is not producing a date,
>>>> and all records with all StartDates are being passed. Therefore, I
>>>> assume that my error in the (CDate(StartDate)).
>>>>
>>>> Do you have any ideas about that?
>>>>
>>>> Thanks,
>>>>
>>>> Patty
>>>>
>>>>
>>>>
>>>> On Sun, Sep 8, 2013 at 2:44 PM, Duane Hookom
>>>>>>>> Patty,
>>>> If something isn't working as expected in code, add some debugging.
>>>> Try add this line after the variable is updated:
>>>>
>>>> Debug.Print strSQL
>>>>
>>>> Then open the debug window (press Ctrl+G) to view the statement. You
>>>> can copy the select statement and paste it into the SQL view of a new
>>>> query for troubleshooting.
>>>>
>>>>
>>>> Duane Hookom MVP
>>>> MS Access
>>>>
>>>> ________________________________From: patty.mapes@gmail.com<mailto:patty.mapes@gmail.com>
>>>>> Date: Sun, 8 Sep 2013 14:39:43 -0400
>>>>> Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters
>>>>
>>>>> Duane,
>>>>> I have tried different variations (=, <,>) that should change the data
>>>>> but nothing is having any impact. It appears that entire WHERE section
>>>>> of the statement is just being ignored, but not throwing any error
>>>>> message.
>>>>>
>>>>>
>>>>> On Sun, Sep 8, 2013 at 2:00 PM, Duane Hookom
>>>>
>>> <duanehookom@hotmail.com<mailto:duanehookom@hotmail.com>>>>> wrote:
>>>>>
>>>>> I believe you are missing the delimiters around your date value
>>>>>
>>>>
>>>>> strSQL = "SELECT * FROM [diagnosis] WHERE (CDate(StartDate))>&"
>>>>> & DateOfVisit & "&"
>>>>
>>>>>
>>>>> Duane Hookom MVP
>>>>> MS Access
>>>>>
>>>>> ________________________________>>>>>> From: patty.mapes@gmail.com<mailto:patty.mapes@gmail.com><
>>>>>
>>>>>> I have the following code, failing on the last line:
>>>>>>
>>>>>> DateOfVisit = CDate(Forms!Select_Diagnosis![VisitList])
>>>>>>
>>>>>> Set db = CurrentDb
>>>>>> Set rs = db.OpenRecordset(Domain)
>>>>>> 'select all events
>>>>>>
>>>>>> strSQL = "SELECT * FROM [diagnosis] WHERE (CDate(StartDate))>" &
>>>>>> DateOfVisit
>>>>>>
>>>>>>
>>>>>> If someone can see my error, I will be very grateful.
>>>>>>
>>>>>> Thank you,
>>>>>>
>>>>>> Patty
>>>>>> --
>>>>>> Patricia Mapes
--
Patricia Mapes
--
Patricia Mapes
--
Patricia Mapes
Patricia Mapes
--
Patricia Mapes
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (38) |
Tidak ada komentar:
Posting Komentar