I have the following SELECT within

TheRiver · Jan 20, 2004

I have the following SELECT within a module.

What I want to do is update the field census date based on the results of the Select Case.

So the module will update the field Census_date to the str_censusdate. But how do I write this in the Update query?

Select Case intAnswer
Case "Apr": str_censusdate = "20030430"
Case "May": str_censusdate = "20030531"
Case "Jun": str_censusdate = "20030630"
Case Else: Exit Function
End Select

strSQL = "UPDATE [WL CMDS (RNA00)] SET Census_Date = str_censusdate " & "WHERE Census_Date <> str_censusdate"
Create.Execute strSQL

MinusM · Jan 20, 2004

In Access SQL this can be done using IIF or Switch

IIF is better upto 3 conditions after that it becomes less legible
Syntax is
Switch(Condition1,Expression1,Condition2,Expression2...goes on)

strSQL = "UPDATE [WL CMDS (RNA00)] SET Census_Date = switch(intAnswer ="Apr"; "20030430";intAnswer ="May"; "20030531"
)" & "WHERE Census_Date <> switch(intAnswer ="Apr"; "20030430";intAnswer ="May"; "20030531"
)"

Best of luck

TheRiver · Jan 20, 2004

Is it not possible to somehow just pull across str_censusdate in the update bit?

All of this will be done within a module.

MakeItSo · Jan 20, 2004

This is assumed your Census_Date is a text field:

Code:

strSQL = &quot;UPDATE [WL CMDS (RNA00)] SET Census_Date = '&quot; & str_censusdate & &quot;' WHERE Census_Date <> '&quot; & str_censusdate & &quot;'&quot;
DoCmd.RunSQL strSQL

If your Census_Date is a Date/Time field, you must do it differently:

Code:

strSQL = &quot;UPDATE [WL CMDS (RNA00)] SET Census_Date = #&quot; & left(str_censusdate,4) & &quot;/&quot; & Mid(str_censusdate,5,2) & &quot;/&quot; & right(str_censusdate,2) & &quot;# WHERE Census_Date <> str_censusdate&quot;
DoCmd.RunSQL strSQL

Does this help?
Regards,
MakeItSo

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

TheRiver · Jan 20, 2004

That worked great 'Makeitso' thanks for your help.

TheRiver · Jan 20, 2004

More help needed. As part of this module, before I do the update I import data from a txt file. What I want to be able to do is only update the newly imported files and not things that already exist within the table. Is this possible? If so how?

MakeItSo · Jan 20, 2004

Do you import directly to the table or into a temp table first?
If not: you could do so and then use this temp table as query criterion:

strSQL = "UPDATE [WL CMDS (RNA00)] SET Census_Date = #" & left(str_censusdate,4) & "/" & Mid(str_censusdate,5,2) & "/" & right(str_censusdate,2) & "# " _
& "FROM (your table) a inner join (temp table) b " _
& "ON a.[RecordID] = b.[RecordÍD] " _
& "WHERE Census_Date <> str_censusdate"

Adapt the names "your table" and "temp table" as well as [RecordID] to your respective table/field names.

That will do the job. ;-)

Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

TheRiver · Jan 20, 2004

I used this to get what I need. Another question is, is it posiible to somehow replace the 20030430 with a standard format to save me going into the module each year and updating it?

Select Case intAnswer
Case "Apr": str_censusdate = "20030430"
Case "May": str_censusdate = "20030531"
Case "Jun": str_censusdate = "20030630"
Case "Jul": str_censusdate = "20030731"
Case "Aug": str_censusdate = "20030831"
Case "Sep": str_censusdate = "20030930"
Case "Oct": str_censusdate = "20031031"
Case "Nov": str_censusdate = "20031130"
Case "Dec": str_censusdate = "20031231"
Case "Jan": str_censusdate = "20040131"
Case "Feb": str_censusdate = "20040228"
Case "Mar": str_censusdate = "20040331"
Case Else: Exit Function
End Select

Create.Execute "UPDATE [WL CMDS (RNA00)] SET Census_Date = '" & str_censusdate & "' WHERE Census_Date <> '" & str_censusdate & "'"

MakeItSo · Jan 20, 2004

Try this:

Code:

Dim mon as string, lastd as string
mon=Month(&quot;2004-&quot; & intanswer & &quot;-30&quot;)
if len(mon)=1 then mon = &quot;0&quot; & mon
select case mon
case &quot;02&quot;
  lastd=&quot;28&quot;
case &quot;01&quot; or &quot;03&quot; or &quot;05&quot; or &quot;07&quot; or &quot;08&quot; or &quot;10&quot; or &quot;12&quot;
  lastd=&quot;31&quot;
case else
  lastd=&quot;30&quot;
str_censusdate=Year(Date()) & mon & lastd

Only trouble here: What if Feb has 29 days? You could catch this with If Year(Date()) \ 4 = 0 AND Year(Date()) \ 100 <> 0 then
lastd="29"
Else
lastd="28"

;-)

MakeItSo · Jan 20, 2004

P.S: You can leave "2004" in this case, even if you switch to 2005... It's just to get the month value of Apr, Mar ...

TheRiver · Jan 20, 2004

where would I put this around the existing code?

MakeItSo · Jan 20, 2004

Just replace your entire select case block with it.
So your code block from above will look like this:

Code:

Dim mon as string, lastd as string
...
mon=Month(&quot;2004-&quot; & intanswer & &quot;-30&quot;)
if len(mon)=1 then mon = &quot;0&quot; & mon
if CInt(mon)<1 or CInt(mon)>12 then goto skipthis'No valid month
select case mon
case &quot;02&quot;
  If Year(Date()) \ 4 = 0 AND Year(Date()) \ 100 <> 0 then
   lastd=&quot;29&quot; 
  Else
   lastd=&quot;28&quot;
  End If
case &quot;01&quot; or &quot;03&quot; or &quot;05&quot; or &quot;07&quot; or &quot;08&quot; or &quot;10&quot; or &quot;12&quot;
  lastd=&quot;31&quot;
case else
  lastd=&quot;30&quot;
str_censusdate=Year(Date()) & mon & lastd

Create.Execute &quot;UPDATE [WL CMDS (RNA00)] SET Census_Date = '&quot; & str_censusdate & &quot;' WHERE Census_Date <> '&quot; & str_censusdate & &quot;'&quot;

...

skipthis:
End Function

TheRiver · Jan 20, 2004

It doesnt like 'skipthis'

MakeItSo · Jan 20, 2004

What do you mean: "Doesn't like"?
Have you inserted the line before End Function (with colon):
skipthis:
End Function

TheRiver · Jan 20, 2004

It works kind of now, but when I import data in which should have month on 20031130, it gives it a 20041130 figure

MakeItSo · Jan 20, 2004

Ooops, missed something:
Have another:

Code:

Dim yr as String

and replace this line:

Code:

str_censusdate=Year(Date()) & mon & lastd

with these:

Code:

if mon > Month(Date()) then 
  yr = CStr(CInt(Year(Date())-1))
else
  yr = CSrt(Year(Date()))
End If
str_censusdate=yr & mon & lastd

[afro]

TheRiver · Jan 20, 2004

It still giving problems, remember that I will be running these modules each month based on financial years.

So Apr03 to Mar04.

At the moment if I add Apr 03 data I get 20040430 and when I run Nov03 data I get 20031130!!!!

MakeItSo · Jan 20, 2004

based on financial years. So Apr03 to Mar04.
Didn't know that - have patience with non-business-men... ;-)
Anyway, there still was a little bug in there.
Replace
if mon > Month(Date()) then
yr = CStr(CInt(Year(Date())-1))
else
yr = CSrt(Year(Date()))
End If

with

[blue]
[green]'*****Version a: Up to for Jan: 2004, else 2003(monthly)[/green]
If CInt(mon) > Month(Date) Then
yr = CStr(CInt(Year(Date) - 1))
Else
yr = CStr(Year(Date))
End If

[green]'*****Version b: Up to Mar: 2004, else 2003 [/green](quarterly)
If CInt(mon) \ 4 > Month(Date) \ 4 Then
yr = CStr(CInt(Year(Date) - 1))
Else
yr = CStr(Year(Date))
End If

[/blue]

Hope this gives you correct results now.

P.S: Always back-up your db before doing mass-updates...
;-)

TheRiver · Jan 20, 2004

Still a bit more work!!!

when I load Jan 04 do I need to change anything?

When i load it at the moment I get result of 20040130 and when I load Mar 04 data I get 20030330

MakeItSo · Jan 20, 2004

OK. Concerning the March 03 problem:
replace
[red]
If CInt(mon) \ 4 > Month(Date) \ 4 Then
yr = CStr(CInt(Year(Date) - 1))
Else
yr = CStr(Year(Date))
End If
[/red]
with
[blue]
If (CInt(mon)-1) \ 3 > (Month(Date)-1) \ 3 Then
yr = CStr(CInt(Year(Date) - 1))
Else
yr = CStr(Year(Date))
End If
[/blue]

Concerning 30th of Jan instead of 31st:
Although the above code should work, you might try replacing
[red]
mon=Month("2004-" & intanswer & "-30&quot

[/red]
with
[blue]
mon=CStr(Month("2004-" & intanswer & "-30&quot

)
[/blue]

This is straining...

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

I have the following SELECT within

IS-IT--Management

MIS

IS-IT--Management

Programmer

IS-IT--Management

IS-IT--Management

Programmer

IS-IT--Management

Programmer

Programmer

IS-IT--Management

Programmer

IS-IT--Management

Programmer

IS-IT--Management

Programmer

IS-IT--Management

Programmer

IS-IT--Management

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor