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
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (24) |
Tidak ada komentar:
Posting Komentar