#Name? error - Question on Dates

[Lu]

I have the following table to convert month names to french.

Mth_Name
Field 1= Month Number
Field 2= Month Name

example:
1 janvier
2 fevrier
....
12 decembre

I have a query, QryCurrentMonth which uses DatePart("m",Now()) to return the
current month number and find Month Name.

My problem is this. When creating a report I need my date to be displayed as:
"le 6 fevrier 2008". I have tried many options in my control source but to
no avail.

="le " & " " & DatePart("d",Now()) & " " & [QryCurrentMonth]![MONTH NAME] &
" " & DatePart("yyyy",Now())

Why do I keep getting #Name? instead of the date? Please help!

 

[MikeB]

I have the following table to convert month names to french.

Mth_Name
Field 1= Month Number
Field 2= Month Name

example:
1 janvier
2 fevrier
...
12 decembre

I have a query, QryCurrentMonth which uses DatePart("m",Now()) to return the
current month number and find Month Name.

My problem is this. When creating a report I need my date to be displayed as:
"le 6 fevrier 2008". I have tried many options in my control source but to
no avail.

="le " & " " & DatePart("d",Now()) & " " & [QryCurrentMonth]![MONTH NAME] &
" " & DatePart("yyyy",Now())

Why do I keep getting #Name? instead of the date? Please help!

#name? errors are usually when you have an unrecognized field/table in
a query. What I do is to break the bits of the control source apart
and see which one is incorrect. Take each part of the control source
and put it in its own text field and see which one does not resolve.

The part that looks suspicious to me is: [QryCurrentMonth]![MONTH
NAME]

 

[John W. Vinson]

My problem is this. When creating a report I need my date to be displayed as:
"le 6 fevrier 2008"

I don't have the French version installed, but I don't think you need any
table AT ALL, nor any code, nor any queries! What do you see if you just
display the date in a field with format property

"\l\e d mmmm yyyy"


John W. Vinson [MVP]

 

[Lu]

John, thanks for your reply. Your suggestion of "\l\e d mmmm yyyy" returns
"le 15 February 2008". The reason for this exercise is to print the month
name in french. I don't require a french version of Access. I just created a
table which holds all the french names then created a query to tell me the
current month (february) look in my table and find the french name (fevrier).
Thats all. Nothing difficult. The query works just fine, it is only when I
put it in a report do I get the error. Any other suggestions?

 

[John W. Vinson]

John, thanks for your reply. Your suggestion of "\l\e d mmmm yyyy" returns
"le 15 February 2008". The reason for this exercise is to print the month
name in french. I don't require a french version of Access. I just created a
table which holds all the french names then created a query to tell me the
current month (february) look in my table and find the french name (fevrier).
Thats all. Nothing difficult. The query works just fine, it is only when I
put it in a report do I get the error. Any other suggestions?

What's the recordsource of the report? Does it include your month names query,
joined by the month number?

 

[Carolyn Jeter]

I have the following table to convert month names to french.

Mth_Name
Field 1= Month Number
Field 2= Month Name

example:
1 janvier
2 fevrier
...
12 decembre

I have a query, QryCurrentMonth which uses DatePart("m",Now()) to return
the
current month number and find Month Name.

My problem is this. When creating a report I need my date to be displayed
as:
"le 6 fevrier 2008". I have tried many options in my control source but to
no avail.

="le " & " " & DatePart("d",Now()) & " " & [QryCurrentMonth]![MONTH NAME]
&
" " & DatePart("yyyy",Now())

Why do I keep getting #Name? instead of the date? Please help!

 

[fredg]

I have the following table to convert month names to french.

Mth_Name
Field 1= Month Number
Field 2= Month Name

example:
1 janvier
2 fevrier
...
12 decembre

I have a query, QryCurrentMonth which uses DatePart("m",Now()) to return
the
current month number and find Month Name.

My problem is this. When creating a report I need my date to be displayed
as:
"le 6 fevrier 2008". I have tried many options in my control source but to
no avail.

="le " & " " & DatePart("d",Now()) & " " & [QryCurrentMonth]![MONTH NAME]
&
" " & DatePart("yyyy",Now())

Why do I keep getting #Name? instead of the date? Please help!

Access throws a #Name error when it cannot find the name of a field
used in it's control source expression.

This part is incorrect:
[QryCurrentMonth]![MONTH NAME]

If QryCurrentMonth and it's fields is not in the Report's record
source, then you must use DLookUp with criteria to find the current
month name, and you must refer to the actual field name in the table,
which you state are Field1 and Field2.

="le " & " " & DatePart("d",Now()) & " " &
DLookUp("[Field2]","QryCurrentMonth","[Field1] = " &
DatePart("m",Now())) & " " & DatePart("yyyy",Now())

However, I would do it this way:

Note: Now() includes a Time value. While it won't affect this
expression, since you are formatting it not to show the Time, I would
suggest you use Now() only when you need the Time value as well as the
Date value.
If you do not need the Time, use Date() instead.

= "le " & Format(Date(),"d ") &
DLookUp("[Field2]","tblMonths","[Field1]= " & DatePart("m",Date())) &
" " & Year(Date())

I hope this has helped.