Re: Querying on dates in dd/mm/yyyy hh/mm/ss format using Access2002 And SQL Server 7

Sylvain,
I am grateful for these wonderful detailed answers.
I didn't think about the multi-user problem that can be caused becuase of
using views, and now I've finally succeeded in retrieving records from my DB
to the form using the 1st method you've suggested (with using Input
Parameters).

I have one more question about the dates issue:
Here is the where clause i'm using in my 'CREATE PROCEDURE' command, in
order to create the SP which is used as the record source for my search
form:
WHERE (dbo.EVENTS.EVENT_TIME >= DATEADD(DAY, - (@DaysToSearch), GETDATE()))
AND

dbo.EVENTS.FREE_TEXT LIKE @SearchedText



The purpose is to let the user search for a string (This part works fine) in
the time of @DaysToSearch days before the user clicked the 'search' button.

The problem is that I'm not sure it's OK to use the GETDATE() function over
there.

I remember that I've read once that doing such things is wrong since the
server compares the parameter values to the date of the creation of the SP,
instead of comparing them to the date of calling the SP. Is that true?
How can I ensure that the server looks x days back from the moment I press
the 'search' button, and not x days back from the moment I created the SP?

Regards,
Amir.


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:OJNYmV3BGHA.3584@xxxxxxxxxxxxxxxxxxxxxxx
> For parsing a string to a date/time, the 103 format doesn't limits the
> user to dates only and will correctly parse a time. See the BOL on CAST
> and CONVERT topic for more info.
>
> For your second question, SP are not void functions: they will returns a
> resultset (or recordset in DAO or ADO language) if they end with a SELECT
> statement. In the case of SP with parameters, you have two possibility
> for calling it:
>
> 1) you can set the record source to the name of a SP, set the Record
> Source Qualifier to dbo and put the list of parameters in the Input
> Parameters property. Check previous posts in this newsgroup for more
> details.
>
> 2) a second possibility is to set the Record Source property to an EXEC
> string that will call the SP; for example:
>
> EXEC MyStoreProcedure 100, 'aaaa', ....
>
> This last method is the most easier to use for beginners. However, with
> SP, you must be very careful about the use of the NOCOUNT property.
> Usually, you should always set the option to ON at the beginning of each
> SP that you intent to call for a returning a resultset:
>
> SET NOCOUNT ON
> ...
>
> Altering Views might seem a good idea but it's not in the case of a
> multi-users application.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
>
> "Amir" <agamy@xxxxxxxxxxxx> wrote in message
> news:%233dQHP2BGHA.4004@xxxxxxxxxxxxxxxxxxxxxxx
>> Hi,
>>
>> Thanks again for your answers.
>>
>> The 103 format is working properly, but (I think) it limits the user to
>> dates only, without hours restriction.
>> On the other hand, I've given it a little thought, and actually it's a
>> bit more comfortable for the user not to be forced to enter the exact
>> hours each time, so there is no need to solve that issue by now (even
>> though I would have wanted to know the solution for using both date+time
>> just because of curiousity).
>>
>> As to your second answer about the functions, I think don't understand it
>> completely.
>> Currently I can think of 3 (similar) methods of doing what I wish to do
>> using Access and SQL Server 7 (e.g. without using stored functions):
>> 1. Use parameterized stored procedure which will alter the View that is
>> used as the row source of my search results form.
>> 2. Use Access DoCmd.RunSQL command in order to do the same (alter the
>> view which is used as the row source for the results form).
>> 3. Use SQL sentence as the row source of the search results form (instead
>> of a view), and change that SQL sentence each time the user clicks the
>> 'search' button.
>> (If I am not wrong) Both 3 options should 'create' the correct query, so
>> that after they run, I might show the form and run a Requery command to
>> show the results.
>> I think I'll use the 2nd or the 3rd option since I'm quite new to stored
>> procedures.
>>
>> Am I wrong? Is there something bad with one of these solutions? Are there
>> other solutions assuming I have to use SQL Server 7?
>>
>> At your first answer you've mentioned that:
>> 'editing Views instead of using server filters or parameterized stored
>> procedures: I'm not sure if this is a good idea.'
>> I don't understand how I can use parameterized stored procedures to solve
>> my problem, because my problem requires that a value will be returned
>> (e.g. a table), and such things are done by using functions, aren't they?
>> Is there a way to solve my problem by using stored procedures? How?
>>
>> Kind Regards,
>> Amir.
>>
>>
>> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
>> wrote in message news:OXM2Oj1BGHA.3936@xxxxxxxxxxxxxxxxxxxxxxx
>>> First of all, did you try the format 103 instead of 102 in your calls to
>>> the Convert() function?
>>>
>>> --
>>> Sylvain Lafontaine, ing.
>>> MVP - Technologies Virtual-PC
>>> E-mail: http://cerbermail.com/?QugbLEWINF
>>>
>>>
>>> "Amir" <agamy@xxxxxxxxxxxx> wrote in message
>>> news:%23z0ckM0BGHA.344@xxxxxxxxxxxxxxxxxxxxxxx
>>>> Hello,
>>>>
>>>> First of all, thanks for your answer.
>>>>
>>>> I've tried to solve that by using stored function, but:
>>>> 1. I'm currently working with SQL Server2000, but I will need to
>>>> implement this on SQL Server 7.0 version, so I think I could not use
>>>> stored functions there. Am I right?
>>>> 2. I've tried to use the following stored function:
>>>> CREATE FUNCTION dbo.SHOW_FILTERED_RESULTS (@DaysBackToSearch Int,
>>>> @CurrentDate SmallDateTime) RETURNS TABLE
>>>> AS
>>>> RETURN (SELECT dbo.EVENTS.FREE_TEXT, dbo.EVENTS.mytext,
>>>> dbo.EVENTS.EVENT_ID, dbo.Z_EVENTS.Z_EVENT_ID, dbo.Z_EVENTS.MYTEXT AS
>>>> Expr1,
>>>> dbo.EVENTS.EVENT_TIME
>>>> FROM dbo.EVENTS LEFT OUTER JOIN
>>>> dbo.Z_EVENTS ON dbo.EVENTS.EVENT_ID = dbo.Z_EVENTS.EVENT_ID
>>>> WHERE (dbo.EVENTS.EVENT_TIME >=
>>>> DATEADD(DAY,-@DaysBackToSearch,@CurrentDate)));
>>>>
>>>> but that produces the following error message:
>>>> Server: Msg 170, Level 15, State 1, Procedure SHOW_FILTERED_RESULTS,
>>>> Line 8
>>>> Line 8: Incorrect syntax near ';'.
>>>>
>>>> Do you have any idea about how I can solve that?
>>>>
>>>> I will be pleased if you post a more detailed explanation about how do
>>>> you think I should solve that problem of filtering the search results
>>>> form without using Access filters, so that all the filtering will be
>>>> done at the server.
>>>>
>>>> Kind regards,
>>>> Amir.
>>>>
>>>> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
>>>> wrote in message news:%23S0AjeyBGHA.1092@xxxxxxxxxxxxxxxxxxxxxxx
>>>>> First, editing Views instead of using server filters or parameterized
>>>>> stored procedures: I'm not sure if this is a good idea.
>>>>>
>>>>> Second, if you run the command « select convert (nvarchar (10),
>>>>> getdate(), 102) », you will see that the result is something like
>>>>> 2005.12.22 and not 22/12/2005. Instead of 102, try with the 103
>>>>> format or reformat (split/relink) the parts of the string
>>>>> MaxDateAndTime into the proper string format for 102.
>>>>>
>>>>> --
>>>>> Sylvain Lafontaine, ing.
>>>>> MVP - Technologies Virtual-PC
>>>>> E-mail: http://cerbermail.com/?QugbLEWINF
>>>>>
>>>>>
>>>>> "Amir" <agamy@xxxxxxxxxxxx> wrote in message
>>>>> news:eEjSMfqBGHA.4076@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>> Hi,
>>>>>>
>>>>>> I have a form used to search for records between curtain dates. In
>>>>>> that form the user types 2 dates, then runs the search using a
>>>>>> 'search' button, and he should see all the records which are between
>>>>>> the dates typed.
>>>>>>
>>>>>> The controls in the form are:
>>>>>> MinDateAndTime (TextBox)
>>>>>> MaxDateAndTime (TextBox)
>>>>>> RunSearch (Command Button)
>>>>>>
>>>>>> I want the user to input the date/time value in the following format:
>>>>>> dd/mm/yyyy hh/mm/ss
>>>>>>
>>>>>> so I've put the following input mask for both MinDateAndTime and
>>>>>> MaxDateAndTime:
>>>>>> 00/00/0000\ 00:00:00;0;_
>>>>>>
>>>>>> Now what I want to do is that each time the RunSearch button is
>>>>>> clicked, it will run a code that alters the view which is the record
>>>>>> source of the form (View is named: View1), then requery, so that the
>>>>>> form will contain only the records which meets the dates restriction.
>>>>>>
>>>>>> The code for the RunSearch button is:
>>>>>> (look at the WHERE clause, the rest is just a 'fill'..)
>>>>>>
>>>>>> Private Sub SearchButton_Click()
>>>>>> DoCmd.RunSQL "ALTER VIEW View1 AS " & _
>>>>>> "SELECT dbo.EVENTS.FREE_TEXT, dbo.EVENTS.mytext, " & _
>>>>>> "dbo.EVENTS.EVENT_ID, dbo.Z_EVENTS.Z_EVENT_ID,
>>>>>> dbo.Z_EVENTS.MYTEXT AS Expr1, " & _
>>>>>> "dbo.EVENTS.EVENT_TIME " & _
>>>>>> "FROM dbo.EVENTS LEFT OUTER JOIN " & _
>>>>>> "dbo.Z_EVENTS ON dbo.EVENTS.EVENT_ID = dbo.Z_EVENTS.EVENT_ID "
>>>>>> & _
>>>>>> "WHERE (dbo.EVENTS.EVENT_TIME < " & _
>>>>>> "CONVERT(DATETIME, " & Chr(39) & Me.MinDateAndTime & Chr(39) &
>>>>>> _
>>>>>> ", 102)) AND (dbo.EVENTS.EVENT_TIME > CONVERT(DATETIME," & _
>>>>>> Chr(39) & Me.MaxDateAndTime & Chr(39) & ", 102))"
>>>>>> Me.Requery
>>>>>> End Sub
>>>>>>
>>>>>> The problem is that after entering, for example, the following
>>>>>> values:
>>>>>> MinDateAndTime: 19/12/2005 12:12:12
>>>>>> MaxDateAndTime: 19/01/2006 12:12:12
>>>>>>
>>>>>> when I click the RunSearch button I get the following error message:
>>>>>> Run-time error '242':
>>>>>> The conversion of a char data type to a datetime data type resulted
>>>>>> in an out-of-range datetime value.
>>>>>>
>>>>>> I've tried looking for the answer in:
>>>>>> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
>>>>>> but I can't understand what exactly I should put in the SQL command
>>>>>> in order for that to work properly, while still letting the user
>>>>>> enter the dates in the
>>>>>> dd/mm/yyyy hh/mm/ss
>>>>>> format and not in other formats.
>>>>>>
>>>>>> I'm using Access2002 with Microsoft SQL Server 7.
>>>>>>
>>>>>> Kind Regards,
>>>>>> Amir.
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.