Using conditions in SQL...help!

[ConfusedA]

This question is directly related to this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=174814

I am trying to figure out how to code in a query so that I can apply one of two numbers to a record based on it's seasonal value (which is determined by the month given for each record.)

There are 3 important fields that tie into this.
Jobtype- Some jobs have their own code associated with them, while others (the ones I'm working with) have a code based on summer or winter work.
Month- This is important in determining the seasonal code when applicable.
GL Number (or job code)- This is the field that I need to have the code associated with and be able to insert the proper value into.

This is the current code I have tried in SQL, I know there are problems with it, but I am unsure of what they are. Help is appreciated.

SELECT tblTruckValues.Truck, tblTruckValues.Jobtype, tblTruckValues.JobHour, tblTruckValues.fldYear, tblTruckValues.fldMonth, tblGlnumber.[GL Number] Or Iff(IsNull(tblGlnumber.[Gl Number]) THEN
If [tblmonnfthio.fldMonth = 12 OR tblmonthinfo.fldMonth < 3 THEN
[GL NUMBER] = "Winter Code2"
Else [GL Number] = "Summer Code1"
End If End If), [JobCost]*[JobHour] AS PRICE
FROM (tblGlnumber INNER JOIN (tblTruckValues INNER JOIN tblmonthinfo ON tblTruckValues.fldMonth = tblmonthinfo.fldMonth) ON tblGlnumber.[Job Type] = tblTruckValues.Jobtype) INNER JOIN tblCost ON tblTruckValues.Truck = tblCost.Truck;

 

[namliam]

Try reading up on IIF, IIF doent have THEN or ELSE or END IF parts

IIF(Logical, true, false)
Is the proper syntax...

Good luck

 

[DCrake]

SELECT tblTruckValues.Truck, tblTruckValues.Jobtype, tblTruckValues.JobHour, tblTruckValues.fldYear, tblTruckValues.fldMonth, tblGlnumber.[GL Number] Or [B]Iff(IsNull(tblGlnumber.[Gl Number]) THEN 
If [tblmonnfthio.fldMonth = 12 OR tblmonthinfo.fldMonth < 3 THEN 
[GL NUMBER] = "Winter Code2" 
Else [GL Number] = "Summer Code1"
End If End If)[/B], [JobCost]*[JobHour] AS PRICE
FROM (tblGlnumber INNER JOIN (tblTruckValues INNER JOIN tblmonthinfo ON tblTruckValues.fldMonth = tblmonthinfo.fldMonth) ON tblGlnumber.[Job Type] = tblTruckValues.Jobtype) INNER JOIN tblCost ON tblTruckValues.Truck = tblCost.Truck;

All this is not acceptable in the context you are using it.

Iff(IsNull(tblGlnumber.[Gl Number]), 
Iff([tblmonnfthio[B][COLOR="Red"]][/COLOR][/B].fldMonth = 12 OR [tblmonthinfo].fldMonth < 3, "Winter Code2" ,"Summer Code1"
))

This has not been checked but is in the correct format. This is called nested Iff's. You will also notice that the closing square bracket was missing.

David

 

[ConfusedA]

Thanks to you both, I was having a horrible time trying to wrap my head around that code.

IIf(IsNull([tblGlnumber].[Gl Number]),IIf([tblmonthinfo].[fldMonthnumber]=12 Or [tblmonthinfo].[fldMonthnumber]<3,"Winter Code2","Summer Code1"))

is working, in the sense that I am not getting any errors. But I am also not getting any values. I get values when I remove the code, could I have messed up something else in my query to have caused this? I checked to make sure that tbl.monthinfo and fldMonthnumber are the proper locations. tbl.monthinfo has a relationship to tbl.truckvalues where fldmonth is selected, could this relationship be causing my lack of values?

 

[DCrake]

Think about what you are actually asking it to do...

IIf(IsNull([tblGlnumber].[Gl Number]),IIf([tblmonthinfo].[fldMonthnumber]=12 Or [tblmonthinfo].[fldMonthnumber]<3,"Winter Code2","Summer Code1"))

Is there a value in the GI Number field[/COLOR]

If True

[COLOR="seagreen"]Is the fldMonthnumber = 12 or < 3[/COLOR]

    If True
        [COLOR="seagreen"]then it is a winter code[/COLOR]    
    Else
        [COLOR="seagreen"]then it is a summer code[/COLOR]
    End If
Else
    This is where it falls down. You have not told it what it is if it is not null

End If


David

 

[ConfusedA]

In all other queries I have created I have used an , "*", [tblGLvalues]![GL Values] but I am unsure of how to make this work so that it places the given GL value if there is one.

 

[DCrake]

Ok, Lets revert to a Function to get the answer

Copy this function into a module

Public Function GetSeason(GINumber As Integer,SummerCode1 As String, WinterCode1 As String) As String

Select Case GINumber
    Case 0 :GetSeason = "Unknown"
    Case < 3 :GetSeason = WinterCode1
    Case 12 :GetSeason =  WinterCode1
    Case Else :GetSeason = SummerCode1
End Select

End Function

Next in your query use the following syntax

Price:GetSeason(Nz([GL Number],0),[Winter Code1],[Summer Code1])

By passing both codes and the GL Number to the function it is able to return the correct value. The Nz() contends with Null values. Don't know how you want to represent this, not defined.


David

 

[ConfusedA]

Hmm, I tried this and it has added two pop-up boxes asking for Winter Code1 and Summer Code1 parameter values. I this what we are trying to do?

 

[DCrake]

From the nature of your question I assumed that you needed find out what the correct winter/summer code was not the actual text. What is Summer/Winter code?

Very confusing

David

 

[ConfusedA]

Sorry, I'm confused too. What I am trying to do is have it place into the field "Summer code", "Winter code" or it's given GL value based on the job type and month. So that I'll have output like:
Job Month Month# GL#
Road1 Jan 1 GL1234
Private Mar 3 Winter Code
Road2 June 6 GL1234
Private July 7 Summer Code

Etc.

 

[ConfusedA]

Thats why I had that initial complicated mess of if statements. In simple terms I want:
If theres a GL value for the job, put it in to the GL field.
Otherwise, If month = dec to march, then put Winter Value into GL field.
Else put summer value into GL field.

I just don't understand the code behind that.

 

[namliam]

If you call the function like David put it...
Price:GetSeason(Nz([GL Number],0),[Winter Code1],[Summer Code1])

It will ask for the code...
Price:GetSeason(Nz([GL Number],0),"Winter Code1","Summer Code1")

You need to send strings/texts into the function...

 

[ConfusedA]

sorry about this, I think I've managed to make the situation far more confused than it needs to be.

I think I understand what this code does now, correct me if I'm wrong; it shows you the results that have 'winter code' and 'summer code'?

My problem is that the code is not actually applying the 'summer code' or 'winter code' identification to the record, nor is it showing the jobs with existing values.

 

[ConfusedA]

Update: I got the code to work properly. DCrake the code is flawless for those that do not have a valid GL number! So thanks a ton for that!
There's just one piece of the puzzle that I cannot figure out. GL : IIf(IsNull([tblGlnumber].[Gl Number]),IIf([tblmonthinfo].[fldMonthnumber]=12 Or [tblmonthinfo].[fldMonthnumber]<4," winter code"," summer code"))
Adding something like:
, [tblGlnumber].[Gl Number] to pick up existing GL values where ISNull is false. But I cannot figure out where I can put this expression without producing an error of some type.

 

[ConfusedA]

Final Solution: In working order.

As a new field:
GL : IIf(IsNull([tblGlnumber].[Gl Number]),(IIf([tblmonthinfo].[fldMonthnumber]=12 Or [tblmonthinfo].[fldMonthnumber]<4,"winter code","summer code")),[tblGlnumber].[Gl Number])

 

[namliam]

Just a random question... tblMonthInfo??
MonthNumber??

What exactly are you doing there?? Do you know about the "Month" function for example?

 

[ConfusedA]

Hi there, sorry for not replying back right away, I took a few days off and away from this project.
I do know about the month function and my problem is that I am going by year/month/week and not day. I suppose I could have used day 1-5 to represent each possible week but the rest of my database is set up differently. The solution I've created for this problem is working though, so thank you all for helping me, a mix between the answers here, searching the forum some more and a little outside intervention have yielded a working process. Of course I am no where near complete on this project.

I have a new problem that involves using months, figured I'd post it here before I turn it into a whole new post. This is for a somewhat unrelated (at the current time) section of my database. I am trying to figure out how to list the amount of work (cost wise) per job done.
I have a table of employee jobs in which each job an employee does can be submitted with appropriate hours, then I have a query which calculates the price (employee's wage) for each job.

My problem is that I want to be able to add up all the costs for each job type to display a subtotal of each job type. I have created a subquery which adds up all the job costs, this works for adding costs up when an employee does the same job over a period of time. This is limited by the use of multiple employees as a job is split by employee even when they are not in the subquery. Is there a way to get around this?

Thanks again!
ConfusedA