Expression Builder Format Date Time - What happened to Access Help?!

[rm]

I am attempting to create a report. The report is based off of the
following query:

SELECT dtDateTime, varBuildNum, varProjectName
FROM tblBuild
WHERE dtDateTime>#1/1/2007#
ORDER BY dtDateTime, varProjectName;

(I do not like the field names - but I did not create the database).

In the report I am attempting to format the field dtDateTime in the
"Expression Builder". I am using the "Format" function. In the help
system I found the page of examples - none of which works.

= Format([dtDateTime], "Long Time") 'Error
= Format([dtDateTime], "Long Date") 'Error
= Format([dtDateTime], "h:m:s") 'Error
= Format([dtDateTime], "hh:mm:ss AMPM") 'Error
= Format([dtDateTime], "dddd, mmm d yyyy") 'Error

(By the way good freaking luck finding a clear explanation of using
LIKE in a SQL statement in the help system. What happened? I worked
with Access many versions ago and know for a fact that a perfectly
clear explanation of the the LIKE operator existed. What has happened
to the help system in Access?!)

 

[Rick Brandt]

I am attempting to create a report. The report is based off of the
following query:

SELECT dtDateTime, varBuildNum, varProjectName
FROM tblBuild
WHERE dtDateTime>#1/1/2007#
ORDER BY dtDateTime, varProjectName;

(I do not like the field names - but I did not create the database).

In the report I am attempting to format the field dtDateTime in the
"Expression Builder". I am using the "Format" function. In the help
system I found the page of examples - none of which works.

= Format([dtDateTime], "Long Time") 'Error
= Format([dtDateTime], "Long Date") 'Error
= Format([dtDateTime], "h:m:s") 'Error
= Format([dtDateTime], "hh:mm:ss AMPM") 'Error
= Format([dtDateTime], "dddd, mmm d yyyy") 'Error

(By the way good freaking luck finding a clear explanation of using
LIKE in a SQL statement in the help system. What happened? I worked
with Access many versions ago and know for a fact that a perfectly
clear explanation of the the LIKE operator existed. What has happened
to the help system in Access?!)

Are you trying to use the Format function as the ControlSource of a TextBox? If
so any of those should work providing that dtDateTime is actually a DateTime
data type and the name of the TextBox is not "dtDateTime".

You could also just use those format strings in the format property instead of
using the Format function. The difference is that if you use the property the
value is still a date (it will still sort like a date). The format function
will change it to a string so you will get character sorting.

 

[rm]

The name of the control is txtDateTime. The control type is a text
box. Yes the property of the text box that I am attempting to populate
is the ControlSource. dtDateTime is produced by the Expression Builder
by selecting "Queries" in the left hand column of the Expression
builder. The statement "=[dtDateTime]" does result in displaying the
date. Any attempt to use Format or Format$ fails.

I am attempting to create a report. The report is based off of the
following query:

SELECT dtDateTime, varBuildNum, varProjectName
FROM tblBuild
WHERE dtDateTime>#1/1/2007#
ORDER BY dtDateTime, varProjectName;

(I do not like the field names - but I did not create the database).

In the report I am attempting to format the field dtDateTime in the
"Expression Builder". I am using the "Format" function. In the help
system I found the page of examples - none of which works.

= Format([dtDateTime], "Long Time") 'Error
= Format([dtDateTime], "Long Date") 'Error
= Format([dtDateTime], "h:m:s") 'Error
= Format([dtDateTime], "hh:mm:ss AMPM") 'Error
= Format([dtDateTime], "dddd, mmm d yyyy") 'Error

(By the way good freaking luck finding a clear explanation of using
LIKE in a SQL statement in the help system. What happened? I worked
with Access many versions ago and know for a fact that a perfectly
clear explanation of the the LIKE operator existed. What has happened
to the help system in Access?!)

Are you trying to use the Format function as the ControlSource of a TextBox? If
so any of those should work providing that dtDateTime is actually a DateTime
data type and the name of the TextBox is not "dtDateTime".

You could also just use those format strings in the format property instead of
using the Format function. The difference is that if you use the property the
value is still a date (it will still sort like a date). The format function
will change it to a string so you will get character sorting.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -

(I must eat crow on the Like issue by the way. I understand what has
changed in the help system with that topic.)

 

[Rick Brandt]

The name of the control is txtDateTime. The control type is a text
box. Yes the property of the text box that I am attempting to populate
is the ControlSource. dtDateTime is produced by the Expression Builder
by selecting "Queries" in the left hand column of the Expression
builder. The statement "=[dtDateTime]" does result in displaying the
date. Any attempt to use Format or Format$ fails.

Perhap I wasn't clear. You cannot refer to a field in a ControlSource
expression that has the same name as the TextBox itself. That causes a circular
reference error. Change the name of the TextBox and any of your expressions
should work.

 

[Rick Brandt]

The name of the control is txtDateTime. The control type is a text
box. Yes the property of the text box that I am attempting to
populate is the ControlSource. dtDateTime is produced by the
Expression Builder by selecting "Queries" in the left hand column
of the Expression builder. The statement "=[dtDateTime]" does
result in displaying the date. Any attempt to use Format or Format$
fails.

Perhap I wasn't clear. You cannot refer to a field in a ControlSource
expression that has the same name as the TextBox itself. That causes
a circular reference error. Change the name of the TextBox and any
of your expressions should work.

My bad. I just noticed that you do have a different prefix. I can see no other
reason why your expressions would not work.

 

[rm]

Thank you for the attempt to help. I appreciate the effort.

The name of the control is txtDateTime.

dtDateTime is the name of the field in the query. So the statement
"=[dtDateTime]" does work.

The name of the control is txtDateTime. The control type is a text
box. Yes the property of the text box that I am attempting to populate
is the ControlSource. dtDateTime is produced by the Expression Builder
by selecting "Queries" in the left hand column of the Expression
builder. The statement "=[dtDateTime]" does result in displaying the
date. Any attempt to use Format or Format$ fails.

Perhap I wasn't clear. You cannot refer to a field in a ControlSource
expression that has the same name as the TextBox itself. That causes a circular
reference error. Change the name of the TextBox and any of your expressions
should work.

 

[fredg]

Thank you for the attempt to help. I appreciate the effort.

The name of the control is txtDateTime.

dtDateTime is the name of the field in the query. So the statement
"=[dtDateTime]" does work.

The name of the control is txtDateTime. The control type is a text
box. Yes the property of the text box that I am attempting to populate
is the ControlSource. dtDateTime is produced by the Expression Builder
by selecting "Queries" in the left hand column of the Expression
builder. The statement "=[dtDateTime]" does result in displaying the
date. Any attempt to use Format or Format$ fails.

Perhap I wasn't clear. You cannot refer to a field in a ControlSource
expression that has the same name as the TextBox itself. That causes a circular
reference error. Change the name of the TextBox and any of your expressions
should work.

Sounds like a missing reference.
Open any module in Design view (or click Ctrl + G).
On the Tools menu, click References.
Click to clear the check box for the type library or object library
marked as "Missing:."

An alternative to removing the reference is to restore the referenced
file to the path specified in the References dialog box. If the
referenced file is in a new location, clear the "Missing:" reference
and create a new reference to the file in its new folder.

See Microsoft KnowledgeBase articles:
283115 'ACC2002: References That You Must Set When You Work with
Microsoft Access'
Or for Access 97:
175484 'References to Set When Working With Microsoft Access' for
the correct ones needed,
and
160870 'VBA Functions Break in Database with Missing References' for
how to reset a missing one.

For even more information, see
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html