PMJI, but:
strSQL = "SELECT * FROM diagnosis.diag, diagnosis.icd9 FROM diagnosis
WHERE (((CDate([StartDate]))>
[Forms]![Select_Diagnosis]![VisitList]))GROUP BY diagnosis.diag,
diagnosis.icd9"
.. isn't even valid SQL. Where did the two FROM clauses come from?
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)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom
Sent: Monday, September 09, 2013 1:23 AM
To: Access Professionals Yahoo Group
Subject: RE: [MS_AccessPros] Error 3061 Too Few Parameters
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:duaneh
> ookom@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
------------------------------------
Yahoo! Groups Links
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (32) |
Tidak ada komentar:
Posting Komentar