Need VBA code (Access 97) to reference Oracle db field

[madelca100]

All,

I'm trying to write a function to convert a YYYYMMDD date field to a real date. I would like to call the function and have it return a particular field as a date.

I already have a generic function that works (see below).

I want this one to be very specific and don't want to pass the name of the field to the function. Without judging whether this is good form or not I would like to know if (and how) it can be done.

This following function works when I pass it the YYYYMMDD field name (e.g., cvtYYYYMMDD2Date(date_of_claim):

Function cvtYYYYMMDD2Date(strDateField As Variant) As Variant
cvtYYYYMMDD2Date = DateValue(Mid(strDateField, 5, 2) _
& "/" & Mid(strDateField, 7, 2) & "/" & Mid(strDateField, 1, 4))
End Function



This next function does not work.
oracle table = claim
oracle field = date_of_claim

Function DoC2Date() As Variant
DoC2Date = DateValue(Mid(claim!date_of_claim, 5, 2) _
& "/" & Mid(claim!date_of_claim, 7, 2) _
& "/" & Mid(claim!date_of_claim, 1, 4))
End Function

Error message:
Run-time error '424'
Object required

Can I even refer to the database directly?
If so, what am I missing?

btw - In case you haven't guessed it, I'm not a VBA programmer.

thanks,
mike

I haven't failed, I just found 10,000 ways that don't work.

 

[fredericofonseca]

If using a function is it good code practice to pass the input parameters on the function, and not to depend on external values.

So your function should be


Function DoC2Date(datevalue as string) As Variant
DoC2Date = DateValue(Mid(datevalue, 5, 2) _
& "/" & Mid(datevalue, 7, 2) _
& "/" & Mid(datevalue, 1, 4))
End Function

But as you are getting the date from a oracle table then it may be faster to convert the date as part of the SQL statmement itself.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SkipVought]

BTW,

Oracle date default format is
[tt]
28-Mar-2005
[/tt]


Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[madelca100]

thanks fredericofonseca

but as you can see from my post I already have a function that works when a value is passed. I need something that will access a particular field in the database (without having to pass its name to the function).

Can this be done?

thanks,
mike

 

[fredericofonseca]

Pass the recordset to the function.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com