Access Application to SQL
Access Application to SQL
(OP)
I come from a Networking background so please excuse if this is a really straight forward question as I have no experience of Access or VB and am on a steep learning curve with this project. I am trying to convert a old Access application into a SQL Database with a Visual Basic Web Front end. One of the things I want to do is to change the Access queries to SQL Views. I have been successful with a converting quite a few of them but the 3 statements below are confusing me I have used the help on SQL and the Net to look for answers but am still not getting anywhere. Any Help would be extremlly usefull also a brief explaination of where I am going wrong.
Select Package,
IIf([CertificateSent],"Yes","No") AS [Certificate Sent], -- (I Know I need to use the case statement here but I am expecting a value to = something ELSE '' not just Yes, No)
Sum([Activity]* 1000^[ActivityFactor]*[Quantity]) AS [Total Activity], -- (Dont understand the ^ function yes I have used the help but still dont get it )
[PackageNumberType] & "/" & [PackageNumberYear] & "/" & Format([PackageNumberMonth],"00") & "/" & Format([PackageNumberSequence],"000") AS [Package Number], , (I know I need to replace the & with + but its the format function for the package Numbersequence thats troubling me I try to format it as a string but still get the error message 'Format' is not a recognized built-in function name.)
FROM tblCustomer INNER JOIN tblContentType
RIGHT JOIN tblPackageContent ON tblContentType.ContentTypeID = tblPackageContent.ContentTypeID
RIGHT JOIN tblPackage
Select Package,
IIf([CertificateSent],"Yes","No") AS [Certificate Sent], -- (I Know I need to use the case statement here but I am expecting a value to = something ELSE '' not just Yes, No)
Sum([Activity]* 1000^[ActivityFactor]*[Quantity]) AS [Total Activity], -- (Dont understand the ^ function yes I have used the help but still dont get it )
[PackageNumberType] & "/" & [PackageNumberYear] & "/" & Format([PackageNumberMonth],"00") & "/" & Format([PackageNumberSequence],"000") AS [Package Number], , (I know I need to replace the & with + but its the format function for the package Numbersequence thats troubling me I try to format it as a string but still get the error message 'Format' is not a recognized built-in function name.)
FROM tblCustomer INNER JOIN tblContentType
RIGHT JOIN tblPackageContent ON tblContentType.ContentTypeID = tblPackageContent.ContentTypeID
RIGHT JOIN tblPackage
Talk To Other Members
RE: Access Application to SQL
try:
case when CertificateSent = 0 Then 'No' Else 'Yes' end As [Certificate Sent]
^ is the power symbol in Jet SQL.
John
RE: Access Application to SQL
In In SQL Server T-SQL, you use the power keyword. i.e. SELECT power(2,3) returns 8.
FORMAT is an ACCESS keyword that formats your output. And from what I have found is that you want to format numerics, boolean values and dates in your front end program.
Look here for lots of info on how to convert Access to SQL Server:
http://