New to ADP - stored procedures / vba variables 2

[AJParkinson]

I know this sounds lazy, but I need to start somewhere, and fast!
My normal area of working is within an MDB file and am quite used to creating connections from Access 2000 to SQL7 - (DAO connnection only though), and passing parameters via VBA functions to the queries.

Once I've been simply stepped through the basics of something I can generally pick it up and develop it from there. I could do with a simple walk through the following for within an ADP file - any help will be greatly appreciated.

What I need is to pass variables created by users selecting options in form through to the view that generates a report.
Access 2000 ADP > SQL7
Created so far:
One Report, one Form, one View, one Module ? (I will be generating many different reports once I've got the hang of it so I'll reuse code where I can).

At the moment this all works fine, but I need to filter down the data -
On this particular form, the user needs to select a 'territory' from the form, and by default using this particular form will select data from between a set of dates (always the previous month - start to end) and a status.
I have got the code in place to assign values to variables in the VBA code but I'm struggling to understand how to pass them to the view.
Please could somebody comment out a simple step by step for this, particularly any stored procedure creation including connection exlpanation and what type of variable declarations are allowed.

I've read though some of the threads but have failed to relate it back to how I want to use it.
The variables I've created in VBA to be passed are: mer_sdate & mer_edate (for the between criteria), mer_terr, and mer_stat

Below is the SQL behind the view in the ADP with the Where criteria 'pre-set' obviously this is where I need to pass the variables to.

SELECT dbo.SalesLead.reference AS [Lead Reference], 
    dbo.SalesLead.total_price AS [Total Customer Price], 
    dbo.SalesLead.ClosedDate AS [Closed Date], 
    dbo.[l_Lead Status].description AS [Lead Status], 
    dbo.[l_Sales Territory].description AS [Sales Territory], 
    dbo.Company.name AS [Main Company], 
    dbo.Users.username AS Owner
FROM dbo.SalesLead INNER JOIN
    dbo.[l_Lead Status] ON 
    dbo.SalesLead.stage = dbo.[l_Lead Status].code INNER JOIN
    dbo.[l_Sales Territory] ON 
    dbo.SalesLead.class = dbo.[l_Sales Territory].code INNER JOIN
    dbo.Company ON 
    dbo.SalesLead.company_id = dbo.Company.id INNER JOIN
    dbo.Users ON dbo.SalesLead.owner = dbo.Users.id
WHERE (dbo.[l_Sales Territory].description = N'Central') AND 
    (dbo.[l_Lead Status].description LIKE N'Confirmed%') AND 
    (dbo.SalesLead.ClosedDate BETWEEN CONVERT(DATETIME, 
    '2004-04-01 00:00:00', 102) AND CONVERT(DATETIME, 
    '2004-04-30 00:00:00', 102))

I've never written any stored procedures before, and as the SQL db that I'm connecting to is our main db I don't wish to screw anything up!

Lastly a couple of asides - the between dates above, would this exclude anything on the 30th? do I need to change it to the 1st of the next month to be fully inclusive? and what does the '102' mean?

Once again, any help will be greatly appreciated.
Andrew

 

[batteam]

One good place to start is the 'Input Parameters' property in the report's property box. Its the very last item in the box and is new in Access Projects. Work with some example reports and test data to get the hang of how that works. Don't forget to use the Access 'Help' file for this item, too. Good Luck.

 

[cmmrfrds]

The 102 is the style property of the convert function. Here are a couple of examples.

Without century (yy) With century (yyyy)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd

In this example
CONVERT(DATETIME,'2004-04-01 00:00:00', 102)
the 102 style will format the date as yyyy.mm.dd
Notice the time portion is ignored which is good for a between date range check. That way the time portion will be ignored for the last day of the month. If the style was 2 then the format would be yy.mm.dd. Now if you want to look at the time part then use a style that includes the time.

In your example the convert function would be applied to your database field not the literal since you can format the literal anyway you want already.
CONVERT(varchar(10),dbo.SalesLead.Closed, 102) = '1999.12.31'

 

[AJParkinson]

Thanks for the tip batteam, and thanks for the clarification cmmrfrds, much appreciated.

 

[AJParkinson]

What have I got wrong?

Typical values for the variables used to pass values to the functions in the vba module are:
mer_sdate - 01/04/2004 (declared as a date in the module)
mer_edate - 30/04/2004 (declared as a date in the module)
mer_terr - Central (declared as a string in the module)
mer_stat - Confirmed Order (declared as a string in the module)

When I run the form/report I get the error :There was a problem accessing a property or method of the OLE object.
If I take/comment the date variables out of the equation, just leaving the string variables, everything works - the report shows the expected results.

In the report properties 'Input Parameters' I have:

@mer_sdate = COPM1(),@mer_edate = COPM2(),@mer_terr = COPM3(),@mer_stat = COPM4()

In a vba module I have the functions:

Public Function COPM1() As String
  COPM1 = mer_sdate
End Function

Public Function COPM2() As String
  COPM2 = mer_edate
End Function

Public Function COPM3() As String
  COPM3 = mer_terr
End Function

Public Function COPM4() As String
  COPM4 = mer_stat
End Function

I've have a stored procedure as the record source for the report:

Alter Procedure "mer_sp_conf_ord_prev_m"

@mer_sdate DATETIME,
@mer_edate DATETIME,
@mer_terr NVARCHAR(40),
@mer_stat NVARCHAR(40)

As

SELECT dbo.SalesLead.reference AS [Lead Reference], 
    dbo.SalesLead.total_price AS [Total Customer Price], 
    dbo.SalesLead.ClosedDate AS [Closed Date], 
    dbo.[l_Lead Status].description AS [Lead Status], 
    dbo.[l_Sales Territory].description AS [Sales Territory], 
    dbo.Company.name AS [Main Company], 
    dbo.Users.username AS Owner
FROM dbo.SalesLead INNER JOIN
    dbo.[l_Lead Status] ON 
    dbo.SalesLead.stage = dbo.[l_Lead Status].code INNER JOIN
    dbo.[l_Sales Territory] ON 
    dbo.SalesLead.class = dbo.[l_Sales Territory].code INNER JOIN
    dbo.Company ON 
    dbo.SalesLead.company_id = dbo.Company.id INNER JOIN
    dbo.Users ON dbo.SalesLead.owner = dbo.Users.id
WHERE (dbo.[l_Sales Territory].description = @mer_terr) AND 
    (dbo.[l_Lead Status].description = @mer_stat) AND 
    (dbo.SalesLead.ClosedDate BETWEEN CONVERT(DATETIME, @mer_sdate, 102) AND
    CONVERT(DATETIME, @mer_edate, 102))

Please help!

 

[AJParkinson]

Think I've found the problem, I was sending dates to the variables in the format dd/mm/yyyy - I changed it round to yyyy/mm/dd and it works!