how can i pass a field value to a function

[JacobLondonUk]

i have a table named DatesTbl
within it i have a field named Date_E with many dates in it
then more fields with other text relating to those dates,
i want to find the info of the other fields by passing a date value to a function and getting back the values of the other fields which are matching to the given date.
i created a function:
Public Function InfoDate(GivenDate As Date) As String
Dim stringOFTodaysDate As String
stringOFTodaysDate = Format(GivenDate, "dd/mm/yy")
InfoDate = DLookup("Otherfields", "datesTbl", "date_e=" & stringOFTodaysDate)
End Function
when i use the function: control.value = InfoDate(date)
it creates an error stating "invalid use of null"

i tried changing from date to string and backwards, tried adding "#" around it, nothing changed it
any one can help me please, much appreciated
thanks
jacob

 

[josephff]

First question i would ask is in

control.value = InfoDate(date)

what is "Date" referring to?

Secondly, when using dlookup statements i always use a "NZ" preceder to counter null values..

try making it this

InfoDate = nz(DLookup("Otherfields", "datesTbl", "date_e=" & stringOFTodaysDate))

 

[JacobLondonUk]

thanks for taking your time to help me!
"Date" is reffering to, either the Date literal which means Date(), or a valid date expression.
i did your advice, i used nz(...)
it returns nothing...
so it actualy does not get any value from th etable.
it does not for me with dlookup
i tried to get it with a query and it works fine in the query:
select Otherfields from DatesTbl where DatesTbl!date_e = Date();
but how do i get the sql query to pass its results value to a function???

 

[vbaInet]

Double post:

http://www.access-programmers.co.uk/forums/showthread.php?t=218872