Access Application to SQL 1

[Bubsa]

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

 

[gmmastros]

(I Know I need to use the case statement here but I am expecting a value to = something ELSE '' not just Yes, No)

Can you please explain a little more what you mean by this. Specifically, can you show some sample values in the CertificateSent column, and then show the expected output?

The ^ operator in SQL Server is a bitwise exclusive or. I think the ^ operator in Access is power. So (I think) you are looking for this...

Sum([Activity]* [!]Power([/!]1000[!],[/!][ActivityFactor][!])[/!]*[Quantity]) AS [Total Activity]

There is no Format function in SQL. In VB, you can use the format function to add leading zero's to a number. In VB... Format(2, "000") returns "002". There are ways to do this in SQL Server, too. But... there's no built-in function that is as simple as VB's. The simplest way to accomplish this in SQL Server is to use the Right function. Ex:

Select Right('000' + YourColumnName, 3)

It CAN get a little more complicated than this, though. If your column can contain NULL, the code I show above will return NULL. You can accommodate the null values like this.

Select Right('000' + Coalesce(YourColumnName, ''), 3)

Coalesce will return the first non-null parameter. So, if YourColumnName is not null, it will be returned. If it is NULL, you will get the second parameter, which is an empty string.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[djj55]

SQL will usually translate the Yes and No into 1 and 0 (which when viewed by Access it will be -1 and 0). In .NET I believe Yes is 1.

Hope this helps


djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[Bubsa]

Hey Gmmastros

Many Thanks for your help and your very clear explaination. Regarding the Case statement I think its me grasping the logic behind it. The Certificate sent column is a checkbox so the data is either yes or no.

See I can fully understand the logic behind this statement.

SELECT County, CASE County
WHEN 'Kent' THEN Sales * 2
WHEN 'Essex' THEN Sales * 1.5
ELSE Sales
END
"New Sales",
Date
FROM Store_Information

But How can I change this into a case statment
IIf([CertificateSent],"Yes","No")

Or even a more compliucataed example again from the Access Application.

Format(IIf(Sum([Activity]*1000^[ActivityFactor]*[Quantity])/1000000<1,Sum([Activity]*1000^[ActivityFactor]*[Quantity])/1000,IIf(Sum([Activity]*1000^[ActivityFactor]*[Quantity])/1000000000<1,Sum([Activity]*1000^[ActivityFactor]*[Quantity])/1000000,Sum([Activity]*1000^[ActivityFactor]*[Quantity])/1000000000)),"Fixed") AS [Total Activity], IIf([Beqs]/1000000<1,"KBq",IIf([Beqs]/1000000000<1,"MBq","GBq")) AS Factor

 

[gmmastros]

It appears as though your CertificateSent column is a bit data type. If so, then this should work.

Select Case When CertificateSent = 1 
            Then 'Yes'
            Else 'No'
            And As [IsCertificateSent]

As djj55 said, true in a bit column is represented by a 1, false is a 0.

One thing that may be confusing you.... there are actually 2 ways to write a case statement. You listed one of them already.

[tt][blue]
SELECT County,
CASE County
WHEN 'Kent' THEN Sales * 2
WHEN 'Essex' THEN Sales * 1.5
ELSE Sales
END As [New Sales],
Date
FROM Store_Information
[/blue][/tt]

The other way is....

[tt][blue]
SELECT County,
CASE WHEN County = 'Kent' THEN Sales * 2
WHEN County = 'Essex' THEN Sales * 1.5
ELSE Sales
END As [New Sales],
Date
FROM Store_Information
[/blue][/tt]

Some times, you can use either method (they both work). But sometimes you really need to use the 2nd method because it gives you more flexibility. Because of this, I almost never use the first method.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom