date format problems

  • Thread starter Thread starter Joanne
  • Start date Start date
J

Joanne

I'm trying to fill a control with the 'day number' and another with
'day month', such as

"the _19_ day of _September_".

I used the expression builder, functions, date/time, 'day' and then
'month' which fills the controls on the form in design view with
'=Day([«number»])' and =Month([«number»]).

My form is built on a query built on my table, and these two fields
are set to date/time for type.

When I actually open the form, Name? is living in the control instead
of the number for today, or the current month name.

Can someone please point out what I have wrong here, or where I can
get further info to correct this?
Thanks
 
I'm not completely sure, but I think you are saying that
=Day([«number»])
and
=Month([«number»])

are the expressions in the Control Source for two textboxes on your form?

And «number» is the name of a field from your query, and this field is
formatted as Date/Time?

The #Name? error that you're seeing may be because you have a control on
your form also named «number» in addition to «number» being the name of a
field in your form's Record Source query. Check this; change the name of the
control to something else.

Assuming that «number» is the field that is formatted as Date/Time, to show
the "day" from it, your expression
=Day([«number»])
is valid to use.

However, the way to show the full month name is this:
=Format([«number»], "mmmm")
 
This expression should work:
= Day([Ťnumberť]) && Month([Ťnumberť])
the double ampersand concatinates the two date elements with a space between
them.

Tom
 
Ken said:
I'm not completely sure, but I think you are saying that
=Day([«number»])
and
=Month([«number»])

are the expressions in the Control Source for two textboxes on your form?
Click to expand...

Yes, that's true. They were set using the expression builder
And «number» is the name of a field from your query, and this field is
formatted as Date/Time?
Click to expand...

No, the field names are 'day' and 'month' each done separately, and
each formatted as Date/Time
The #Name? error that you're seeing may be because you have a control on
your form also named «number» in addition to «number» being the name of a
field in your form's Record Source query. Check this; change the name of the
control to something else.
Click to expand...

No, there are no controls on my form named 'number' nor are there any
fields of any type in my tables named 'number'
Assuming that «number» is the field that is formatted as Date/Time, to show
the "day" from it, your expression
=Day([«number»])
is valid to use.

However, the way to show the full month name is this:
=Format([«number»], "mmmm")
Click to expand...
=Format etc returns an error

What is DatePart? Could that be what I need
 
No, it doesn't. That will raise an error.

If you want a space in between, you need to use & " " &

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



TomU said:
This expression should work:
= Day([Tnumbert]) && Month([Tnumbert])
the double ampersand concatinates the two date elements with a space between
them.

Tom

Joanne said:
I'm trying to fill a control with the 'day number' and another with
'day month', such as

"the _19_ day of _September_".

I used the expression builder, functions, date/time, 'day' and then
'month' which fills the controls on the form in design view with
'=Day([Tnumbert])' and =Month([Tnumbert]).

My form is built on a query built on my table, and these two fields
are set to date/time for type.

When I actually open the form, Name? is living in the control instead
of the number for today, or the current month name.

Can someone please point out what I have wrong here, or where I can
get further info to correct this?
Thanks
Click to expand...
Click to expand...
 
Ahhhh, part of your problem may be that you are being bitten by the use of
Reserved Words as the names of fields. Day and Month are the names of VBA
functions; if you use them as control or field names, you must always put
[ ] characters around them when using them as field names. Better, much
better, to never use them at all as control or field names.

See KB article number 286335 for more info:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

Second, if Day and Month are field names, then you cannot "invoke" them by
using the VBA functions with the same names. Your form is completely
confused because your expression tells it to go get a function named Day,
but it sees a field named Day, and doesn't know what you want done.

Post the SQL of the form's Record Source query and let's start with it to
help you past this difficulty.

Also provide more details about the data that the query returns and about
what you're wanting to show on the form.

--

Ken Snell
<MS ACCESS MVP>


Joanne said:
Ken said:
I'm not completely sure, but I think you are saying that
=Day([«number»])
and
=Month([«number»])

are the expressions in the Control Source for two textboxes on your form?
Click to expand...

Yes, that's true. They were set using the expression builder
And «number» is the name of a field from your query, and this field is
formatted as Date/Time?
Click to expand...

No, the field names are 'day' and 'month' each done separately, and
each formatted as Date/Time
The #Name? error that you're seeing may be because you have a control on
your form also named «number» in addition to «number» being the name of a
field in your form's Record Source query. Check this; change the name of the
control to something else.
Click to expand...

No, there are no controls on my form named 'number' nor are there any
fields of any type in my tables named 'number'
Assuming that «number» is the field that is formatted as Date/Time, to show
the "day" from it, your expression
=Day([«number»])
is valid to use.

However, the way to show the full month name is this:
=Format([«number»], "mmmm")
Click to expand...
=Format etc returns an error

What is DatePart? Could that be what I need
Click to expand...
 
Oops, wrong language.

Douglas J. Steele said:
No, it doesn't. That will raise an error.

If you want a space in between, you need to use & " " &

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



TomU said:
This expression should work:
= Day([Tnumbert]) && Month([Tnumbert])
the double ampersand concatinates the two date elements with a space between
them.

Tom

Joanne said:
I'm trying to fill a control with the 'day number' and another with
'day month', such as

"the _19_ day of _September_".

I used the expression builder, functions, date/time, 'day' and then
'month' which fills the controls on the form in design view with
'=Day([Tnumbert])' and =Month([Tnumbert]).

My form is built on a query built on my table, and these two fields
are set to date/time for type.

When I actually open the form, Name? is living in the control instead
of the number for today, or the current month name.

Can someone please point out what I have wrong here, or where I can
get further info to correct this?
Thanks
Click to expand...
Click to expand...
Click to expand...
 
KEn
I completely overlooked using reserved words. lo!
I renamed my fields DNumber for day and MName for name of month
MY goal is to fill in this type of sentence on a legal form using the
input from my user interface in access 2000 and automating the
document in word 2000 to fill in the blanks and print the doc

"on this the __________ day of _________________ blah blah blah"

I put the controls on the form and using the expression builder -
I chose functions, built-in functions, date/time, and day and month
this is what is returned to the control source for these controls
= Day («number»)
= Month («number»)

When I open the form, both controls show this ?Name

Here is the sql statement from the query that the table is built on,
including these two controls.

SELECT tblEmpContactInfo.EmpID, tblEmpContactInfo.FName,
tblEmpContactInfo.Midinit, tblEmpContactInfo.LName, [LName] & ", " &
[FName] & " " & [Midinit] & "." AS LFMName, [FName] & " " & [Midinit]
& ". " & [LName] AS FullName2, [FName] & " " & [Midinit] & ". " &
[LName] AS FullName, [FName] & " " & [Midinit] & "." AS FNameMidInit,
[City] & ", " & [State] & " " & [Zip] AS CityStateZip,
tblEmpContactInfo.Street1, tblEmpContactInfo.City,
tblEmpContactInfo.Street2, tblEmpContactInfo.Apt_Suite,
tblEmpContactInfo.State, tblEmpContactInfo.Zip, tblEmpContactInfo.DOB,
tblEmpContactInfo.SSN, tblEmpContactInfo.HomePhone,
tblEmpContactInfo.WorkPhone, tblEmpContactInfo.DOB,
tblEmpContactInfo.StartDate2, tblEmpContactInfo.StartDate,
tblEmpContactInfo.AnnivDate, tblEmpContactInfo.ZNumber,
tblEmpContactInfo.WPosition, tblEmpContactInfo.StartPayRate,
tblEmpContactInfo.MaritalStatus, tblEmpContactInfo.StOfRes,
tblEmpContactInfo.Gender, tblEmpContactInfo.HrsWorked,
tblEmpContactInfo.[Instructions 1], tblEmpContactInfo.[Instructions
2], tblEmpContactInfo.[Instructions 3], tblEmpContactInfo.Note1,
tblEmpContactInfo.Dnumber, tblEmpContactInfo.MName
FROM tblEmpContactInfo;

Actually, I would really like to know what to use as 'criteria' on the
fields in the query used to build the table.

Thanks for your help Ken. I really appreciate it
Ken said:
Ahhhh, part of your problem may be that you are being bitten by the use of
Reserved Words as the names of fields. Day and Month are the names of VBA
functions; if you use them as control or field names, you must always put
[ ] characters around them when using them as field names. Better, much
better, to never use them at all as control or field names.

See KB article number 286335 for more info:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

Second, if Day and Month are field names, then you cannot "invoke" them by
using the VBA functions with the same names. Your form is completely
confused because your expression tells it to go get a function named Day,
but it sees a field named Day, and doesn't know what you want done.

Post the SQL of the form's Record Source query and let's start with it to
help you past this difficulty.

Also provide more details about the data that the query returns and about
what you're wanting to show on the form.
Click to expand...
 
Joanne, I suspect that you are being confused by language
and technical vocabulary. When Ken uses something like
=Day(«number»)
the << and >> around the number, he is just indicating that
this is a place holder where you should fill in your own
field's name. I don't see a field in your query with the
name "Number", maybe you want to use the StartDate field??
=Day(StartDate)

This kind of syntax is called a meta-language and is used to
describe the syntax of the statement or expression under
discussion. Sort of the language that is used to describe
another language ;-) You'll need to be highly adaptable
is reading and writing this kind of thing since different
folks use different notations, usually rather casually.
This is one reason why posting the exact names that you are
using can avoid this extra(?) layer of confusion.

Another issue might be your use of the word "Field". A
field is often defined as an instance (current record) of a
column in a table/query. The thingies on a form or report
that are used to display the value of a field are called
"Controls". When you said that you changed the names of the
"field" from Number to DNumber, I think you really mean that
you changed the name of the control (text box) that is
displaying the day of the month.

Sorry if this interjection just adds to the confusion.
--
Marsh
MVP [MS Access]


I renamed my fields DNumber for day and MName for name of month
MY goal is to fill in this type of sentence on a legal form using the
input from my user interface in access 2000 and automating the
document in word 2000 to fill in the blanks and print the doc

"on this the __________ day of _________________ blah blah blah"

I put the controls on the form and using the expression builder -
I chose functions, built-in functions, date/time, and day and month
this is what is returned to the control source for these controls
= Day («number»)
= Month («number»)

When I open the form, both controls show this ?Name

Here is the sql statement from the query that the table is built on,
including these two controls.

SELECT tblEmpContactInfo.EmpID, tblEmpContactInfo.FName,
tblEmpContactInfo.Midinit, tblEmpContactInfo.LName, [LName] & ", " &
[FName] & " " & [Midinit] & "." AS LFMName, [FName] & " " & [Midinit]
& ". " & [LName] AS FullName2, [FName] & " " & [Midinit] & ". " &
[LName] AS FullName, [FName] & " " & [Midinit] & "." AS FNameMidInit,
[City] & ", " & [State] & " " & [Zip] AS CityStateZip,
tblEmpContactInfo.Street1, tblEmpContactInfo.City,
tblEmpContactInfo.Street2, tblEmpContactInfo.Apt_Suite,
tblEmpContactInfo.State, tblEmpContactInfo.Zip, tblEmpContactInfo.DOB,
tblEmpContactInfo.SSN, tblEmpContactInfo.HomePhone,
tblEmpContactInfo.WorkPhone, tblEmpContactInfo.DOB,
tblEmpContactInfo.StartDate2, tblEmpContactInfo.StartDate,
tblEmpContactInfo.AnnivDate, tblEmpContactInfo.ZNumber,
tblEmpContactInfo.WPosition, tblEmpContactInfo.StartPayRate,
tblEmpContactInfo.MaritalStatus, tblEmpContactInfo.StOfRes,
tblEmpContactInfo.Gender, tblEmpContactInfo.HrsWorked,
tblEmpContactInfo.[Instructions 1], tblEmpContactInfo.[Instructions
2], tblEmpContactInfo.[Instructions 3], tblEmpContactInfo.Note1,
tblEmpContactInfo.Dnumber, tblEmpContactInfo.MName
FROM tblEmpContactInfo;

Actually, I would really like to know what to use as 'criteria' on the
fields in the query used to build the table.

Thanks for your help Ken. I really appreciate it
Ken said:
Ahhhh, part of your problem may be that you are being bitten by the use of
Reserved Words as the names of fields. Day and Month are the names of VBA
functions; if you use them as control or field names, you must always put
[ ] characters around them when using them as field names. Better, much
better, to never use them at all as control or field names.

See KB article number 286335 for more info:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

