Switch function

  • Thread starter Thread starter MyVi
  • Start date Start date
M

MyVi

Hi everyone.

Let's see. We have this:

if Date() = MONDAY then X =Date() (so, today)
if Date() = TUSDAY then X = Date() -1 (so, yesterday)
if Date() = WEDNESDAY then X = Date() - 2 (so, the day before
yesterday)
if Date() = THRUSDAY then X = Date() - 3
if Date() = FRIDAY then X = Date() - 4
if Date() = SATURDAY then X = Date() + 2 (so, day after tomorrow)
if Date() = SUNDAY then X = Date() + 1 (so, tomorrow)

NOTE: when I say "MONDAY" I mean that DATE() can be equal to a day that
could be monday, oct 31st of 2005. So we don't really mind the day,
month and year of the date but the weekday itself).


Well, what I want is to use the function Switch to make these
calculations and to get one of those resoults depending on DATE(). But
what I donno is how to write properly the function Switch to do so.

As well, what we have is (as first thing) this other function:

=DLookup("[CODIGO CLIENTE]";"CAL_RESULTADO";"[_DIA]=#31/10/2005#") ,

and I need to use the Switch function to get the correct DATE all the
time (instead of that #31/10/2005#).

So, what I need is to mount the Switch properly that will give me the
final resoult date to be into "[_DIA]= ....date...")


Any clue on how to do this?

Thank you

Vic
 
According to the Access Help file
Switch(expr-1, value-1[, expr-2, value-2 . [, expr-n,value-n]])
Function MatchUp (CityName As String)
Matchup = Switch(CityName = "London", "English", CityName _
= "Rome", "Italian", CityName = "Paris", "French")
End Function
So, you could mod this example function to pass in Date(), then compare it
in the switch function, and then return a value to your X.Personally, I
would stay as far away as possible from this type of hard-coded solution.
Instead, make it as table-driven as possible..02-- Steve Clark, Access
MVPhttp://www.fmsinc.com/consulting
 
MyVi said:
Hi everyone.

Let's see. We have this:

if Date() = MONDAY then X =Date() (so, today)
if Date() = TUSDAY then X = Date() -1 (so, yesterday)
if Date() = WEDNESDAY then X = Date() - 2 (so, the day before
yesterday)
if Date() = THRUSDAY then X = Date() - 3
if Date() = FRIDAY then X = Date() - 4
if Date() = SATURDAY then X = Date() + 2 (so, day after tomorrow)
if Date() = SUNDAY then X = Date() + 1 (so, tomorrow)

NOTE: when I say "MONDAY" I mean that DATE() can be equal to a day that
could be monday, oct 31st of 2005. So we don't really mind the day,
month and year of the date but the weekday itself).


Well, what I want is to use the function Switch to make these
calculations and to get one of those resoults depending on DATE(). But
what I donno is how to write properly the function Switch to do so.

As well, what we have is (as first thing) this other function:

=DLookup("[CODIGO CLIENTE]";"CAL_RESULTADO";"[_DIA]=#31/10/2005#") ,

and I need to use the Switch function to get the correct DATE all the
time (instead of that #31/10/2005#).

So, what I need is to mount the Switch properly that will give me the
final resoult date to be into "[_DIA]= ....date...")


Any clue on how to do this?

Thank you

Vic
Click to expand...

Vic,

Snytax:

Switch(expr-1, value-1[, expr-2, value-2 … [, expr-n,value-n]])

Example:

Public Function fDayOfWeek() As Date

Dim dtDayOfWeek As Date

'Add error handling.

dtDayOfWeek = Switch(DatePart("w", Date) = 2, Date, _
DatePart("w", Date) = 3, Date - 1)
' Just continue on with the rest of the Switch function.

fDayOfWeek = dtDayOfWeek

End Function


=DLookup("[CODIGO CLIENTE]";"CAL_RESULTADO";"[_DIA]=fDayOfWeek()) ,



Sincerely,

Chris O.
 
Chris, thanks. It is working already
Let me show you what I've done.

1st thing is to put the code. I'm not really sure where to put it BUT,
what I've done is to put it in the Form. Let's see. I have a Form where
a TextBox has the Function DLookUp. So, in this TextBox I've gone to
and event and once in the VBA code page of this textbox then I've wrote
this:

Public Function fDayOfWeek() As Date
Dim dtDayOfWeek As Date
dtDayOfWeek = Switch(DatePart("w", Date) = 2, Date, _
DatePart("w", Date) = 3, Date - 1, _
DatePart("w", Date) = 4, Date - 2, _
DatePart("w", Date) = 4, Date - 3, _
DatePart("w", Date) = 5, Date - 4, _
DatePart("w", Date) = 6, Date + 2, _
DatePart("w", Date) = 7, Date + 1)
fDayOfWeek = dtDayOfWeek
'MsgBox fDayOfWeek
End Function

This seems to be OK. (I've try to add one more line with 'MsgBox
fDayOfWeek to show me the final date, and it does what I need to).

Second: Well, after this what I've done is to go to the TextBox on that
Form and I've done this:

=DBúsq("[CODIGO CLIENTE]";"CAL_RESULTADO";"[_DIA]=fDayOfWeek")

Well, it works. In my case I use more column and it looks like this:

=DBúsq("[CODIGO CLIENTE]";"CAL_RESULTADO";"[_DIA]=fDayOfWeek AND
[C_M]<>No AND [A_IR]<>No")

There is just one tiny little point that does not work, but it does not
really matter.

DatePart("w", Date) = 7, Date + 1)
This last part, where it supposed to count for sunday date, it does not
work. So if the date() is sunday, it does not show anything. But it is
okey. When is monday it works again, and on satuday is working too. So
I won't work on sundays, so I don't really need it to work.

Thank you.

Vic
 
Depending on your system settings, Sunday is 1. Your posted version, seems
to have an error in it. You have two calculations for the value of 4. Only
the first one will ever return a value, since Switch works with the first
True event.


Chris, thanks. It is working already
Let me show you what I've done.

1st thing is to put the code. I'm not really sure where to put it BUT,
what I've done is to put it in the Form. Let's see. I have a Form where
a TextBox has the Function DLookUp. So, in this TextBox I've gone to
and event and once in the VBA code page of this textbox then I've wrote
this:

Public Function fDayOfWeek() As Date
Dim dtDayOfWeek As Date
dtDayOfWeek = Switch(DatePart("w", Date) = 2, Date, _
DatePart("w", Date) = 3, Date - 1, _
DatePart("w", Date) = 4, Date - 2, _
DatePart("w", Date) = 4, Date - 3, _
DatePart("w", Date) = 5, Date - 4, _
DatePart("w", Date) = 6, Date + 2, _
DatePart("w", Date) = 7, Date + 1)
fDayOfWeek = dtDayOfWeek
'MsgBox fDayOfWeek
End Function

This seems to be OK. (I've try to add one more line with 'MsgBox
fDayOfWeek to show me the final date, and it does what I need to).

Second: Well, after this what I've done is to go to the TextBox on that
Form and I've done this:

=DBúsq("[CODIGO CLIENTE]";"CAL_RESULTADO";"[_DIA]=fDayOfWeek")

Well, it works. In my case I use more column and it looks like this:

=DBúsq("[CODIGO CLIENTE]";"CAL_RESULTADO";"[_DIA]=fDayOfWeek AND
[C_M]<>No AND [A_IR]<>No")

There is just one tiny little point that does not work, but it does not
really matter.

DatePart("w", Date) = 7, Date + 1)
This last part, where it supposed to count for sunday date, it does not
work. So if the date() is sunday, it does not show anything. But it is
okey. When is monday it works again, and on satuday is working too. So
I won't work on sundays, so I don't really need it to work.

Thank you.

Vic
 
Back
Top