Upsizing questions From someone a little Green

[lameid]

This is my first real upsizing attempt. I foolishly stuck with Jet so I could get things going faster without learning all the ins and outs of ADP's. Catch 22 is I ran across a reporting problem and thought, a Transact-SQL Full join would solve this problem (no, I'm not kidding and I hate this project).

I don't have any real SQL server programming experience but I read a Transact-SQL book for version 7.0 (cringe, running 2000).

It seems most of my errors stem from Jet Functions and one custom function. Specifically I have a bomb on a format function used to format and convert a date (or mask it) and a custom function.

First the custom function used in the Jet query...

Function NotToExceed(dblValue, dblNotToExceed) As Double
If dblValue > dblNotToExceed Then
NotToExceed = dblNotToExceed
Else
NotToExceed = dblValue
End If
End Function

How embarrasing, no error handling.

I'm hoping that there is a similar SQL Server Function and I can just fix the VBA and queries to use the same name and hence fixing the conversion problem. Otherwise, I'm thinking create a database, create an appropriate stored procedure and then upsize. Any thoughts, criticism, boots to the head or create procedure examples are welcome here.

I am also using the Jet format function to control dates and below is an example although it shows another error...

Server Error 156: Incorrect syntax near the keyword 'Index'.

CREATE PROCEDURE "qry Update DealVolume RatePeriod"
AS
UPDATE DealVolume SET DealVolume.RatePeriod = convert(datetime,format(DealVolume.VolDate,Index.FormatStringMask))
FROM
WHERE

I'm playing games where I convert an extra field value to a date format appropriate to the pricing index... I.e. m/1/yyyy because everthing in August for a particular Pricing Index.

Is there a quick fix to the keyword problem for upsizing or do I get to rename the table <dolt>?

I hope this is enough detail because the back-up tape is crying for storage and I've got to get out of here for a change.

 

[cmmrfrds]

The first function can be replaced with a Case statement in Tsql.

(Case when dblValue > dblNotToExceed then dblNotToExceed else dblNotToExceed End) as rtvalue

Do NOT use space in object names.
"qry Update DealVolume RatePeriod"
"qryUpdateDealVolumeRatePeriod"

Convert uses "style" to indicate the format, which you need to look up in the documentation.

convert(datetime,DealVolume.VolDate,101)

But, why are you trying to convert the field DealVolume.VolDate? Is this a character field?

style 101 is format of mm/dd/yyyy

 

[lameid]

I am formating the string because a purcahse can be made during a month but for the type of pricing chosen may be for a month rather than a day. By convention I store all month prices on the first day of the month so I need to make some dates mm/1/yyyy. So 4/25/2005 becomes 4/1/2005. Then it can be joined to pricing. Catch-22 some pricing is daily. Since I am doing this on the afterupdate event, a trigger may be the better way to go.

 

[cmmrfrds]

I believe you can do what you need with standard functions.
getdate() is date/time as of now.

-- first day of next month.
select dateadd(M,1,getdate()) - day(getdate())+1
-- first day of this month.
select getdate() - day(getdate())+1

You could wrap this inside a case statement to make a decision on which date to use.

Case when mycode = 'beginmonth' then
mydate - day(mydate)+1
else mydate End

 

[lameid]

So much to learn so little time...

I guess the biggest struggle and caveat of an ADP is that you completely loose access to all Jet/Access functions in queries. Unless of course I am entirely wrong.

This means that all set processing using functions has to be accomplished with ADO Recordsets. Except of course functions that have equivalents in SQL server. Unless of course you make your own function library.

Wouldn't you gain a lot more flexibility by using an MDB front end, appropriate views and stored procedures on SQL server and SQL Pass through queries? Then you could still use all the ADO recordsets to directly hit the database. The only thing that I see an ADP giving is a means to manage SQL Server objects within Access.

Am I completely off my nut or is that about the size of it?

 

[cmmrfrds]

True you cannot use Access functions in TSQL queries, but that can be replaced on sql server either by build-in functions or some equivalent. You can create user defined functions (udf's) on sql server which can be used in queries.
The Forms and Reports in an ADP are setup to easily use SQL Statements, Stored Procedures, and tables.
I have done many of both types of apps and if all the tables are going to be kept in sql server, then I would make it an ADP, otherwise an MDB. There are other threads on this Forum that discuss ADP versus MDB, do some searching.