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> 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.> Date: Sun, 8 Sep 2013 17:43:42 -0400
Duane Hookom MVP
MS Access
________________________________
> To: MS_Access_Professionals@yahoogroups.com
> From: patty.mapes@gmail.com> 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>> 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<mailto:MS_Access_Professionals@yahoogroups.com>
>> From: patty.mapes@gmail.com<mailto: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><mailto: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><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 15:59:50 -0400> <JohnV@msn.com<mailto:JohnV@msn.com><mailto:JohnV@msn.com<mailto:JohnV@msn.com>><mailto:JohnV@msn.com<mailto:JohnV@msn.com><mailto:JohnV@msn.com<mailto:JohnV@msn.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
>>>
>>
>> wrote:> [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<mailto:MS_Access_Professionals@yahoogroups.com>>>]
>>>
>>>
>>> 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> 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>>>
>>> Sent: Sunday, September 08, 2013 9:34 PM
>>> To:
>>>
>>
>>>
>>> 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>><mailto: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>><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 14:39:43 -0400> <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>>><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<mailto:duanehookom@hotmail.com>>>>>
>>>> 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
>>>
>>>>
>>>
>>
>>> wrote:> 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>>><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<mailto:MS_Access_Professionals@yahoogroups.com>>>>
>>>>
>>>>
>>>> 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:
>>>
>>>>
>>>
>>
>>>>> 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>>><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<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
>>
>>
>>
>>
>
>
>
> --
> Patricia Mapes
>
>
>
>
--
Patricia Mapes
__._,_.___
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (21) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar