Solved Case Statement to get value in Access Query

[hrdpgajjar]

Hi all,
I have table name Application which consists of following columns,
1. WO Date
2. TPA Date
3. TR Date
4. Invoice Date
5. Current Stage

Now i want to use Case statement to get Current Stage value based on all above 4 columns,

For Example,
If Invoice Date is null and all above 3 fields have dates entered then current stage is "TR DONE"
if invoice Date and TR Date fields are null and rest of above 2 fields have dates then current stage is "TPA Done" so on...

I have tried to enter query in "Onload " of form but not able to do it.

how can i do this?

thanks

 

[arnelgp]

i don't think you need Current Stage field on your table. you can create a Query and use the query on your form:

SELECT [WO Date], [TPA Date], [TR Date], [Invoice Date], Switch(IsNull([Invoice Date]) And 
Not IsNull([WO Date]) And Not IsNull([TPA Date]) And Not IsNull([TR Date]), "TR DONE", 
IsNull([Invoice Date]) And IsNull([TR Date]) And Not IsNull([WO Date]) And Not IsNull([TPA Date]), "TPA DONE", 
True, Null) As [Current Stage] FROM YourTableName;

 

[cheekybuddha]

Arnel has given you a "quick'n'dirty" solution.

If you are interested in a better solution (that requires a bit more effort on your part) then consider normalising your table properly.

This would require a table for holding all your different possible stages, and then a junction table to link applications to their stages.

Something like:

Applications          ApplicationStages           Stages
------------          -----------------           -------
ID (PK)      >--|     ID (PK)                |--< ID (PK)
...             |---< ApplicationFK          |    Stage
...                   StageFK           >----|
                      Date

 

[DHookom]

I’m with David regarding normalizing although I prefer a naming convention that would not have multiple “ID” fields and “Date” as a field name.

 

[mikenyby]

I agree with the normalization method listed above, but if you don't want to go through that trouble you can also make current stage a calculated field in the table. You can nest a few IIF statements together to get the desired result.
If your data is stored in back end tables on SQL Server or some other more competent database engine, you can create a computed field in the back end and use the SQL CASE statement to do what you're looking to do.

If this database is not going to have a lot of production utility, this may be an adequate method. If it's going to get more mileage, definitely look at normalizing.

I'm also on team @DHookom regarding naming conventions. ID fields without the table names can be a little confusing when writing joins.

 

[Gasman]

Confuses Access as well.

 

[mikenyby]

Confuses Access as well.

Joins in Access are obnoxious to begin with. The only place you'll ever see so many RIGHT JOINs in the wild.

 

[GPGeorge]

Joins in Access are obnoxious to begin with. The only place you'll ever see so many RIGHT JOINs in the wild.

The query designer in Access is a graphical UI, which has to be translated into SQL. That does often result in awkward SQL, to be sure.

For someone who is serious about writing good, clean SQL statements, there's always the SQL editor. And in the very near future, the Monaco SQL Editor will be available.

 

[cheekybuddha]

I prefer a naming convention that would not have multiple “ID” fields and “Date” as a field name.

I couldn't agree more about naming a field 'Date' - never do it!

However, whilst everyone is free to have their opinions, I feel that the objection to 'ID' is misguided and usually a bit cargo-cultist.

If you are SELECT'ing from a single table, then 'ID' is more than adequate, and actually has the effect of giving the field no more meaning than it needs as a unique identifier of a record/row:

SELECT
  ID,
  SomeField
FROM YourTable;

... and if you use the Query Builder that insists on qualifying everything even in queries based on a single table, you will get:

SELECT
  YourTable.ID,
  ...
 
-- instead of:
SELECT
  YourTable.YourTableID,
  ...

which just makes you think you've been drinking too much and are seeing double

If you are using it in a query with a join, it will always be qualified:

SELECT
  t.ID,
  t.SomeField
  l.LookupValue
FROM YourTable t
INNER JOIN LookupTable l
        ON t.LookupFK = l.ID
;

I find it more irritating to have to read/write:

SELECT
  Applications.ApplicationID
  Applications.SomeField,
  ...
FROM Applications
INNER JOIN ...

Naming the field ApplicationID serves no benefit here - it's obvious which table it's from or, if not, then you are using an alias that is not expressive enough.

Confuses Access as well.

Only when you try and use a field that could be from more than one table in an expression, since the stupid query builder decides that's the time when it can't be bothered to qualify a selected field.

