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

 

[vbaInet]

Why don't you put the DLookup directly in the Control Source of the textbox? You can incorportate the Format line in the DLookup funciton.

 

[josephff]

Why don't you put the DLookup directly in the Control Source of the textbox? You can incorportate the Format line in the DLookup funciton.

this is probably the best path...or you can set an event to fire to pull any of the "otherfields" that you want based on the date

dim otherfields as string
dim date1 as string
date1 = Format(Date, "mm/dd/yyyy")
 
otherfields = nz(DLookup("Otherfields", "datesTbl", "date_e= '" & date1 & "'"))
 
control.value = otherfields

or similiar

 

[vbaInet]

I don't even know why you're needing to Format() the date. In the Control Source:

=DLookup("Otherfields", "datesTbl", "date_e = #" [GivenDate] & "#")

 

[josephff]

I don't even know why you're needing to Format() the date. In the Control Source:

=DLookup("Otherfields", "datesTbl", "date_e = #" [GivenDate] & "#")

I'm not either after a few tests...I have code where Ive formated the date like that but i was under the impression that it was in a different format originally and needed it. In fact it does not. I think I was using Now() at one time and thats the reason.

 

[vbaInet]

The Jet Engine (i.e. Access' database engine) uses American date so in some cases it matters how you pass the date. A query will attempt to re-format the date when it's entered but there are cases where you need to force format it.

 

[JacobLondonUk]

tried using "#" no diffrence
its returning null, it doesnt evaluate the given date to the date in the table

 

[vbaInet]

Does your Date field contain a Time part?

 

[JacobLondonUk]

no! field type is Date/time, but iits format is "short date"

 

[vbaInet]

Upload a sample db let me have a look.

 

[josephff]

can you repaste your code into here with the corrections

 

[JacobLondonUk]

its a huge database
but here are a few ideas i tried:

Public Function InfoDate(GivenDate As Date) As String
Dim UsingGivenDate As Date
Dim DateStringPass As String
UsingGivenDate = Format(GivenDate, "dd/mm/yy")
'Dim stringOFTodaysDate As String
'Dim Dan As Variant
'Dim convertedString As String
'stringOFTodaysDate = Format(GivenDate, "dd/mm/yy")
'convertedString = Replace(stringOFTodaysDate, ".", "/")
'If IsDate(GivenDate) Then
'If DateValue(GivenDate) = GivenDate Then
'infoDate = Nz(DLookup("fulldate_y", "datesqry", "datesqry!datestring=" & stringOFTodaysDate))
DateStringPass = (Nz(DLookup("long_y", "datesqry", " datesqry.formateddate = " & UsingGivenDate), "???"))
infoDate = DateStringPass
'SELECT DatesQry.Long_Y FROM DatesQry WHERE DatesQry.Date_E=Date();
'Dan = CurrentDb.OpenRecordset("SELECT DatesQry.Long_Y FROM DatesQry WHERE DatesQry.Date_E=" & GivenDate).OpenRecordset
'infoDate = Dan
'Dim Db As DAO.Database
'Dim Rs As DAO.Recordset
'Dim StrSQL As String
'Set Db = CurrentDb
'StrSQL = "SELECT datesqry.long_y FROM datesqry WHERE DatesQry.Datestring=" & stringOFTodaysDate
'Set Rs = Db.OpenRecordset(StrSQL, dbOpenSnapshot)
'infoDate = Rs
'Rs.Close
'Db.Close
'dan = CurrentDb.openrecordset("SQL").OpenRecordset.Field s("Sum_of_What_ever") 'End If
'End If
End Function

 

[vbaInet]

All I need is a stripped down version. Create a table with some sample data and the form and upload that.

 

[josephff]

I'm a little confused at your table and field names so if you could clarify. What is the name of the control you are trying to populate, what is the name of the form it is on. The name of the table you are pulling from, and a field that you want to retrieve.

you have this here in there which should work (when corrected) for the lookup but there is no declaration for it and the end variable needs quatation marks

also you have in here datesqry!datestring...is this an actual name for the table and field?

'infoDate = Nz(DLookup("fulldate_y", "datesqry", "datesqry!datestring=" & stringOFTodaysDate))

in the meantime try creating a button on your form and putting this code into it

dim date1 as string
date1 = Format(Date, "dd/mm/yy")

 
dim lookup1 as string
lookup1 = nz(DLookup("long_y", "datesqry", "formateddate = '" & date1 & "'"))
 
msgbox date1
msgbox lookup1

and see if the msgbox reads anything out. the first one should read you the date the second should look for the long_y field in the datesqry table where the formateddate field is equal to date1

 

[JacobLondonUk]

BINGO!!!
it works
many many thanks
here is the code i am using:

Public Function InfoDate(GivenDate As Date) As String
Dim date1 As String
date1 = Format(GivenDate, "dd/mm/yy")
InfoDate = Nz(DLookup("long_y", "datesqry", "formateddate = '" & date1 & "'"))
End Function

you made my day (week)

 

[josephff]

BINGO!!!
it works
many many thanks
here is the code i am using:

Public Function InfoDate(GivenDate As Date) As String
Dim date1 As String
date1 = Format(GivenDate, "dd/mm/yy")
InfoDate = Nz(DLookup("long_y", "datesqry", "formateddate = '" & date1 & "'"))
End Function

you made my day (week)

glad you got it working. You may want to play with vbaInet's code to try to get it to work in there also using the # symbols. As was stated there isnt nec. a need to format the date because it should already be in that format. Always remember to use correct double and single quotations when calling your variables. I saw in that snippet of code you posted how some were out of whack. String and numbers require different things to be able to call them correctly, and most of the time that is the problem (or it was for me when i first started)

strings will require the format above however numbers will be like this

InfoDate = Nz(DLookup("long_y", "datesqry", "formateddate = " & date1))

and dates like this as vbaInet posted

InfoDate= DLookup("long_y", "datesqry", "formateddate = #" & [date1] & "#")

i may be wrong but i think it didnt work for you before is because an ampersand was left out?