Date-Conditional calculations

[nedstar1]

Hi friends,

I'm working on a query that will calculate a total based on an order's date. If the order is/was placed before 7/15, then they receive a certain flexible percentage of free overproduction. If the order is placed after this date, they receive a different quantity of overproduction, for which they are charged.

Basically, I want to make a calculation that will first call a date variable and compare it to 7/15/2004, and if it is on or before that date, to add a percentage called from a different table to the total order quantity but not to the order price. If the date is 7/16/2004 or after, the total quantity would increase by another percentage from a different table and the order price would increase accordingly.

Should I approach this as two queries? I'm starting off in that direction to see what happens when I try to build the comparison query. I could really use some help on this - I've got a nice form going for my users, but I need to get a few calculations figured out before it is of any real use, and I'm a bit overwhelmed by the query process. No hablo SQL, but I am eager to learn.

TIA
Regards,
Nedstar1

 

[amorous]

You can do this in a single query using CASE statements...

-VJ

 

[nedstar1]

I'll F1 it, thanks.

Nedstar1

 

[amorous]

Let me give you an example...

SELECT field1, field2,
CASE
WHEN yourdatefield<=7/15/2004
THEN
Ordertotal= yourcalculation
WHEN yourdatefield>7/15/2004
THEN
Ordertotal=othercalculation
END
FROM YourTableName

-VJ

 

[nedstar1]

Hi Amorous,

Thanks for the follow-up. This really helps!

I'm going to try it now - I'll post back if it works the way I want it to.

Thanks again for the followup

Nedstar1

 

[nedstar1]

Here's how I have it set up - can you tell me what I've got wrong in my syntax?

SELECT tblJobs.JobCreationDate, tblJobs.JobQty, tblJobVarious.JobVarFreeOvers, tblJobVarious.JobVarOvers
CASE
WHEN tblJobs.JobCreationDate<=7/15/2004
THEN
tblJobVarious.QtyWOvers=tblJobs.JobQty+(tblJobs.JobQty*tblJobVarious.JobVarFreeOvers)
WHEN tblJobs.JobCreationDate>7/15/2004
THEN
tblJobVarious.QtyWOvers=tblJobs.JobQty+(tblJobs.JobQty*tblJobVarious.JobVarOvers)
END
FROM tblJobs LEFT JOIN tblJobVarious ON tblJobs.JobID = tblJobVarious.JobID;


I'm guessing it has to do with parentheses - I'm going back to my Access 2003 Bible to try to find out about them.

TIA
Regards,
Nedstar1

 

[PHV]

This is ANSI SQL, not JET SQL.
Take a look at the IIf function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

probably need the date delimiters:

SELECT tblJobs.JobCreationDate, tblJobs.JobQty, tblJobVarious.JobVarFreeOvers, tblJobVarious.JobVarOvers
CASE
WHEN tblJobs.JobCreationDate<=#7/15/2004#
THEN
tblJobVarious.QtyWOvers=tblJobs.JobQty+(tblJobs.JobQty*tblJobVarious.JobVarFreeOvers)
WHEN tblJobs.JobCreationDate>#7/15/2004#
THEN
tblJobVarious.QtyWOvers=tblJobs.JobQty+(tblJobs.JobQty*tblJobVarious.JobVarOvers)
END
FROM tblJobs LEFT JOIN tblJobVarious ON tblJobs.JobID = tblJobVarious.JobID;

Leslie

 

[nedstar1]

Thanks for the quick responses.

PHV - I'm so newb I don't know the difference between ANSI and JET SQL. I'll look in to the IF statement after I post this.

LesPaul - thanks for the tip - I entered the date delimiters as you demonstrated above, but I'm still getting the same error:
"Syntax error (missing operator) in query expression yada yada yada"

To my eyes, it should work. The only thing I can think of that could be wrong w/r/t the fields is that both JobVarFreeOvers and JobVarOvers are bother formatted as percentages - could this cause some problems? I figured that if I multiply the original quantity by the percentage,a nd add it to the original quantity, I'd have a correct calculation - that sure seems right to me, and that's how I wrote the calc formulas. I had help with the CASE statement, and while it seems relatively straightforward, I could be misunderstanding its use.

Any further feedback is welcome and appreciated.

TIA

Regards,
Nedstar1

 

[lespaul]

are you trying to update the QtyWovers field? that's what this looks like:

tblJobVarious.QtyWOvers=tblJobs.JobQty+(tblJobs.JobQty*tblJobVarious.JobVarFreeOvers)

or are you just trying to select information from the table?

leslie

 

[nedstar1]

I'm trying to update the QTYWOvers field. The User will enter in the tblJob.JobQty quantity, and the tblJob.JobCreationDate. There are two fields on a subform for the entry of Overs - overproduction. If they place the order by 7/15, they get as much as 3% overs for free. After that, we can produce up to 5% overs and charge for them.

Do you see what I'm trying to do? I want this field to reflect the revised quantity with overs, based on the date-based calculations. I will then use the same date criteria to either bill by original quantity (if the overs were free) or by the Original+overs if after the cutoff (when we are allowed to charge for overs).

Thanks for replying so quickly - I'm sure I'm close to having this resolved, thanks to the good folks on Tek-Tips.

Any suggestions? Got another way to approach it?

TIA
Regards,
Nedstar1

 

[PHV]

I'll look in to the IF statement
NO, the IIf function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

If you are trying to update a field, your syntax is all wrong. The UPDATE statement is :

UPDATE TABLENAME SET FIELDNAME = "Something"

so you would need something like:

UPDATE tblJobVarious, tblJobs SET QtyWOvers = iif(tblJobs.JobCreationDate<=#7/15/2004#, tblJobs.JobQty+(tblJobs.JobQty*tblJobVarious.JobVarFreeOvers), JobQty+(tblJobs.JobQty*tblJobVarious.JobVarOvers))

Leslie

 

[nedstar1]

Hi friends,

PHV - Thanks for the clarification - I found the IIF help files - very confusing, but I'm plodding through the example and all will be clear someday.

LesPaul - thanks for the help - this statement doesn'yt get kicked out of the SQL editor, but I am still a bit flummoxed. I was sure I had bound the query I was building to the form, and I went through the process of updating the field, and it stopped and prompted me for the values for the query. Where do I need to put this statement? In an event field for some field or another location?

Headed home from work soon, but will be checking back, as I am under the gun and need to resolve these past few errors to feel comfy about it.

TIA everyone,

Regards,
Nedstar1