Steele's Diff2Date Function

[Guest]

I am using the Diff2Date function (very slick) in a query giving me an age or
duration in hours labeled as such ie: 96 Hours. I need to match this result
to a field in another table which has a record for every hour. This field is
a long integer data type. I guess I do not know what data type the Diff2Date
function results. Can anyone advise as to which field I convert and to what
so the data types match? Thank You

 

[Brendan Reynolds]

I'm not familiar with the function but from your description, I would guess
it probably returns a string. You can find out for sure by looking at the
function declaration. It will look something like 'Public Function Diff2Date
As Something' where 'Something' is the data type of the return value.

If I'm right and the data type is string, you can use the Val function to
convert it to a number - Val evaluates any digits it finds at the beginning
of the string, and stops at the first non-numeric character it encounters,
so for example Val("96 Hours") returns 96, ignoring the non-numeric text "
Hours". Val returns a Double, so you need to wrap the call to Val in a call
to the CLng function to convert the Double to a Long. (You can't pass the
string directly to the CLng function as the CLng function, unlike the Val
function, expects the entire argument to be numeric). For example ...

? clng(val("96 Hours"))
96

It would likely be more efficient, though, to write an alternative version
of the function that returns a Long value.

 

[Guest]

Thank You I will work on this and post back if I have difficulty. Any other
suggestions very welcome. Thanks to Doug Steele for the Diff2Dat function
too.

 

[Guest]

The Val and Clng functions seemed to work without a problem but I am still
having trouble matching the data types. I used the Diff2Date function in a
query, I used the Val and Clng functions in the same query and have tried
them together and just the Val function separately attempting to compare to
the [AGE] field in the other table. I have used the properties in the query
to assign the same format and changed the data type in the table several
times and still get a data type mismatch error. Help.

 

[Brendan Reynolds]

Did you look at the declaration of the function as I suggested? What data
type does the function return?

--
Brendan Reynolds (MVP)

The Val and Clng functions seemed to work without a problem but I am still
having trouble matching the data types. I used the Diff2Date function in
a
query, I used the Val and Clng functions in the same query and have tried
them together and just the Val function separately attempting to compare
to
the [AGE] field in the other table. I have used the properties in the
query
to assign the same format and changed the data type in the table several
times and still get a data type mismatch error. Help.

I'm not familiar with the function but from your description, I would
guess
it probably returns a string. You can find out for sure by looking at the
function declaration. It will look something like 'Public Function
Diff2Date
As Something' where 'Something' is the data type of the return value.

If I'm right and the data type is string, you can use the Val function to
convert it to a number - Val evaluates any digits it finds at the
beginning
of the string, and stops at the first non-numeric character it
encounters,
so for example Val("96 Hours") returns 96, ignoring the non-numeric text
"
Hours". Val returns a Double, so you need to wrap the call to Val in a
call
to the CLng function to convert the Double to a Long. (You can't pass the
string directly to the CLng function as the CLng function, unlike the Val
function, expects the entire argument to be numeric). For example ...

? clng(val("96 Hours"))
96

It would likely be more efficient, though, to write an alternative
version
of the function that returns a Long value.

 

[Guest]

Brendan: I did as you suggested, note the following:

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As Date, _
Optional ShowZero As Boolean = False) As Variant

I first tried the Val function noting that it returns a "Double", as this
was the same data type I had originally set in the static table I mentioned.
I then used the Clng and Val function together and changed the data type in
the static table to match. No luck. I have also tried assigning the format
type in the field properties of the Query I was using to apply the functions
to no avail.

Again, the outcome I am trying to achieve is to compare live lab values to
those in the static table and determine whether they are greater than or less
than the value in the static table using the age in hours as the measure.
Once I have the age it doesn't matter how it is formatted I guess because if
for example the live value is 12.9 at 99 hours, no matter how the 99 is
formated, if the format matches the record for 99 in the static table, the
lab value can then be compared....right?

