473,854 Members | 1,537 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

I am trying to convert a MS Access Query to a SQL query and I need help?

6 New Member
I am trying to convert a MS Access Query to a SQL query and I need help with some IIF Statements?

2 in particular that I've encountered problems with are:

Count(IIf([elphoursflag]=0 Or [elphoursflag]=2,IIf([envelopes].isitecode=[logsheets].isitecode,IIf([recdate] Between DateAdd([d],7,[dFirstPSYDate]) And (DateAdd([d],13,[dFirstPSYDate])),[zip]))))'AS wk2count,

AND

Format(Avg(IIf([elphoursflag]=0 Or [elphoursflag]=2,IIf([envelopes].isitecode=[logsheets].isitecode,IIf([recdate] Between DateAdd([d],7,[dFirstPSYDate]) And (DateAdd([d],13,[dFirstPSYDate])),[elphours])))),[0.0])AS wk2avg,

Any help that someone could offer would be greatly appreciated!
Thanks!
Mar 8 '07 #1
1 2317
newtechiebug
6 New Member
After some more effort, I was able to get this working:
Count(case
when [elphoursflag]=0 Or [elphoursflag]=2 then
case
when [envelopes].isitecode=[logsheets].isitecode then
case
when [recdate] Between DateAdd([d],7,[dFirstPSYDate]) And (DateAdd([d],13,[dFirstPSYDate])) then
[zip]
end
end
end) AS wk2count,
Round(Avg(case
when [elphoursflag]=0 Or [elphoursflag]=2 then
case
when [envelopes].isitecode=[logsheets].isitecode then
case
when [recdate] Between DateAdd([d],7,[dFirstPSYDate]) And (DateAdd(day,13 ,[dFirstPSYDate])) then
elphours
end
end
end), 0.0) AS wk2avg,


I am trying to convert a MS Access Query to a SQL query and I need help with some IIF Statements?

2 in particular that I've encountered problems with are:

Count(IIf([elphoursflag]=0 Or [elphoursflag]=2,IIf([envelopes].isitecode=[logsheets].isitecode,IIf([recdate] Between DateAdd([d],7,[dFirstPSYDate]) And (DateAdd([d],13,[dFirstPSYDate])),[zip]))))'AS wk2count,

AND

Format(Avg(IIf([elphoursflag]=0 Or [elphoursflag]=2,IIf([envelopes].isitecode=[logsheets].isitecode,IIf([recdate] Between DateAdd([d],7,[dFirstPSYDate]) And (DateAdd([d],13,[dFirstPSYDate])),[elphours])))),[0.0])AS wk2avg,

Any help that someone could offer would be greatly appreciated!
Thanks!
Mar 9 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

12
8273
by: Guy | last post by:
I got a big Access file (1 400 tables) to convert to SQL and I would like to be provided with some automated tools, except upsizing wizard and DTS, to convert it on my own. I got a lot of forms and query to convert too. Can someday provide me with at least one tool name ? Thanks,
1
6600
by: ian m via SQLMonster.com | last post by:
Hi, I currently have a ms access update query that runs perfectly well and quicly in access however I now need to add this query to convert this qeryu to oracles equivelant sql syntax and add it to the end of an oracle sql script. Unfortunately Im not having much success although i seem to be able to convert it to a working oracle sql. it takes hours to run the statement in oracle where as in access it runs in seconds
2
15869
by: Bubba | last post by:
I know it's possible, just don't know how to do it. I have a spreadsheet that I imported into access. Two of the columns in the table have Hard Drive space values listed for example 2.45 GB and 453 MB, you get the picture. Both the GB and MB assorted values exist in both columns. Is there anyway to convert the GB or MB to 0's, or even better yet decimal places so I can look for values < or > to 6gb. I already tried a query, but it seems...
3
29045
by: jerry.ranch | last post by:
I have a need to convert simple dates (i.e. 02/14/2005) to a number, do some math, and convert back to a date. (in a simple query). The math involves adding or substracting days, and days of the week . I've used the weekday() function to convert dates to numberic days of the week (1-7) I've used cdbl (date) to convert a date to a serial number, but then I do math with the number and I can't seem to convert this back to a date.
29
2654
by: Jan | last post by:
Hi: I have an Access database that's been running (in one form or another) for a couple of different clients for a few years. Now a new client has requested that it be implemented with a SQL server back-end. I'm doing my best to learn about SQL server, and I plan to leave the front-end more or less as-is, just linking to the SQL server back end, but here's a basic question: The db has a front-end linked to two back-ends. One of the...
4
39363
by: perryclisbee via AccessMonster.com | last post by:
I have dates of service for several people that range all over each month. ie: patient had dates of service of: 7/3/2006, 7/24/2006 and 7/25/2006. I need to create a new field via a query that will convert each of the records of these service dates to the first date of that month, with results showing: 7/1/2006, 7/1/2006, 7/1/2006. How would you place an expression on a query that will convert any given date to the first day of the month...
0
2090
by: sebastiany | last post by:
Hi, I currently have a MS Access select query (pretty lengthy) that runs fine in MS Access, however I now need to convert this query to Oracle's equivelant SQL syntax so that I can create a SQL view in Oracle. Any help is appreciated!! Here's the MS Access select query: SELECT TC_PFIZER_T_PROJECT.NUMBER_STRING_UPPER, TC_PFIZER_T_PROJECT.NAME, TC_PFIZER_T_CONTACT.FIRST_NAME, TC_PFIZER_T_CONTACT.NAME,...
2
2692
by: TroutmansRegistrar | last post by:
I have a web programming task that has me stumped. I hope that one of you could give me some insight or might know someone who could guide me in the right direction. This is the final piece of the web application that my employer wanted finished by January 1st. Perhaps my biggest problem is that I can’t seem to formulate the right search-engine search question to find the utility I need nor can I find any reference to the real issue. In...
3
1027
by: PGM | last post by:
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim Projname, ProjSpon As String Dim AppName, AppType, AppSupp, AppDesc As String Dim VendName, VendPhone As String Dim ReqName, ReqPurp As String
0
9901
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10678
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10751
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9512
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7079
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5740
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5940
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4153
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3185
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.