Zb Kormecki,
Is the [arrival] field a date/time data type field? Also, please show us
how you are calling the "Nurse_Count" function from your query/form.
If it is a date/time data type, even though you are displaying [arrival] as
an integer from 0-23, it isn't stored as an interger 0-23 but you are trying
to compare it to integers 0-23.
Data/time is actually stored as a decimal number (serial number). Starting
with 12/31/1899 is actually stored as the number 1, 1/1/1900 is stored as 2,
today's date (9/21/2007) is stored as 39,346. So 24 hours translates to 1.
Now times are a fraction of 1. 0.25 = 6:00 am, 0.5 = 12:00 noon, 0.75 =
6:00 pm, and 0 = 12:00 midnight. Now to calculate a specific time on a
specifice date then you ad the serial number of the date to the fraction
that corresponds to the time of day. High noon today would translate to
39,346.5. My current time right now would translate to
39,346.626990740740741 (3:02:52 PM on 9/21/2007).
What you are comparing is integers to fractions. Will need to convert one
or the other so they are like values. I think the best approach will be to
convert the [arrival] hour to an integer with this: Time:
Hour([all_data]![arrival]). Now your [Time] field in your query should be
an integer. BUT in order for your "Nurse_Count()" function to work, I think
you will need to convert "Hour" to an integer data type (you are bringing
"Hour" in as a date data type but then comparing it to an integer data
type). I would change your chang your function declaration from this:
Public Function Nurse_Count(Hour As Date) As Double
to this:
Public Function Nurse_Count(ArrivalHour As Integer) As Double
(I changed "Hour" to "ArrivalHour" just because Hour is a built in function.
It might still work as "Hour". If you do change it to "ArrivalHour", make
sure to change every occurance of "Hour" in the function to "ArrivalHour" as
well).
Also, I think you are going to have problems with your If...Then...ElseIf
statements.
If I remember correctly, you can't compare Hour (ArrivalHour) to more than
one value at a time. For example:
If Hour = 1 Or 2 Or 10 Then
might not work. You may have to change it to
If Hour = 1 Or Hour = 2 Or Hour = 10 Then
You can simplfy things a little bit, like for x=7, the test can be rewritten
as follows:
ElseIf Hour >= 3 AND Hour <= 6 Then
x=13 can be rewritten as:
ElseIf Hour >= 15 AND Hour <= 21 Then
Becareful though, x=10 will need to be rewritten as follows:
ElseIf (Hour >= 11 AND Hour <= 14) OR (Hour = 23) OR (Hour = 0) Then
Also, it looks like you have a typo for x=8, you have "7 Or 8 Or 8". I'm
guessing that should be "7 Or 8 Or 9" (but rewritten following examples
above).
It might be a little cumbersome trying to rewrite the If...Then...ElseIf
part. If you look up the Select Case statement in Help, it might save you a
litlle time in rewriting. Using the Select Case, you can set it up more
like you current If...The...ElseIf statement.
Please keep in mind that I haven't tested any of my suggestions, I'm just
wingin' it best as remember. Other people might have better info for you.
HTH,
Conan
Zb Kornecki said:
I normally work w/ Excel but my file is quickly reaching on the 65k limit
so
I'm trying to do some data filtering and basics in Access and need help w/
the following.
I wrote the following function to group a query based on the number of
RN's
on duty. however it only return 9 as a result for everything. I can't
figure out why.
hour gets imported as the following expression
Time:Format([all_data]![arrival],"hh")
Time is not a column in the query but if it makes a diference I could add
the above expression All_data is a named table in the query
Thanks
Zb
Public Function Nurse_Count(Hour As Date) As Double
Dim x
If Hour = 1 Or 2 Or 10 Then
x = 9
ElseIf Hour = 3 Or 4 Or 5 Or 6 Then
x = 7
ElseIf Hour = 7 Or 8 Or 8 Then
x = 8
ElseIf Hour = 15 Or 16 Or 17 Or 18 Or 19 Or 20 Or 21 Then
x = 13
ElseIf Hour = 22 Then
x = 11
ElseIf Hour = 11 Or 12 Or 13 Or 14 Or 23 Or 0 Then
x = 10
End If
Nurse_Count = x
End Function
