×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Access Application to SQL

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   
 

RE: Access Application to SQL

To give you some assistance:
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 SQL Server T-SQL the "^" is the Bitwise Exclusive OR.

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://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close