Thank you for following this Brendan.

Did you look at the declaration of the function as I suggested? What data
type does the function return?

--
Brendan Reynolds (MVP)

The Val and Clng functions seemed to work without a problem but I am still
having trouble matching the data types. I used the Diff2Date function in
a
query, I used the Val and Clng functions in the same query and have tried
them together and just the Val function separately attempting to compare
to
the [AGE] field in the other table. I have used the properties in the
query
to assign the same format and changed the data type in the table several
times and still get a data type mismatch error. Help.

I'm not familiar with the function but from your description, I would
guess
it probably returns a string. You can find out for sure by looking at the
function declaration. It will look something like 'Public Function
Diff2Date
As Something' where 'Something' is the data type of the return value.

If I'm right and the data type is string, you can use the Val function to
convert it to a number - Val evaluates any digits it finds at the
beginning
of the string, and stops at the first non-numeric character it
encounters,
so for example Val("96 Hours") returns 96, ignoring the non-numeric text
"
Hours". Val returns a Double, so you need to wrap the call to Val in a
call
to the CLng function to convert the Double to a Long. (You can't pass the
string directly to the CLng function as the CLng function, unlike the Val
function, expects the entire argument to be numeric). For example ...

? clng(val("96 Hours"))
96

It would likely be more efficient, though, to write an alternative
version
of the function that returns a Long value.

--
Brendan Reynolds (MVP)

I am using the Diff2Date function (very slick) in a query giving me an
age
or
duration in hours labeled as such ie: 96 Hours. I need to match this
result
to a field in another table which has a record for every hour. This
field
is
a long integer data type. I guess I do not know what data type the
Diff2Date
function results. Can anyone advise as to which field I convert and to
what
so the data types match? Thank You

 

[Brendan Reynolds]

If any of the values involved are Null, that could be the problem. You might
try adding criteria to your query to exclude any records with Null values in
any of the fields involved in the expression, or use the NZ function to
replace Null values with an appropriate alternative.

If that doesn't solve the problem, try posting the SQL for the query.

--
Brendan Reynolds (MVP)

Brendan: I did as you suggested, note the following:

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As
Date, _
Optional ShowZero As Boolean = False) As Variant

I first tried the Val function noting that it returns a "Double", as this
was the same data type I had originally set in the static table I
mentioned.
I then used the Clng and Val function together and changed the data type
in
the static table to match. No luck. I have also tried assigning the
format
type in the field properties of the Query I was using to apply the
functions
to no avail.

Again, the outcome I am trying to achieve is to compare live lab values to
those in the static table and determine whether they are greater than or
less
than the value in the static table using the age in hours as the measure.
Once I have the age it doesn't matter how it is formatted I guess because
if
for example the live value is 12.9 at 99 hours, no matter how the 99 is
formated, if the format matches the record for 99 in the static table, the
lab value can then be compared....right?

Thank you for following this Brendan.

Did you look at the declaration of the function as I suggested? What data
type does the function return?

--
Brendan Reynolds (MVP)

The Val and Clng functions seemed to work without a problem but I am
still
having trouble matching the data types. I used the Diff2Date function
in
a
query, I used the Val and Clng functions in the same query and have
tried
them together and just the Val function separately attempting to
compare
to
the [AGE] field in the other table. I have used the properties in the
query
to assign the same format and changed the data type in the table
several
times and still get a data type mismatch error. Help.

:

I'm not familiar with the function but from your description, I would
guess
it probably returns a string. You can find out for sure by looking at
the
function declaration. It will look something like 'Public Function
Diff2Date
As Something' where 'Something' is the data type of the return value.

If I'm right and the data type is string, you can use the Val function
to
convert it to a number - Val evaluates any digits it finds at the
beginning
of the string, and stops at the first non-numeric character it
encounters,
so for example Val("96 Hours") returns 96, ignoring the non-numeric
text
"
Hours". Val returns a Double, so you need to wrap the call to Val in a
call
to the CLng function to convert the Double to a Long. (You can't pass
the
string directly to the CLng function as the CLng function, unlike the
Val
function, expects the entire argument to be numeric). For example ...

