Access 2003 Date() Function returns "#Name?"

we use MS-Access only for one old program made in Access 2000. We are now using it in MS-Access 2003 in a Citrix environmnent compose of 8 servers. From my point of view all these servers are the same (installed in the same way with same content). On the three servers that have been installed first, the Date() function used in the program returns the correct value like DD/MM/YYYY, but on all the others servers the Date() function returns "#Name?". We have been able to live with the problem until now. I have to fix this and I have searched the net without success. Of course I have covered the Regional Settings and OS Date format usual stuff.

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

Sponsored Links:

Related Topics

MAX function different to Excel Max - why?
After trawling through data and queries for days I have finally stumbled upon the reason figures weren't matching which is because when the Max function is given the values ("3", "L") it returns "L" in MS Access and yet in MS Excel the same Max function returns "3". Firstly - does anyone know why this would be the case and the logic behind it?

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?!


DatePart return error
I'm trying to convert "mm/dd/y hh:nn:ss" date format to just time using DatePart function.

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.


DateAdd function for workdays?
I am trying to use the DateAdd function but it needs to calculate based on workdays, I figured maybe they considered weekdays the same, but it returns the same result as just using the days interval.Can someone help figure out how to get it to exclude saturday & sunday?

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.


Long Choose Function
I need to write a choose function that selects the proper ending for the day of the month. For instance, January 3rd, 2012. In excel, I had a template document with a date that would automatically update thanks to the following formula:

=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.


using functions as criteria in a query
I have created a query in Access 2003 as follows:

1) WHERE (((AccDS1check) Is Not Null) AND ((AccFabDate)=GetFDate()) AND ((AccRelacion)=21))
2)
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
4)
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


Date issue in VBA
Forum, Access 2010
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\#")
Else
EvaluateDate = Format(EvaluateDate, "\#mm\/dd\/yyyy\#")
End If
MsgBox EvaluateDate End Function


Easy one, If then ElseIf
Access-2003. I've created a simple trouble ticket feature to an existing DB. I have a form tied to a query that returns results and displays the current record. I have a combo-box named "Status" that has 3 possible values; Open, Restored, Closed.

I've got two other fields on the same form that record the "restore date and close date". I'm using VB on the status box to try and set the restore or close date automatically. I've tried: After update trigger:

If Me. Status = "restored" Then Me.restore_date = Date()
ElseIf Me.Status = "closed" Then Me.close_date = Date()
End If

also tried

If (Status = "restored") Then (restore_date = "Date()") etc.


SQL Month function behaving oddly
When I call the MONTH function on a date where the day is higher than 12, it works properly. But when I do it with a date where the day is less than or equal to 12, it returns the day value. So

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/Y 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.


SQL Month function behaving oddly
When I call the MONTH function on a date where the day is higher than 12, it works properly. But when I do it with a date where the day is less than or equal to 12, it returns the day value. So

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


Easy one, If then ElseIf
Access-2003. I've created a simple trouble ticket feature to an existing DB. I have a form tied to a query that returns results and displays the current record. I have a combo-box named "Status" that has 3 possible values; Open, Restored, Closed. I've got two other fields on the same form that record the "restore date and close date". I'm using VB on the status box to try and set the restore or close date automatically. Not working with my code. I've tried: After update trigger:

If Me. Status = "restored" Then Me.restore_date = Date()
ElseIf Me.Status = "closed" Then Me.close_date = Date()
End If

also tried

If (Status = "restored") Then (restore_date = "Date()") etc.

nutin workin


How do you use a Function inside another Function?
Forum,

I have created a Function to perform a task (delete records) and want this to be actioned within another function.

How do I get this to happen ? DoCmd ?

Here is an extract of the main procedure and the function I want to get to work is FuncDeleteRecord(some variables)


If RepayFreq = "Fortnightly" Then
'FuncDeleteRecord("TblLateFeeCalculated", "LDPK", "LoanID")
Do Until Date <= CommenceDate

Appreciate any advice


Date Function (Not recognizing days in month)
I am having trouble with a date function.

Currently I am using:

If Day(Date) + 7 < 8 then

code code code

else

code code code

As I write this post on the 25th of November, the number coming back is equal to 32.

How do I tell it to remain in "month ranges"

What I need is for the function to recognize that 7 days from now would be the first of November so that it returns a


how can i pass a field value to a function
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


how can i pass a field value to a function
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


Trouble with query criteria for Date/time to return results for "Today"
I have a date/time field ( "DateAdded") on a data entry form and want the recordset of that form to only show the current day's records. IF I set the query criteria for the forms record source to:
(today being that date), I get today's records. That's what I want but don't want to create a parameter query asking for that date every time the form closes/opens during the day.

Date()
returns no results and I'm wondering if it has to time with the time component.

>Date()-1
Returns yesterday's and todays records.

Is there a simple date function that will work here


DCount only counting some dates
I'm trying to count the number of records containing a particular date. The Dcount appears to count some dates but ignores others.

For example, if I ask it to count records containing the date 14/10/2010 it works, but if I ask it to count those with the date 04/06/2010 it returns zero.

A query finds the records OK but still Dcount won't, even when the query is used as the data source. I've spent more than 2 days solid trying to make this work and am now desperate.

The syntax I've used for Dcount is:
DCount("*", "tblTest","ECDate = #04/06/10#") but I've messed about with lots of variations of the syntax.

This must be something simple - I can't believe Access will accept some dates but ignore others. I've "hard coded" the dates into the DCount function for testing but in the database I'mdeveloping the dates are passed to the function via parameters.


Trouble with query criteria for Date/time to return results for "Today"
I have a date/time field ( "DateAdded") on a data entry form and want the recordset of that form to only show the current day's records. IF I set the query criteria for the forms record source to:

>=#17/03/2012#
I get today's records. That's what I want but don't want to create a parameter query asking for that date every time the form closes/opens during the day.

Date()
returns no results and I'm wondering if it has to time with the time component.

>Date()-1
Returns yesterday's and todays records.

Is there a simple date function that will work here?


How to handle Null Date in email ?
Forum, Access 2010.
Email code works fine except where one field has a null date.
An SQL in the vba code collects the data however, one field relates to Date Last Repayment Made

LastLoanRepayDate(CStr([LoanID])) AS LastRepayDate

LastLoanRepayDate() is a function that returns the date of the most recent repayment.
Being smart. I have the function returning 1/1/1995 where no repayment has been made.
Normally not an issue because other code filters any date prior to our company start date but for the email, we want the text to be blank or "NA" or other but not 1/1 1995.

Any ideas how this can be handled


Need to pull max price out of paragraph
So far it finds the "$", then returns the string. Occasionally there is a $5k, so it also changes it to $5000 and returns it. Now I need help if there are multiple values (always preceded by a "$") and pulling the max. Thanks in Advance, as I can't seem to figure this one out.

Public Function DollarAmt(s As String) As Double
If InStr(s, "$") Then
s = Mid(s, InStr(s, "$") + 1)
If Mid(s, Len(Trim(Val(s))) + 1, 1) = "k" Then
s = Val(s) & "000"
Else
s = Val(Replace(s, ",", ""))
End If
Else
s = 0
End If


DCount only counting some dates
I've attached a "database" which illustrates the problem I'm having. I'm trying to count the number of records containing a particular date. The Dcount appears to count some dates but ignores others. For example, if I ask it to count records containing the date 14/10/2010 it works, but if I ask it to count those with the date 04/06/2010 it returns zero.

A query finds the records OK but still Dcount won't, even when the query is used as the data source. I've spent more than 2 days solid trying to make this work and am now desperate.

The syntax I've used for Dcount is:
DCount("*", "tblTest","ECDate = #04/06/10#") but I've messed about with lots of variations of the syntax.

This must be something simple - I can't believe Access will accept some dates but ignore others. I've "hard coded" the dates into the DCount function for testing but in the database I'm developing the dates are passed to the function via parameters.

I'll be enormously grateful if someone can shed some light on this for me