Floor function in Access

[Jessica]

Greetings!

How can I use the Floor function (found in Excel) in a module for Access?
What I have it doing in Excel is taking a date time (e.g. 3/2/2005 22:25:23)
and by using the floor function get it to the nearest hour (Floor(3/2/2005
22:25:23, 1/24) so that I get 3/2/2005 22:00:00. I then have to add values
to this date/time. How can I get access to do the same thing? I tried using
the round function (round(3/2/2005 22:25:23, 1/24) but got 38143 as a value
rather then a date value. Any ideas on how to correct this?

Thanks!

Jessica

 

[JaRa]

You can reference in VBA the Excel Library.

Then create a function in vba which then can be used everywhere in access

public function MyFloor([parameter list]) as [resulttype]
MyFloor=Floor([Parameter list])
end function


- Raoul

 

[Jessica]

Raoul,

This sounds like the way for me to go...but I am not sure what you mean by
parameter list and resulttype. Is result type the output data type? What
would parameter list be?

Thanks!
Jessica

You can reference in VBA the Excel Library.

Then create a function in vba which then can be used everywhere in access

public function MyFloor([parameter list]) as [resulttype]
MyFloor=Floor([Parameter list])
end function


- Raoul

Greetings!

How can I use the Floor function (found in Excel) in a module for Access?
What I have it doing in Excel is taking a date time (e.g. 3/2/2005 22:25:23)
and by using the floor function get it to the nearest hour (Floor(3/2/2005
22:25:23, 1/24) so that I get 3/2/2005 22:00:00. I then have to add values
to this date/time. How can I get access to do the same thing? I tried using
the round function (round(3/2/2005 22:25:23, 1/24) but got 38143 as a value
rather then a date value. Any ideas on how to correct this?

Thanks!

Jessica

 

[JaRa]

Not tested

Public Function MyFloor(number As Double, significance As Double) As Double
MyFloor = Floor(number, significance)
End Function

- Raoul

Raoul,

This sounds like the way for me to go...but I am not sure what you mean by
parameter list and resulttype. Is result type the output data type? What
would parameter list be?

Thanks!
Jessica

You can reference in VBA the Excel Library.

Then create a function in vba which then can be used everywhere in access

public function MyFloor([parameter list]) as [resulttype]
MyFloor=Floor([Parameter list])
end function


- Raoul

Greetings!

How can I use the Floor function (found in Excel) in a module for Access?
What I have it doing in Excel is taking a date time (e.g. 3/2/2005 22:25:23)
and by using the floor function get it to the nearest hour (Floor(3/2/2005
22:25:23, 1/24) so that I get 3/2/2005 22:00:00. I then have to add values
to this date/time. How can I get access to do the same thing? I tried using
the round function (round(3/2/2005 22:25:23, 1/24) but got 38143 as a value
rather then a date value. Any ideas on how to correct this?

Thanks!

Jessica

 

[Jessica]

Ah, that makes perfect sense now that I see it. Still having a problem
though...Access is getting hung up on the Floor function itself. I do have
the Excel Library selected, so I am not sure why it is doing this. It gives
me an error message of : Sub or Function not defined.

Jessica

Not tested

Public Function MyFloor(number As Double, significance As Double) As Double
MyFloor = Floor(number, significance)
End Function

- Raoul

Raoul,

This sounds like the way for me to go...but I am not sure what you mean by
parameter list and resulttype. Is result type the output data type? What
would parameter list be?

Thanks!
Jessica

You can reference in VBA the Excel Library.

Then create a function in vba which then can be used everywhere in access

public function MyFloor([parameter list]) as [resulttype]
MyFloor=Floor([Parameter list])
end function


- Raoul

:

Greetings!

How can I use the Floor function (found in Excel) in a module for Access?
What I have it doing in Excel is taking a date time (e.g. 3/2/2005 22:25:23)
and by using the floor function get it to the nearest hour (Floor(3/2/2005
22:25:23, 1/24) so that I get 3/2/2005 22:00:00. I then have to add values
to this date/time. How can I get access to do the same thing? I tried using
the round function (round(3/2/2005 22:25:23, 1/24) but got 38143 as a value
rather then a date value. Any ideas on how to correct this?

Thanks!

Jessica

 

[JaRa]

Myfloor= floor(....
should become

MyFloor=Excel.WorksheetFunction.Floor(

- Raoul

Ah, that makes perfect sense now that I see it. Still having a problem
though...Access is getting hung up on the Floor function itself. I do have
the Excel Library selected, so I am not sure why it is doing this. It gives
me an error message of : Sub or Function not defined.

Jessica

Not tested

Public Function MyFloor(number As Double, significance As Double) As Double
MyFloor = Floor(number, significance)
End Function

- Raoul

Raoul,

This sounds like the way for me to go...but I am not sure what you mean by
parameter list and resulttype. Is result type the output data type? What
would parameter list be?

Thanks!
Jessica


:

You can reference in VBA the Excel Library.

Then create a function in vba which then can be used everywhere in access

public function MyFloor([parameter list]) as [resulttype]
MyFloor=Floor([Parameter list])
end function


- Raoul

:

Greetings!

How can I use the Floor function (found in Excel) in a module for Access?
What I have it doing in Excel is taking a date time (e.g. 3/2/2005 22:25:23)
and by using the floor function get it to the nearest hour (Floor(3/2/2005
22:25:23, 1/24) so that I get 3/2/2005 22:00:00. I then have to add values
to this date/time. How can I get access to do the same thing? I tried using
the round function (round(3/2/2005 22:25:23, 1/24) but got 38143 as a value
rather then a date value. Any ideas on how to correct this?

Thanks!

Jessica

 

[Jessica]

That worked perfectly! Thank you so much!

Jessica

Myfloor= floor(....
should become

MyFloor=Excel.WorksheetFunction.Floor(

- Raoul

Ah, that makes perfect sense now that I see it. Still having a problem
though...Access is getting hung up on the Floor function itself. I do have
the Excel Library selected, so I am not sure why it is doing this. It gives
me an error message of : Sub or Function not defined.

Jessica

Not tested

Public Function MyFloor(number As Double, significance As Double) As Double
MyFloor = Floor(number, significance)
End Function

- Raoul


:

Raoul,

This sounds like the way for me to go...but I am not sure what you mean by
parameter list and resulttype. Is result type the output data type? What
would parameter list be?

Thanks!
Jessica


:

You can reference in VBA the Excel Library.

Then create a function in vba which then can be used everywhere in access

public function MyFloor([parameter list]) as [resulttype]
MyFloor=Floor([Parameter list])
end function


- Raoul

:

Greetings!

How can I use the Floor function (found in Excel) in a module for Access?
What I have it doing in Excel is taking a date time (e.g. 3/2/2005 22:25:23)
and by using the floor function get it to the nearest hour (Floor(3/2/2005
22:25:23, 1/24) so that I get 3/2/2005 22:00:00. I then have to add values
to this date/time. How can I get access to do the same thing? I tried using
the round function (round(3/2/2005 22:25:23, 1/24) but got 38143 as a value
rather then a date value. Any ideas on how to correct this?

Thanks!

Jessica

 

[Tim Ferguson]

How can I use the Floor function (found in Excel) in a module for Access?
What I have it doing in Excel is taking a date time (e.g. 3/2/2005 22:25:23)
and by using the floor function get it to the nearest hour (Floor(3/2/2005
22:25:23, 1/24) so that I get 3/2/2005 22:00:00. I then have to add values
to this date/time. How can I get access to do the same thing? I tried using
the round function (round(3/2/2005 22:25:23, 1/24) but got 38143 as a value
rather then a date value. Any ideas on how to correct this?

The usual function in VBA is Int() -- Fix() works too but handles negative
numbers differently.

If you want to truncate the minutes down to the start of the hour, you
multiply by 24 first:

dtRoundedTime = Int(dtOldTime * 24) / 24!

The result will be a double float, but as long as dtRoundedTime is dimmed
as a date then you will get a sensible number. Here is an example:

? cdate(Int(#2005-09-13 13:45:51# * 24) / 24!)
13/09/2005 13:00:00

which was what was wanted.

I don't think that 38143 is what you got from that calculation as

? cdate(38143)
05 June 2004

and if you check Help on the Round function, that is not how it's used
anyway.

Hope that helps


Tim F