Re: Convert MDB to ADP

Tech-Archive recommends: Fix windows errors by optimizing your registry


"Robert Morley" <rmorley@xxxxxxxxxxxxxxxxxxxxxxxx> a écrit dans le message
de news: %23kq5YcK4FHA.1184@xxxxxxxxxxxxxxxxxxxxxxx
> What I would suggest, then, is to use a dynamic record source for your
> form. It's fairly easy to set up. Whenever the combo box changes and you
> would normally requery the form, instead, you'll want to reset the record
> source:
>
> Forms!FormName (or Me).RecordSource = "SELECT B315WellTest.* FROM
> B315WellTest WHERE (((B315WellTest.dDate)='" & rptDate() & "') AND
> ((B315WellTest.Source)=" & rptCtry() & "))"
>
> If rptCtry() returns a string, put single quotes just inside the
> double-quotes on either side; I wasn't sure. Also, dates can be tricky to
> pass to SQL Server, so you may have to play around with the date format a
> bit to get things working.
>
> Your other two options (that come to mind off-hand) are to create a Stored
> Procedure/Function that takes the date & country as parameters and returns
> the appropriate recordset, or to send the combo box value into a table on
> the back end that you can simply join into a view. There are advantages &
> disadvantages to all three methods, but I would recommend the first for
> the time being, as it'll easily be the fastest in terms of converting your
> code, and at low volumes, nearly as fast as a stored procedure in terms of
> execution time.
>
>
> Rob
>
> "Jose Perdigao" <JosePerdigao@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:99E92E13-7DF7-4662-9C4F-06F26B0C34D6@xxxxxxxxxxxxxxxx
>> Thanks Rob,
>>
>> I will try to explain what I'm using in access mdb about filter queries
>> I have a form where I generate reports, queries, forms and by automation
>> I
>> generate excel reports.
>>
>> Basicaly I have two functions, one for date, rptDate() and another to
>> strings rptCtry()
>>
>> From a main form, when I select a date and/or coutry from a list box and
>> combo box, the function rptdate() receive this date and rptCtry() receive
>> the
>> country.
>>
>> I give an example:
>>
>> SELECT B315WellTest.*
>> FROM B315WellTest
>> WHERE (((B315WellTest.dDate)=rptDate()) AND
>> ((B315WellTest.Source)=rptCtry()));
>>
>> For this proceudre all filters running well and quickly and I don't need
>> use
>> the parameter Forms!formname.selectdate and etc.
>>
>> I would like use a procedure similar what I am usaing in mdb. I mean, I
>> pass
>> the date and contry for a table or SP to open queries, forms, reports
>> etc.
>>
>> Thanks
>>
>> Jose
>>
>>
>>
>>
>>
>>
>> "Robert Morley" wrote:
>>
>>> > 1. Is it possibel to find any function in SQL server to replace
>>> > Format,
>>> > left, mid functions?
>>>
>>> There's nothing in SQL Server that directly replace the Format()
>>> function.
>>> About the closest you get is STR() to convert numbers to strings, or
>>> CONVERT
>>> to convert a variety of arguments to other formats, including dates to
>>> strings, etc. Neither of these are anywhere near as functional as the
>>> Format() command was, though; you're probably going to be stuck making
>>> your
>>> own functions for a lot of your Format() needs.
>>>
>>> As for Left() and Right(), they're implemented exactly the same in SQL
>>> Server, so no problems there, and Mid() is called SubString() but is
>>> otherwise the same. I seem to remember slight differences in how they
>>> handle Null's, empty strings, and cases where you ask for more
>>> characters
>>> than there are; also SubString() doesn't support the two-parameter
>>> format
>>> that Mid() does (i.e., you can't use just SubString(MyString,2) to
>>> return
>>> everything from the second character on like you could with Mid()). You
>>> should probably double-check the docs for SQL Server if these are a
>>> concern
>>> for you.
>>>
>>> > 2. I know, we can not use my VBA functions in SQL server.
>>> > In mdb, I have my functions to filter records open forms, queries,
>>> > reports
>>> > based date and countries selected from a list box existing in a form.
>>> > Question.
>>> > How can I filter queries (views, SP), forms, reports in ADP project,
>>> > based
>>> > a
>>> > parameter selected from a list box or combo box?
>>>
>>> If you're just using Me.Filter, you can pretty much leave your code
>>> unchanged. The only slight changes would be things like single-quotes
>>> around strings instead of double-quotes, no #'s around dates (I think
>>> you
>>> can only use single-quotes there as well, and provide a formatted string
>>> as
>>> a date...or use CAST/CONVERT). If you're using other methods, post a
>>> few
>>> more specifics, and I or someone else will undoubtedly be able to help
>>> you
>>> further.
>>>
>>>
>>>
>>> Rob
>>>
>>>
>>>
>
>


.

Relevant Pages

  • Re: Convert MDB to ADP
    ... Your first sugestion I think is good for open forms adn reports can I use the ... >> strings rptCtry() ... >>> There's nothing in SQL Server that directly replace the Format() ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Convert MDB to ADP
    ... What I would suggest, then, is to use a dynamic record source for your form. ... > strings rptCtry() ... Is it possibel to find any function in SQL server to replace Format, ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Missing rows in stored procedure results
    ... First, when specifying dates as strings, either use one of the two safe ... format is not in the documentation on CONVERT, ... WHERE dtTran BETWEEN @dtWeekStart AND @dtWeekEnd ... AND dtImport < @dtImportEnd ...
    (microsoft.public.sqlserver.programming)
  • Re: Is there a "select langauge" module?
    ... > number format, date format, time corrections, gender, pluralization, ... > screen space allocated for strings, text directionality, keyboard ... The MessageFormat class is like a very souped-up sprintf ...
    (comp.lang.perl.misc)
  • new lame effort, self-compressed bin-xml...
    ... hell, I use xml, partly because it ... (likewise, my format supports namespaces, which is imo a plus). ... for the most part strings are merged via mru caching. ...
    (comp.compression)