how can i pass a field value to a function

J

JacobLondonUk

New member
Local time
Today, 15:44
Joined
Nov 25, 2011
Messages
7
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
 
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.
 
vbaInet said:
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.
Click to expand...


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
Code: 
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
 
I don't even know why you're needing to Format() the date. In the Control Source:
Code: 
=DLookup("Otherfields", "datesTbl", "date_e = #" [GivenDate] & "#")
 
vbaInet said:
I don't even know why you're needing to Format() the date. In the Control Source:
Code: 
=DLookup("Otherfields", "datesTbl", "date_e = #" [GivenDate] & "#")
Click to expand...

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.
 
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.
 
tried using "#" no diffrence
its returning null, it doesnt evaluate the given date to the date in the table
 
can you repaste your code into here with the corrections
 
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
 
All I need is a stripped down version. Create a table with some sample data and the form and upload that.
 
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?

Code: 
'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

Code: 
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
 
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)
 
JacobLondonUk said:
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)
Click to expand...

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

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

and dates like this as vbaInet posted

Code: 
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?
 
You must log in or register to reply here.

Similar threads

W
passing date variable as criteria expression
Replies
2
Views
642
Wysy
W
riktek
Programmatically construct a constant's name and return its value
2
Replies
38
Views
1,455
riktek
riktek
Gasman
Solved Tempvars not working in function?
Replies
5
Views
530
Gasman
Gasman
D
Solved Encapsulate Function In A Class For Command Bar
Replies
16
Views
1,539
MajP
MajP
C
Get 2 values from popup
Replies
15
Views
1,384
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom