Access 2003 Date() Function returns "#Name?"
Anybody can help me understand the Date() function and on what it relies to return a value ? I am not even a programmer, but a network admin who needs to correct what seems to be a bad setting or something missing on our latest servers
Thank you for taking the time to report an issue.
What's wrong... Please write below.
Secondly, is there anyway to get MS Access to treat numbers as being higher in the ranking order than L. I could write a VBA function to do this but no time - is there any standard way of doing this?
Also this is mega frustrating - why oh why would this be the case Microsoft?!
The code I am using for this event is:
Me.[Due Date] = DateAdd("w", 3, [Date Submitted])
Me.[Update Date] = DateAdd("w", 13, [Date Submitted])
Me.[Dead Date] = DateAdd("w", 16, [Date Submitted])
Where the number specified is actually the number of WORKDAYS that need to be added.
I've used DatePart("hh:nn",[TimeIn]) and it returns "Error".
I've used TEMP: DatePart("h",[TimeIn]) & ":" & DatePart("n",[TimeIn]) and it returns the time; however, I can't compare the result to other time since it returns the results in text format.
That would be a complicated way to get the time part of a date/time value. Try using the TimeValue function instead.
=CHOOSE(Text(Today(), "d"), "st", "nd", "rd", "th", "th", "th", "th", "th", "th", "th", "th", "th", "th", "th", "th", "th", "th", "th", "th", "th", "st", "nd", "rd", "th", "th", "th", "th", "th", "th", "th", "st")
When I try to replicate this in access, it says the formula is too complex. Since I can write shorter choose functions in access, I assume the expression is too complex because it is too long. Is there a clean way around this? I can think of a number of messy ways, but I feel like access should have a way around this.
1) WHERE (((AccDS1check) Is Not Null) AND ((AccFabDate)=GetFDate()) AND ((AccRelacion)=21))
AccFabDate is a date value and GetFabDate is a function thus:
1) Public Function GetFabDate () As String
2) GetFabDate = ">#01/01/1980# and < #01/01/2011#”
3) End Function
When I run the query with ((AccFabDate)= ">#01/01/1980# and < #01/01/2011#”) there is no problem but when I use the public function to define the query, Access 2003 either crashes or returns.
Error 3464: mismatch in criteria expression. The criteria expression in a Find method is attempting to compare a field with a value whose data type does not match the field’s data type.
I need to be able to change the query value dynamically and have code to calculate the string values in the public function
If Me. Status = "restored" Then Me.restore_date = Date()
ElseIf Me.Status = "closed" Then Me.close_date = Date()
If (Status = "restored") Then (restore_date = "Date()") etc.
This code either returns 00:00:00 or throws an error 13 when the EvaluateDate option is not used.
I suspect the IsNull part is not picking up when the date is entered or not.
Public Function fncEvaluateRepay(LoanRef As Long, Optional EvaluateDate As Date) As String
Dim dbs As DAO.Database, rst As DAO.Recordset
Set dbs = CurrentDb()
'Check for EvaluateDate being selected and use todays date if not seelcted
If IsNull(EvaluateDate) Then
EvaluateDate = Format(Date, "\#mm\/dd\/yyyy\#")
EvaluateDate = Format(EvaluateDate, "\#mm\/dd\/yyyy\#")
MsgBox EvaluateDate End Function
MONTH(#23/03/2000#) returns 3 but
MONTH(#03/09/2000#) returns 3 as well
I double-checked the format of my dates as they are stored in the table, as well as how they are entered in the text-box before I click search on my form. It is the DD/MM/YYYY format.
The Query is "SELECT * FROM Employees WHERE MONTH(birthday) = MONTH(#21/05/2000#)"
I set this query as the record source of my form and it's being populated with the wrong results
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)
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