Topics Search

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

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

More topics

 

MAX function different to Excel Max - why?

Preview
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

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

Preview
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

Preview
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

Preview
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
 

Easy one, If then ElseIf

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

Date issue in VBA

Preview
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

Preview
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
 

SQL Month function behaving oddly

Preview
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

Preview
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