? clng(val("96 Hours"))
96

It would likely be more efficient, though, to write an alternative
version
of the function that returns a Long value.

--
Brendan Reynolds (MVP)

I am using the Diff2Date function (very slick) in a query giving me
an
age
or
duration in hours labeled as such ie: 96 Hours. I need to match
this
result
to a field in another table which has a record for every hour. This
field
is
a long integer data type. I guess I do not know what data type the
Diff2Date
function results. Can anyone advise as to which field I convert and
to
what
so the data types match? Thank You

 

[Guest]

Brendan: That was the problem, I was testing the database with only several
complete values entered, the others with empty fields were returning returned
#error and it didn't occur to me that could be a problem. I entered "Not
Null" in the field of the query and it works just fine. Thank you VERY much.
You and the others in this group are great!

If any of the values involved are Null, that could be the problem. You might
try adding criteria to your query to exclude any records with Null values in
any of the fields involved in the expression, or use the NZ function to
replace Null values with an appropriate alternative.

If that doesn't solve the problem, try posting the SQL for the query.

--
Brendan Reynolds (MVP)

Brendan: I did as you suggested, note the following:

Public Function Diff2Dates(Interval As String, Date1 As Date, Date2 As
Date, _
Optional ShowZero As Boolean = False) As Variant

I first tried the Val function noting that it returns a "Double", as this
was the same data type I had originally set in the static table I
mentioned.
I then used the Clng and Val function together and changed the data type
in
the static table to match. No luck. I have also tried assigning the
format
type in the field properties of the Query I was using to apply the
functions
to no avail.

Again, the outcome I am trying to achieve is to compare live lab values to
those in the static table and determine whether they are greater than or
less
than the value in the static table using the age in hours as the measure.
Once I have the age it doesn't matter how it is formatted I guess because
if
for example the live value is 12.9 at 99 hours, no matter how the 99 is
formated, if the format matches the record for 99 in the static table, the
lab value can then be compared....right?

Thank you for following this Brendan.

Did you look at the declaration of the function as I suggested? What data
type does the function return?

--
Brendan Reynolds (MVP)

The Val and Clng functions seemed to work without a problem but I am
still
having trouble matching the data types. I used the Diff2Date function
in
a
query, I used the Val and Clng functions in the same query and have
tried
them together and just the Val function separately attempting to
compare
to
the [AGE] field in the other table. I have used the properties in the
query
to assign the same format and changed the data type in the table
several
times and still get a data type mismatch error. Help.

:

I'm not familiar with the function but from your description, I would
guess
it probably returns a string. You can find out for sure by looking at
the
function declaration. It will look something like 'Public Function
Diff2Date
As Something' where 'Something' is the data type of the return value.

If I'm right and the data type is string, you can use the Val function
to
convert it to a number - Val evaluates any digits it finds at the
beginning
of the string, and stops at the first non-numeric character it
encounters,
so for example Val("96 Hours") returns 96, ignoring the non-numeric
text
"
Hours". Val returns a Double, so you need to wrap the call to Val in a
call
to the CLng function to convert the Double to a Long. (You can't pass
the
string directly to the CLng function as the CLng function, unlike the
Val
function, expects the entire argument to be numeric). For example ...

? clng(val("96 Hours"))
96

It would likely be more efficient, though, to write an alternative
version
of the function that returns a Long value.

--
Brendan Reynolds (MVP)

I am using the Diff2Date function (very slick) in a query giving me
an
age
or
duration in hours labeled as such ie: 96 Hours. I need to match
this
result
to a field in another table which has a record for every hour. This
field
is
a long integer data type. I guess I do not know what data type the
Diff2Date
function results. Can anyone advise as to which field I convert and
to
what
so the data types match? Thank You