Topics Search

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

Access 2003 Date() Function returns "#Name?"
Views: 31
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:

Similar posts...


MAX function different to Excel Max - why?

MAX function different to Excel Max - why? Icon
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?!

DateAdd function for workdays?

DateAdd function for workdays? Icon
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.

DatePart return error

DatePart return error Icon
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.

Long Choose Function

Long Choose Function Icon
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

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

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

Easy one, If then ElseIf

Easy one, If then ElseIf Icon
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

Date issue in VBA

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

SQL Month function behaving oddly

SQL Month function behaving oddly Icon
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

Create a query that accepts "Start" date and "End" date as parameters

Create a query that accepts "Start" date and "End" date as parameters Icon
In Access 2003/XP/2000/97, I'm trying to run a query based on a date. When the query opens, I would like some type of window prompting for the user to enter a "start" date and an "end" date. How can I do this?

how can i pass a field value to a function

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