ACCESS FEED: Re: [MS_AccessPros] Error 3061 Too Few Parameters

Minggu, 08 September 2013

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> 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

________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: patty.mapes@gmail.com
> Date: Sun, 8 Sep 2013 16:45:28 -0400
> Subject: Re: [MS_AccessPros] Error 3061 Too Few Parameters
>
>
>
> 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
> <duanehookom@hotmail.com<mailto:duanehookom@hotmail.com>> 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
>
> ________________________________
>> 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 15:59:50 -0400
>> 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>>>

> wrote:
>>
>>
>> Patty-
>>
>>
>>
>> Did you see my suggestion about putting brackets around [StartDate] ??
>>
>>
>>
>> John Viescas, Author
>>
>> Microsoft Access 2010 Inside Out
>>
>> Microsoft Access 2007 Inside Out
>>
>> Microsoft Access 2003 Inside Out
>>
>> Building Microsoft Access Applications
>>
>> SQL Queries for Mere Mortals
>>
>> http://www.viescas.com/
>>
>> (Paris, France)
>>
>>
>>
>>
>>
>>
>>
>> From:
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com><mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>>
>>
> [mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com><mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>>]

>> On Behalf Of Patricia Mapes
>> Sent: Sunday, September 08, 2013 9:34 PM
>> To:
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com><mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>>

>>
>> 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
>>
> <duanehookom@hotmail.com<mailto:duanehookom@hotmail.com><mailto:duanehookom@hotmail.com<mailto:duanehookom@hotmail.com>>>
> wrote:
>>
>>
>>
>> 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
>>
>> ________________________________
>>> To:
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com><mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>>
>>> From:
> patty.mapes@gmail.com<mailto:patty.mapes@gmail.com><mailto: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><mailto:duanehookom@hotmail.com<mailto:duanehookom@hotmail.com>><mailto:duanehookom@hotmail.com<mailto:duanehookom@hotmail.com><mailto: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
>>>
>>> ________________________________
>>>> To:
>>
>>>
>>
> MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com><mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>><mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com><mailto:MS_Access_Professionals@yahoogroups.com<mailto:MS_Access_Professionals@yahoogroups.com>>>
>>>> From:
>>
> patty.mapes@gmail.com<mailto:patty.mapes@gmail.com><mailto:patty.mapes@gmail.com<mailto:patty.mapes@gmail.com>><mailto:patty.mapes@gmail.com<mailto:patty.mapes@gmail.com><mailto:patty.mapes@gmail.com<mailto:patty.mapes@gmail.com>>>

>>
>>>> Date: Sun, 8 Sep 2013 12:09:31 -0400
>>>> Subject: [MS_AccessPros] Error 3061 Too Few Parameters
>>>>
>>>>
>>>>
>>>> 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
>
>
>
>