Joins in Access are obnoxious to begin with.

The most obnoxious thing about Access joins is its requirement to nest each join in brackets, often leading to the join order being reversed or jumbled up completely

 

[cheekybuddha]

And in the very near future, the Monaco SQL Editor will be available.

But you will still have to use Jet/ACE stupid join syntax, no?

 

[GPGeorge]

But you will still have to use Jet/ACE stupid join syntax, no?

I prefer not to use pejoratives to describe behavior in software, but yes, Access SQL requires Access syntax, with superfluous parentheses.

 

[Pat Hartman]

The only place you'll ever see so many RIGHT JOINs in the wild.

Access doesn't force you to use Right Joins. It uses the order in which you add tables to a query to interpret whether you want a left or a right join. So, add the tables in a logical order or switch them later.

 

[hrdpgajjar]

i don't think you need Current Stage field on your table. you can create a Query and use the query on your form:

SELECT [WO Date], [TPA Date], [TR Date], [Invoice Date], Switch(IsNull([Invoice Date]) And
Not IsNull([WO Date]) And Not IsNull([TPA Date]) And Not IsNull([TR Date]), "TR DONE",
IsNull([Invoice Date]) And IsNull([TR Date]) And Not IsNull([WO Date]) And Not IsNull([TPA Date]), "TPA DONE",
True, Null) As [Current Stage] FROM YourTableName;

Sorry for the late reply, But this works for me as calculated field,

IIf(IsNull([Invoice Sent Date] and IsNull([TR Date] and IsNull([TPA Date] and Not IsNull([WO Date])))),"WO Received",IIf(IsNull([Invoice Sent Date] and IsNull([TR Date] and Not IsNull([TPA Date]))),"TPA Signed",IIf(IsNull([Invoice Sent Date] and Not IsNull([TR Date])),"Trial Run Done","Invoice Sent")))

 

[Pat Hartman]

You've been offered good advice but decided to ignore it.

If you are going to not normalize the data, then you should at least take advantage of the way the IIf() works in queries (but not in VBA proper). That is that the code exits as soon as a True is returned. Therefore, you test back to front for presence rather than front to back for absence.

IIf(IsDate(InvoiceSentDate), "Invoice Sent", IIf(IsDate(TRDate), "TR Date", IIf(IsDate(TPADate), "TPA Date", IIf(IsDate(WODate), "WO Date", "Unknown"))))

WARNING --- The IIf() in VBA evaluates all conditions so in VBA, I would use a nested If instead) This most frequently bites people when they are doing division and there is a possibility that the divisor might be 0. In VBA, the IIf() will get a divide by 0 error but not in a query due to the different execution profile.

Even better - if you are not going to do it right - is to create a function. That way, if the rules change, you can just change the function rather than every query or piece of code where you might have had to calculate this date.

 

[The_Doc_Man]

I'm with Pat on the idea of writing a function. It has the negative of forcing you to alternate between the GUI and SQL memory environments, but you have the ultimate flexibility in how you return your answer. But then, since IIF is actually a VBA function as well, it is six of one, half-a-dozen of the other. But making it a single function vs. a nested bunch of IIF statements means the function is SO much more efficient. Only one level of call for a function vs. layered/nested calls - already a win... but there is also the point that an IIF evaluates all sub-expressions whereas it is possible to write a function that only executes until it gets a resolution and then stops, so there is an expectation of less total work to get an answer.

 

[Pat Hartman]

But then, since IIF is actually a VBA function as well, it is six of one, half-a-dozen of the other.

Not really. If your BE is SQL server, the IIf() will be converted to a Case statement and sent to the server whereas a UDF cannot be sent to the server. It must be processed locally. So, Access sends the rest of the query to the server and then applies the UDF to the resultset before returning the final result to Access.

but there is also the point that an IIF evaluates all sub-expressions

That is the point I thought I carefully made. SQL does NOT evaluate all sub-expressions of the IIf(). It stops at the first true. Whereas, if you run the IIf() using VBA, VBA DOES evaluate all sub-expressions. You can validate this by using an IIf() that potentially divides by 0 so you have to check the contents of the divisor to avoid the divide by 0 error.

IIf(MyDivisor = 0, 0, MyDividend/MyDivisor) --- works fine in a query but gets a divide by 0 in VBA. In VBA, you must use a nested If or some other method instead of the IIf() to avoid the error.