Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Question Unanswered: Help again edit parameters from MDB to ADP

    I need to convert this from Jet SqL to SQL Server. Moving out of an MDB to an ADP some of the queries I need to change over, I inherited this database and the bosses want all databases moved over from MDB to ADP.


    Code:
    SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Count([Main Table].[Action Type]) AS [CountOfAction Type]
    FROM [Main Table]
    GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
    HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the Month and the Year]));

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by desireemm

    Code:
    SELECT Format$([Main Table].Date,'mmmm yyyy') AS [Date By Month], [Main Table].[Action Type], Count([Main Table].[Action Type]) AS [CountOfAction Type]
    FROM [Main Table]
    GROUP BY Format$([Main Table].Date,'mmmm yyyy'), [Main Table].[Action Type]
    HAVING (((Format$([Main Table].[Date],'mmmm yyyy'))=[Enter the Month and the Year]));
    1. For FORMAT$, look in SQL BOL for Convert. Something like Convert(Varchar(7), [Main Table].[Date]

    2. For your parameter ([Enter the Month and Year]), you will likely have to create the query as a stored procedure with an input parameter (or two). Something like:

    CREATE PROC spMyProc (@MonthYear varchar(7) )

    AS

    SELECT ...
    FROM
    WHERE
    MyDateString = @MonthYear


    You would have to validate that @MonthYear was valid (like mm/yyyy). I would not necessarily do it this way. I would use one of the following options:

    1. Declare the incoming parameter as a datetime and calculate the first and last day of the month. Then use two local variables and a BETWEEN statement. This should render the best performance for large amounts of data.

    2. or Declare the incoming parameter as two smallint variables. Then you could use DatePart function twice to get month and year.


    Regards,

    Hugh Scott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •