iif statement

[Guest]

I have a date leave time I am not sure how to word the iif satement to
reflect a currency amount. I already have a per diem field so I would like
to keep it like that, I would need to do an expression in the after update I
belive anyways this is what I am dealing with

IIF([Date Leave] between 12:00am and 9:00am then $42
between 9:01am and 2:00pm then $33
between 2:01pm and 11:00pm then $22. Could someone help me set this up.
Also I have a check box. Does not receive per deim. If this is checked
then the per diem fild is null.

Thanks

 

[Guest]

Assumming your [Date Leave] field is type date/time and formatted to hold
date and time, try this:

FormatCurrency(IIf(Format([Date Leave],"Long Time") Between #00:00:00# And
#09:00:00#,42,IIf(Format([Date Leave],"Long Time") Between #09:00:01# And
#14:00:00#,33,22)))


Delordson Kallon
http://www.instantsoftwaretraining.com/

 

[Guest]

Have you considered using a Select Case statement instead of IIF? It's much
cleaner to use (and maintain) when you have multiple options.

Select Case DateLeave
Case between #00:00:00# and #09:00:00#
currRate=42
Case between #09:00:01# and #14:00:00#
currRate=33
Case between #14:00:01# and #23:00:00#
currRate=22
Case Else
'handle exceptions here
End Select

 

[Guest]

Well I guess I made 2 mistakes. My field name is actually called Time Leave
and it is a text field. Will this still work. And where do I put this? Can
I still place it under afterupdate or in a query?

Have you considered using a Select Case statement instead of IIF? It's much
cleaner to use (and maintain) when you have multiple options.

Select Case DateLeave
Case between #00:00:00# and #09:00:00#
currRate=42
Case between #09:00:01# and #14:00:00#
currRate=33
Case between #14:00:01# and #23:00:00#
currRate=22
Case Else
'handle exceptions here
End Select

I have a date leave time I am not sure how to word the iif satement to
reflect a currency amount. I already have a per diem field so I would like
to keep it like that, I would need to do an expression in the after update I
belive anyways this is what I am dealing with

IIF([Date Leave] between 12:00am and 9:00am then $42
between 9:01am and 2:00pm then $33
between 2:01pm and 11:00pm then $22. Could someone help me set this up.
Also I have a check box. Does not receive per deim. If this is checked
then the per diem fild is null.

Thanks

 

[Guest]

Try something like this:
Create a new module with the following code.

**************************
Public Function currRate(TimeLeave as string) as currency

'convert TimeLeave from string to time
Select Case Format(TimeLeave, "Long Time")

'test the value of TimeLeave and return the correct rate
Case Between #00:00:00# and #09:00:00#
currRate=42

Case Between #09:00:01# and #14:00:00#
currRate=33

Case Between #14:00:01# and #23:00:00#
currRate=22

Case Else
'handle exceptions here...what happens between #23:00:00# and #24:99:99#?

End Select
End Function
***************************

Then call this function from your After Update code to assign the return
value from currRate() to the field on your form displaying the rate

Me![rate field name]=currRate(Me![Time Leave])

This should get you pretty close. Good luck

JKMCCNH

Well I guess I made 2 mistakes. My field name is actually called Time Leave
and it is a text field. Will this still work. And where do I put this? Can
I still place it under afterupdate or in a query?

Have you considered using a Select Case statement instead of IIF? It's much
cleaner to use (and maintain) when you have multiple options.

Select Case DateLeave
Case between #00:00:00# and #09:00:00#
currRate=42
Case between #09:00:01# and #14:00:00#
currRate=33
Case between #14:00:01# and #23:00:00#
currRate=22
Case Else
'handle exceptions here
End Select

I have a date leave time I am not sure how to word the iif satement to
reflect a currency amount. I already have a per diem field so I would like
to keep it like that, I would need to do an expression in the after update I
belive anyways this is what I am dealing with

IIF([Date Leave] between 12:00am and 9:00am then $42
between 9:01am and 2:00pm then $33
between 2:01pm and 11:00pm then $22. Could someone help me set this up.
Also I have a check box. Does not receive per deim. If this is checked
then the per diem fild is null.

Thanks

 

[Douglas J Steele]

Sorry, but no.

Using Format converts the time to a string. You're then comparing that
string to Time constants. That means you've got unnecessary type coercion
happening.

Just leave out the Format statement. (If TimeLeave contains Date and Time,
then use TimeValue(TimeLeave) instead of Format)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try something like this:
Create a new module with the following code.

**************************
Public Function currRate(TimeLeave as string) as currency

'convert TimeLeave from string to time
Select Case Format(TimeLeave, "Long Time")

'test the value of TimeLeave and return the correct rate
Case Between #00:00:00# and #09:00:00#
currRate=42

Case Between #09:00:01# and #14:00:00#
currRate=33

Case Between #14:00:01# and #23:00:00#
currRate=22

Case Else
'handle exceptions here...what happens between #23:00:00# and #24:99:99#?

End Select
End Function
***************************

Then call this function from your After Update code to assign the return
value from currRate() to the field on your form displaying the rate

Me![rate field name]=currRate(Me![Time Leave])

This should get you pretty close. Good luck

JKMCCNH

Well I guess I made 2 mistakes. My field name is actually called Time Leave
and it is a text field. Will this still work. And where do I put this? Can
I still place it under afterupdate or in a query?

Have you considered using a Select Case statement instead of IIF? It's much
cleaner to use (and maintain) when you have multiple options.

Select Case DateLeave
Case between #00:00:00# and #09:00:00#
currRate=42
Case between #09:00:01# and #14:00:00#
currRate=33
Case between #14:00:01# and #23:00:00#
currRate=22
Case Else
'handle exceptions here
End Select

:

I have a date leave time I am not sure how to word the iif satement to
reflect a currency amount. I already have a per diem field so I would like
to keep it like that, I would need to do an expression in the after update I
belive anyways this is what I am dealing with

IIF([Date Leave] between 12:00am and 9:00am then $42
between 9:01am and 2:00pm then $33
between 2:01pm and 11:00pm then $22. Could someone help me set this up.
Also I have a check box. Does not receive per deim. If this is checked
then the per diem fild is null.

Thanks

 

[Guest]

I don't know what a new module means. I am going into uncharted waters.
Can you explain where to go? It sounds like a query right?

Try something like this:
Create a new module with the following code.

**************************
Public Function currRate(TimeLeave as string) as currency

'convert TimeLeave from string to time
Select Case Format(TimeLeave, "Long Time")

'test the value of TimeLeave and return the correct rate
Case Between #00:00:00# and #09:00:00#
currRate=42

Case Between #09:00:01# and #14:00:00#
currRate=33

Case Between #14:00:01# and #23:00:00#
currRate=22

Case Else
'handle exceptions here...what happens between #23:00:00# and #24:99:99#?

End Select
End Function
***************************

Then call this function from your After Update code to assign the return
value from currRate() to the field on your form displaying the rate

Me![rate field name]=currRate(Me![Time Leave])

This should get you pretty close. Good luck

JKMCCNH

Well I guess I made 2 mistakes. My field name is actually called Time Leave
and it is a text field. Will this still work. And where do I put this? Can
I still place it under afterupdate or in a query?

Have you considered using a Select Case statement instead of IIF? It's much
cleaner to use (and maintain) when you have multiple options.

Select Case DateLeave
Case between #00:00:00# and #09:00:00#
currRate=42
Case between #09:00:01# and #14:00:00#
currRate=33
Case between #14:00:01# and #23:00:00#
currRate=22
Case Else
'handle exceptions here
End Select

:

I have a date leave time I am not sure how to word the iif satement to
reflect a currency amount. I already have a per diem field so I would like
to keep it like that, I would need to do an expression in the after update I
belive anyways this is what I am dealing with

IIF([Date Leave] between 12:00am and 9:00am then $42
between 9:01am and 2:00pm then $33
between 2:01pm and 11:00pm then $22. Could someone help me set this up.
Also I have a check box. Does not receive per deim. If this is checked
then the per diem fild is null.

Thanks

 

[Guest]

Thanks for the clarification, Douglas.

Chey, in the database window you will see a tab for tables, one for queries,
one for reports, one for macros, and one for modules. On the modules tab
click New to create a new module, you will probably see:
*****************
Option Compare Database
Option Explicit
*****************
Below Option Explicit enter the code I listed previously, replacing
Select Case Format(TimeLeave, "Long Time")
with
Select Case TimeValue(TimeLeave)
per Douglas' comments.

This code takes the time passed to it, compares it to your predefined
options, and returns the appropriate Rate.

You call the function by placing this code
Me![rate field name]=currRate(Me![Time Leave])
in the After Update event for your Time Leave field. It passes the time to
the function above, captures the return value, then assigns that return value
to your Rate field on your form.

I'm no expert, as Douglas will now attest , but I believe this will get
you where you want to go. Good luck.

JKMCCNH

Sorry, but no.

Using Format converts the time to a string. You're then comparing that
string to Time constants. That means you've got unnecessary type coercion
happening.

Just leave out the Format statement. (If TimeLeave contains Date and Time,
then use TimeValue(TimeLeave) instead of Format)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try something like this:
Create a new module with the following code.

**************************
Public Function currRate(TimeLeave as string) as currency

'convert TimeLeave from string to time
Select Case Format(TimeLeave, "Long Time")

'test the value of TimeLeave and return the correct rate
Case Between #00:00:00# and #09:00:00#
currRate=42

Case Between #09:00:01# and #14:00:00#
currRate=33

Case Between #14:00:01# and #23:00:00#
currRate=22

Case Else
'handle exceptions here...what happens between #23:00:00# and #24:99:99#?

End Select
End Function
***************************

Then call this function from your After Update code to assign the return
value from currRate() to the field on your form displaying the rate

Me![rate field name]=currRate(Me![Time Leave])

This should get you pretty close. Good luck

JKMCCNH

Well I guess I made 2 mistakes. My field name is actually called Time Leave
and it is a text field. Will this still work. And where do I put this? Can
I still place it under afterupdate or in a query?

:

Have you considered using a Select Case statement instead of IIF? It's much
cleaner to use (and maintain) when you have multiple options.

Select Case DateLeave
Case between #00:00:00# and #09:00:00#
currRate=42
Case between #09:00:01# and #14:00:00#
currRate=33
Case between #14:00:01# and #23:00:00#
currRate=22
Case Else
'handle exceptions here
End Select

:

I have a date leave time I am not sure how to word the iif satement to
reflect a currency amount. I already have a per diem field so I would like
to keep it like that, I would need to do an expression in the after update I
belive anyways this is what I am dealing with

IIF([Date Leave] between 12:00am and 9:00am then $42
between 9:01am and 2:00pm then $33
between 2:01pm and 11:00pm then $22. Could someone help me set this up.
Also I have a check box. Does not receive per deim. If this is checked
then the per diem fild is null.

Thanks