Re: Convert MDB to ADP

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
    ... There's nothing in SQL Server that directly replace the Format() function. ... About the closest you get is STRto convert numbers to strings, ... The only slight changes would be things like single-quotes ...
    (microsoft.public.access.adp.sqlserver)
  • Re: convert long timstamp to datetime
    ... The "standard" format for date and time strings in SQL Server ... These strings will always be consistently cast to a DATETIME value so ...
    (microsoft.public.sqlserver.programming)
  • Re: converting string to datetime
    ... zino wrote: ... This value is sent by SQL server as:select ... ISO8601 format instead? ... format in which dates can be represented as strings. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Problems with Dateformat
    ... > queries on a MS SQL Server 2000. ... use parameters, never build complete strings. ... the only two format that SQL Server will always understand. ...
    (microsoft.public.sqlserver.clients)
  • 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)