Update Query using Case 1

[quest4]

I'm new at this, but here goes nothing. I am use Access 2000, and I'm trying to create an update query for my dbase. I have 3 date field and 3 memo filds, WarningDate1, WarningReason1, WarningDate2, WarningReason2, WarningDate3 and WarningReason3. What I need to do is set to "" the date field and the memo field when the date field is 91 days old, which is Date() - 91. I could use three separate update queries, but I thought maybe case may work, instead. I tried the Access & VB Help and no luck. I tried almost all of the formums and no luck. I did use case once or twice in a UNIX shell script, but that is much different than Access. What I need is a little push start on this puppy, any suggestion on how to start it off? Thanks in advance.

 

[tlbroadbent]

Access SQL doesn't have a CASE statement. It does have IIF which you may be able to use. I'm unclear about the actual update that occurs in your queries. If you post a query (or maybe all 3) someone could determine if they could be combined into 1 query, using IIF or other functionality.

You could build a query statement in VBA using the VB SELECT CASE statement. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[quest4]

VB Select case is what I was trying to figure out how it works. If I could see just a rough idea of it I could probably figure out the rest, but I haven't seen anything anywhere on it, other than a format page in VB help. Thanks.

 

[tlbroadbent]

Select Case Statement Example from Access Help
This example uses the Select Case statement to evaluate the value of a variable. The second Case clause contains the value of the variable being evaluated, and therefore only the statement associated with it is executed.

Dim Number
Number = 8 ' Initialize variable.
Select Case Number ' Evaluate Number.
Case 1 To 5 ' Number between 1 and 5, inclusive.
Debug.Print "Between 1 and 5"
' The following is the only Case clause that evaluates to True.
Case 6, 7, 8 ' Number between 6 and 8.
Debug.Print "Between 6 and 8"
Case 9 To 10 ' Number is 9 or 10.
Debug.Print "Greater than 8"
Case Else ' Other values.
Debug.Print "Not between 1 and 10"
End Select

My example to build a SQL statement:

Sub BuildSQLWithSelectCase(DateVal As Date)
Dim sSql As String

sSql = "Update table1 Set "

Select Case DateDiff("d", DateVal, Date)
Case 0 To 30
sSql = sSql & " Reason1 = '" & txtReason & "',"
sSql = sSql & " Date1 = date()"
Case 31 To 60
sSql = sSql & " Reason2 = '" & txtReason & "',"
sSql = sSql & " Date2 = date()"
Case Is > 60
sSql = sSql & " Reason3 = '" & txtReason & "',"
sSql = sSql & " Date3 = date()"
End Select

Debug.Print sSql
DoCmd.RunSQL(sSql)

End Sub Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[quest4]

Great example, I think I could modify it and use it someplace else, in another dbase. But not in this one, here is my first update query and maybe this will show you more clearly what I am trying to do, clear out fields.
UPDATE tblEmployees SET tblEmployees.DateWarning1 = Null And [tblEmployees.ReasonWarning1]=""
WHERE (((tblEmployees.DateWarning1)>Date()-"91&quot);

There are two more just like it, only the names have changed. The "91" is the number of days I'm trying to subtract from todays date.

 

[tlbroadbent]

It is possible to write one query. I think I would opt for a simpler method of three queries and execute them from code or from a macro. However, here is a query that handles all the conditions in one pass.

UPDATE tblEmployees
SET
DateWarning1 = IIf([DateWarning1]<Date()-91,Null,[DateWarning1]),
ReasonWarning1=IIf([DateWarning1]<Date()-91,Null,[ReasonWarning1])
DateWarning2 = IIf([DateWarning2]<Date()-91,Null,[DateWarning2]),
ReasonWarning2=IIf([DateWarning2]<Date()-91,Null,[ReasonWarning2])
DateWarning3 = IIf([DateWarning3]<Date()-91,Null,[DateWarning3]),
ReasonWarning3=IIf([DateWarning3]<Date()-91,Null,[ReasonWarning3])
WHERE tblEmployees.DateWarning1<Date()-91
OR tblEmployees.DateWarning2<Date()-91
OR tblEmployees.DateWarning3<Date()-91

As you can see, the query updates the columns with the current value if the criteria is not met. Therefore, this query is not a very efficient way to do this update. But three passes isn't very efficient either. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[quest4]

Fantasic, it works great. Thanks a million.