Second, if Day and Month are field names, then you cannot "invoke" them by
using the VBA functions with the same names. Your form is completely
confused because your expression tells it to go get a function named Day,
but it sees a field named Day, and doesn't know what you want done.

Post the SQL of the form's Record Source query and let's start with it to
help you past this difficulty.

Also provide more details about the data that the query returns and about
what you're wanting to show on the form.
Click to expand...
Click to expand...
 
Marshall
I get it, I wasn't understanding that <<number>> was for me to fill in
with a control or other reference to the info I want.

Changing to DNumber and MName were actually the controls being changed
on the form, and I also changed their names in the 'fields' in the
table/query that loads the forms. Your explanation of the difference
helps me to understand better what is going on. You are NOT adding to
my confusion.

So, I set the expression for the DNumber control to '= [Date]
and the control named MName also to '= [Date] and predictably I am
getting the full date in this format __/__/__. Can you tell me how to
format the function Date so that it will return only the day or only
the month. (I set the values of [Date] to the 'currentdate' control on
my form so it is using the system date.

Thanks for your time and expertise Marshall - it is greatly
appreciated
Joanne
 
My thanks to Marsh for his additional input.

Now that you have the ControlSource set to =Date(), you can modify that to
show the day:
=Day(Date())

and the month name:
=Format(Date(), "mmmm")

As an aside, I had simply copied the <<number>> syntax from your original
post. In fact, in my newsreader, iin the original post t came through with
nonUnicode characters, so I thought it was a foreign word of some type. It
was only after I saw how my post appeared that I noted it was this
<<number>> syntax.

--

Ken Snell
<MS ACCESS MVP>

Joanne said:
Marshall
I get it, I wasn't understanding that <<number>> was for me to fill in
with a control or other reference to the info I want.

Changing to DNumber and MName were actually the controls being changed
on the form, and I also changed their names in the 'fields' in the
table/query that loads the forms. Your explanation of the difference
helps me to understand better what is going on. You are NOT adding to
my confusion.

So, I set the expression for the DNumber control to '= [Date]
and the control named MName also to '= [Date] and predictably I am
getting the full date in this format __/__/__. Can you tell me how to
format the function Date so that it will return only the day or only
the month. (I set the values of [Date] to the 'currentdate' control on
my form so it is using the system date.

Thanks for your time and expertise Marshall - it is greatly
appreciated
Joanne

Marshall said:
Joanne, I suspect that you are being confused by language
and technical vocabulary. When Ken uses something like
=Day(«number»)
the << and >> around the number, he is just indicating that
this is a place holder where you should fill in your own
field's name. I don't see a field in your query with the
name "Number", maybe you want to use the StartDate field??
=Day(StartDate)

This kind of syntax is called a meta-language and is used to
describe the syntax of the statement or expression under
discussion. Sort of the language that is used to describe
another language ;-) You'll need to be highly adaptable
is reading and writing this kind of thing since different
folks use different notations, usually rather casually.
This is one reason why posting the exact names that you are
using can avoid this extra(?) layer of confusion.

Another issue might be your use of the word "Field". A
field is often defined as an instance (current record) of a
column in a table/query. The thingies on a form or report
that are used to display the value of a field are called
"Controls". When you said that you changed the names of the
"field" from Number to DNumber, I think you really mean that
you changed the name of the control (text box) that is
displaying the day of the month.

Sorry if this interjection just adds to the confusion.
Click to expand...
Click to expand...
 
I suggest the following:

Sub Joanne(DateToBeActedOn as Date)

Dim theDay,theMonth,MyResponse

theDay=Format(DateToBeActedOn,"dd")
theMonth=Format(DateToBeActedOn,"mmmmm")

MyResponse= "the_" & theDay & "_day of _" & theMonth & "_"

End Sub

'Options:
Instead of dd try

d
_dd_

Instead of mmmm try

m
mm
mmm
_mmmm_
 
I suggest the following:

Sub Joanne(DateToBeActedOn as Date)

Dim theDay,theMonth,MyResponse

theDay=Format(DateToBeActedOn,"dd")
theMonth=Format(DateToBeActedOn,"mmmmm")

MyResponse= "the_" & theDay & "_day of _" & theMonth & "_"

End Sub

'Options:
Instead of dd try

d
_dd_

Instead of mmmm try

m
mm
mmm
_mmmm_

Regards

Bernie
 
Back
Top