Bizarre error involving date function

[Richard Harison]

Hello! I need help!
I have written an extensive db and a popup form involves the date function. I
wrote a VB routine that automatically puts the 1st of the present month into one
text box and the last day of the present month in a second. Here is the key
line:
MonthEnd = DatePart("m", BeginningDate) & "/" & LastDay & "/" & DatePart("yyyy",
Date)
(LastDay is derived from an algorithm which returns the correct # of days in a
given month)
I am using Access 2002. When I imported the db to another 2002 computer the
popup form displayed a #NAME error. I found that the problem lay in a VB
library which wasn't active (DAO 3.6 as I recall). I added that library to
references and it worked fine -- on 2002 machines!
Problem now is it will not work in Access 2003. Yet when I look in references
it shows the DAO 3.6 checked.I tried converting the db to 2003, but the menu
says "2002-2003" which says to me that no conversion is necessary.
Is it possible that the Date function is in another library in 2003?
If not, what else could it be?

 

[BruceM]

I don't know why you're having the difficulties you are, so I will leave it
to others to provide guidance on that point, but you may be making this more
difficult than it needs to be. DateSerial will interpret the 0 day of a
month as the last day of the previous month. You could use something like
this:
If Me.NewRecord Then
Me.txtMonthStart =
Format(DateSerial(Year(Date()),Month(Date()),1,"m/d/yyyy")
Me.txtMonthEnd =
Format(Dateserial(Year(Date()),Month(Date())+1,0),"m/d/yyyy")
End If

This assumes that the text boxes txtMonthStart and txtMonthEnd are bound,
and that you want the value to be entered only if it is a new record. I
expect you could use these expressions as the default value of those text
boxes to accomplish the same thing. If your situation differs from these
assumptions the expression can be altered as needed.

 

[Douglas J. Steele]

The Date function comes from the same VBA library in all versions, but it's
more likely that there's a problem with one of the other referenced
libraries (the VBA library is always the last one checked, so if there's a
problem with any of the other libraries, Access doesn't actually make it to
that library)

While you're in the VB Editor, open any code module, then select Tools |
References from the menu bar. Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile, under the Debug menu), go
back in and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected.

(NOTE: write down what the references are before you delete them, because
they'll be in a different order when you go back in)

 

[Marshall Barton]

Bruce, That's definitely a simplification of "an algorithm
which returns the correct # of days in a given month"
However, it would be better to set the text box's
DefaultValue property instead of the Value. This way, the
new record would not be dirtied in case the user decides not
to continue.

If Me.NewRecord Then
Me.txtMonthStart.DefaultValue = _
Format(DateSerial(Year(Date()), Month(Date()), 1), _
"\#m\/d\/yyyy\#")
. . .


Richard, it doesn't matter what library a function is in.
If any needed Reference is missing, all bets are off on any
function in every library. You need to make sure that every
referenced library actually is installed and in the same
place on both machines. Because this can be a serious
hassle, you should not reference a library unless there is
no other way to do the job.

 

[BruceM]

Ah. I see what you're saying. If the user backs out of the record before
entering data in any other fields, the date fields remain empty without
having to hassle with Undo and extra stuff like that. By the way, what's up
with the # signs? I know they're used for dates, but in this context
(format rather than actual values) I can only guess that it has something to
do with treating the date as date and not text.

Bruce, That's definitely a simplification of "an algorithm
which returns the correct # of days in a given month"
However, it would be better to set the text box's
DefaultValue property instead of the Value. This way, the
new record would not be dirtied in case the user decides not
to continue.

If Me.NewRecord Then
Me.txtMonthStart.DefaultValue = _
Format(DateSerial(Year(Date()), Month(Date()), 1), _
"\#m\/d\/yyyy\#")
. . .


Richard, it doesn't matter what library a function is in.
If any needed Reference is missing, all bets are off on any
function in every library. You need to make sure that every
referenced library actually is installed and in the same
place on both machines. Because this can be a serious
hassle, you should not reference a library unless there is
no other way to do the job.
--
Marsh
MVP [MS Access]

I don't know why you're having the difficulties you are, so I will leave
it
to others to provide guidance on that point, but you may be making this
more
difficult than it needs to be. DateSerial will interpret the 0 day of a
month as the last day of the previous month. You could use something like
this:
If Me.NewRecord Then
Me.txtMonthStart =
Format(DateSerial(Year(Date()),Month(Date()),1,"m/d/yyyy")
Me.txtMonthEnd =
Format(Dateserial(Year(Date()),Month(Date())+1,0),"m/d/yyyy")
End If

This assumes that the text boxes txtMonthStart and txtMonthEnd are bound,
and that you want the value to be entered only if it is a new record. I
expect you could use these expressions as the default value of those text
boxes to accomplish the same thing. If your situation differs from these
assumptions the expression can be altered as needed.

 

[Richard Harison]

Thanks--But my text boxes are unbound. They send their values to a "between"
criteria in a query

--
All the Best
Richard Harison

I don't know why you're having the difficulties you are, so I will leave it to
others to provide guidance on that point, but you may be making this more
difficult than it needs to be. DateSerial will interpret the 0 day of a month
as the last day of the previous month. You could use something like this:
If Me.NewRecord Then
Me.txtMonthStart =
Format(DateSerial(Year(Date()),Month(Date()),1,"m/d/yyyy")
Me.txtMonthEnd =
Format(Dateserial(Year(Date()),Month(Date())+1,0),"m/d/yyyy")
End If

This assumes that the text boxes txtMonthStart and txtMonthEnd are bound, and
that you want the value to be entered only if it is a new record. I expect
you could use these expressions as the default value of those text boxes to
accomplish the same thing. If your situation differs from these assumptions
the expression can be altered as needed.

 

[Richard Harison]

Thanks Marsh....I'll also try to implement Doug Steele's suggestion as well.
Please see my thank you to Bruce ME. (text boxes unbound-- contents accessed by
a query

--
All the Best
Richard Harison

Bruce, That's definitely a simplification of "an algorithm
which returns the correct # of days in a given month"
However, it would be better to set the text box's
DefaultValue property instead of the Value. This way, the
new record would not be dirtied in case the user decides not
to continue.

If Me.NewRecord Then
Me.txtMonthStart.DefaultValue = _
Format(DateSerial(Year(Date()), Month(Date()), 1), _
"\#m\/d\/yyyy\#")
. . .


Richard, it doesn't matter what library a function is in.
If any needed Reference is missing, all bets are off on any
function in every library. You need to make sure that every
referenced library actually is installed and in the same
place on both machines. Because this can be a serious
hassle, you should not reference a library unless there is
no other way to do the job.
--
Marsh
MVP [MS Access]

I don't know why you're having the difficulties you are, so I will leave it
to others to provide guidance on that point, but you may be making this more
difficult than it needs to be. DateSerial will interpret the 0 day of a
month as the last day of the previous month. You could use something like
this:
If Me.NewRecord Then
Me.txtMonthStart =
Format(DateSerial(Year(Date()),Month(Date()),1,"m/d/yyyy")
Me.txtMonthEnd =
Format(Dateserial(Year(Date()),Month(Date())+1,0),"m/d/yyyy")
End If

This assumes that the text boxes txtMonthStart and txtMonthEnd are bound,
and that you want the value to be entered only if it is a new record. I
expect you could use these expressions as the default value of those text
boxes to accomplish the same thing. If your situation differs from these
assumptions the expression can be altered as needed.

 

[Richard Harison]

Thanks Doug...
Will try that as soon as I can visit the 2003 computer. As it stands in
"references", "Visual Basic For Applications (checked) is in the first position.
There are no items that say *MISSING*

--
All the Best
Richard Harison

The Date function comes from the same VBA library in all versions, but it's
more likely that there's a problem with one of the other referenced libraries
(the VBA library is always the last one checked, so if there's a problem with
any of the other libraries, Access doesn't actually make it to that library)

While you're in the VB Editor, open any code module, then select Tools |
References from the menu bar. Examine all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you just
unselected (you can tell by doing a Compile, under the Debug menu), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out of
the dialog, then go back in and unselect the reference you just added. If that
doesn't solve the problem, try to unselect as many of the selected references
as you can (Access may not let you unselect them all), back out of the dialog,
then go back in and reselect the references you just unselected.

(NOTE: write down what the references are before you delete them, because
they'll be in a different order when you go back in)

 

[BruceM]

It doesn't matter if the text boxes are unbound. Just use this in the
form's Current event, or wherever it works for your situation:

Me.txtMonthStart = DateSerial(Year(Date()),Month(Date()),1)
Me.txtMonthEnd = Dateserial(Year(Date()),Month(Date())+1,0)

You can apply the formatting directly to the text boxes rather than
including it in the expression.

You could also use the expression as the control source of the text box, or
you could use the expression as the criteria itself. My point is just that
Access provides a way of inserting exactly the dates you need.

Thanks--But my text boxes are unbound. They send their values to a
"between" criteria in a query

 

[Marshall Barton]

The DefaultValue property is more than just a string. It's
actually an expression (regardless of whether there's an
initial = sign or not) so it will be evaluated and, if
needed, type converted. If you omit the #s then it might be
treated as an series of divisions.

For the same reason, setting the DefaultValue to a text
value requires all the "extra" quotes:
Me.textbox.DefaultValue = """" & strABC & """"

If you were somehow able to **guarantee** that **every**
user had the same date/time settings in Windows, the type
conversion from a string to a date would be consistent and
you could use:
Me.textbox.DefaultValue = """" & _
Format(dtABC, "short date") & """"
but that is just begging for trouble.

 

[Richard Harison]

Thanks again---I'll give it a whirl tomorrow!!

 

[BruceM]

Thanks for the information. I will have to keep that in mind about Default
Value (and a number of other properties, I expect) being expressions. That
will help me understand what happens with the rest of the expression. Just
one more question, if I may. What's going on with the back slashes in the
date format? I see that there's one before each symbol that isn't m, d, or
y, but I don't know understand what's behind that, or for that matter if I
am anywhere near the right track with my observation.

The DefaultValue property is more than just a string. It's
actually an expression (regardless of whether there's an
initial = sign or not) so it will be evaluated and, if
needed, type converted. If you omit the #s then it might be
treated as an series of divisions.

For the same reason, setting the DefaultValue to a text
value requires all the "extra" quotes:
Me.textbox.DefaultValue = """" & strABC & """"

If you were somehow able to **guarantee** that **every**
user had the same date/time settings in Windows, the type
conversion from a string to a date would be consistent and
you could use:
Me.textbox.DefaultValue = """" & _
Format(dtABC, "short date") & """"
but that is just begging for trouble.
--
Marsh
MVP [MS Access]

Ah. I see what you're saying. If the user backs out of the record before
entering data in any other fields, the date fields remain empty without
having to hassle with Undo and extra stuff like that. By the way, what's
up
with the # signs? I know they're used for dates, but in this context
(format rather than actual values) I can only guess that it has something
to
do with treating the date as date and not text.

"Marshall Barton" wrote

 

[David Cox]

one more question, if I may. What's going on with the back slashes in the

date format? I see that there's one before each symbol that isn't m, d,
or y, but I don't know understand what's behind that, or for that matter
if I am anywhere near the right track with my observation.

# is a formating character. VBA has to have a way of differentiating an
instruction to the Format function from a literal that is to appear in the
output, as in #01/01/2006#

Thanks for the information. I will have to keep that in mind about
Default Value (and a number of other properties, I expect) being
expressions. That will help me understand what happens with the rest of
the expression. Just one more question, if I may. What's going on with
the back slashes in the date format? I see that there's one before each
symbol that isn't m, d, or y, but I don't know understand what's behind
that, or for that matter if I am anywhere near the right track with my
observation.

The DefaultValue property is more than just a string. It's
actually an expression (regardless of whether there's an
initial = sign or not) so it will be evaluated and, if
needed, type converted. If you omit the #s then it might be
treated as an series of divisions.

For the same reason, setting the DefaultValue to a text
value requires all the "extra" quotes:
Me.textbox.DefaultValue = """" & strABC & """"

If you were somehow able to **guarantee** that **every**
user had the same date/time settings in Windows, the type
conversion from a string to a date would be consistent and
you could use:
Me.textbox.DefaultValue = """" & _
Format(dtABC, "short date") & """"
but that is just begging for trouble.
--
Marsh
MVP [MS Access]

Ah. I see what you're saying. If the user backs out of the record
before
entering data in any other fields, the date fields remain empty without
having to hassle with Undo and extra stuff like that. By the way, what's
up
with the # signs? I know they're used for dates, but in this context
(format rather than actual values) I can only guess that it has something
to
do with treating the date as date and not text.

"Marshall Barton" wrote
Bruce, That's definitely a simplification of "an algorithm
which returns the correct # of days in a given month"
However, it would be better to set the text box's
DefaultValue property instead of the Value. This way, the
new record would not be dirtied in case the user decides not
to continue.

If Me.NewRecord Then
Me.txtMonthStart.DefaultValue = _
Format(DateSerial(Year(Date()), Month(Date()), 1), _
"\#m\/d\/yyyy\#")
. . .

BruceM wrote:

I don't know why you're having the difficulties you are, so I will
leave
it
to others to provide guidance on that point, but you may be making this
more
difficult than it needs to be. DateSerial will interpret the 0 day of
a
month as the last day of the previous month. You could use something
like
this:
If Me.NewRecord Then
Me.txtMonthStart =
Format(DateSerial(Year(Date()),Month(Date()),1,"m/d/yyyy")
Me.txtMonthEnd =
Format(Dateserial(Year(Date()),Month(Date())+1,0),"m/d/yyyy")
End If

This assumes that the text boxes txtMonthStart and txtMonthEnd are
bound,
and that you want the value to be entered only if it is a new record.
I
expect you could use these expressions as the default value of those
text
boxes to accomplish the same thing. If your situation differs from
these
assumptions the expression can be altered as needed.

Hello! I need help!
I have written an extensive db and a popup form involves the date
function. I wrote a VB routine that automatically puts the 1st of the
present month into one text box and the last day of the present month
in
a
second. Here is the key line:
MonthEnd = DatePart("m", BeginningDate) & "/" & LastDay & "/" &
DatePart("yyyy", Date)
(LastDay is derived from an algorithm which returns the correct # of
days
in a given month)
I am using Access 2002. When I imported the db to another 2002
computer
the popup form displayed a #NAME error. I found that the problem lay
in
a
VB library which wasn't active (DAO 3.6 as I recall). I added that
library to references and it worked fine -- on 2002 machines!
Problem now is it will not work in Access 2003. Yet when I look in
references it shows the DAO 3.6 checked.I tried converting the db to
2003,
but the menu says "2002-2003" which says to me that no conversion is
necessary.
Is it possible that the Date function is in another library in 2003?

 

[BruceM]

Are you saying that \ indicates that the following character is a formatting
character? If so, I don't understand, as # is a formatting character and /
is a literal character. Or is / a formatting character too, even though it
also appears in the date display? There does not seem to be anything in
Help about the use of the \ character.

one more question, if I may. What's going on with the back slashes in
the
date format? I see that there's one before each symbol that isn't m, d,
or y, but I don't know understand what's behind that, or for that matter
if I am anywhere near the right track with my observation.

# is a formating character. VBA has to have a way of differentiating an
instruction to the Format function from a literal that is to appear in the
output, as in #01/01/2006#

Thanks for the information. I will have to keep that in mind about
Default Value (and a number of other properties, I expect) being
expressions. That will help me understand what happens with the rest of
the expression. Just one more question, if I may. What's going on with
the back slashes in the date format? I see that there's one before each
symbol that isn't m, d, or y, but I don't know understand what's behind
that, or for that matter if I am anywhere near the right track with my
observation.

The DefaultValue property is more than just a string. It's
actually an expression (regardless of whether there's an
initial = sign or not) so it will be evaluated and, if
needed, type converted. If you omit the #s then it might be
treated as an series of divisions.

For the same reason, setting the DefaultValue to a text
value requires all the "extra" quotes:
Me.textbox.DefaultValue = """" & strABC & """"

If you were somehow able to **guarantee** that **every**
user had the same date/time settings in Windows, the type
conversion from a string to a date would be consistent and
you could use:
Me.textbox.DefaultValue = """" & _
Format(dtABC, "short date") & """"
but that is just begging for trouble.
--
Marsh
MVP [MS Access]


BruceM wrote:

Ah. I see what you're saying. If the user backs out of the record
before
entering data in any other fields, the date fields remain empty without
having to hassle with Undo and extra stuff like that. By the way,
what's up
with the # signs? I know they're used for dates, but in this context
(format rather than actual values) I can only guess that it has
something to
do with treating the date as date and not text.

"Marshall Barton" wrote
Bruce, That's definitely a simplification of "an algorithm
which returns the correct # of days in a given month"
However, it would be better to set the text box's
DefaultValue property instead of the Value. This way, the
new record would not be dirtied in case the user decides not
to continue.

If Me.NewRecord Then
Me.txtMonthStart.DefaultValue = _
Format(DateSerial(Year(Date()), Month(Date()), 1), _
"\#m\/d\/yyyy\#")
. . .


BruceM wrote:

I don't know why you're having the difficulties you are, so I will
leave
it
to others to provide guidance on that point, but you may be making
this
more
difficult than it needs to be. DateSerial will interpret the 0 day of
a
month as the last day of the previous month. You could use something
like
this:
If Me.NewRecord Then
Me.txtMonthStart =
Format(DateSerial(Year(Date()),Month(Date()),1,"m/d/yyyy")
Me.txtMonthEnd =
Format(Dateserial(Year(Date()),Month(Date())+1,0),"m/d/yyyy")
End If

This assumes that the text boxes txtMonthStart and txtMonthEnd are
bound,
and that you want the value to be entered only if it is a new record.
I
expect you could use these expressions as the default value of those
text
boxes to accomplish the same thing. If your situation differs from
these
assumptions the expression can be altered as needed.

Hello! I need help!
I have written an extensive db and a popup form involves the date
function. I wrote a VB routine that automatically puts the 1st of
the
present month into one text box and the last day of the present
month in
a
second. Here is the key line:
MonthEnd = DatePart("m", BeginningDate) & "/" & LastDay & "/" &
DatePart("yyyy", Date)
(LastDay is derived from an algorithm which returns the correct # of
days
in a given month)
I am using Access 2002. When I imported the db to another 2002
computer
the popup form displayed a #NAME error. I found that the problem
lay in
a
VB library which wasn't active (DAO 3.6 as I recall). I added that
library to references and it worked fine -- on 2002 machines!
Problem now is it will not work in Access 2003. Yet when I look in
references it shows the DAO 3.6 checked.I tried converting the db to
2003,
but the menu says "2002-2003" which says to me that no conversion
is
necessary.
Is it possible that the Date function is in another library in 2003?

 

[Marshall Barton]

Exactly! Inside a format specification, you can use "
around the literal characters or you can use / before each
literal character. This can get extra confusing in the
Format function where the format specification is also
enclosed in quotes. It's part of the old conundrum of how
to use quotes in a quoted string.

 

[BruceM]

Aha! Too bad it doesn't work in strings. Those little quote-mark parades
make my brain hurt.

Exactly! Inside a format specification, you can use "
around the literal characters or you can use / before each
literal character. This can get extra confusing in the
Format function where the format specification is also
enclosed in quotes. It's part of the old conundrum of how
to use quotes in a quoted string.
--
Marsh
MVP [MS Access]

Are you saying that \ indicates that the following character is a
formatting
character? If so, I don't understand, as # is a formatting character and
/
is a literal character. Or is / a formatting character too, even though
it
also appears in the date display? There does not seem to be anything in
Help about the use of the \ character.

 

[Richard Harison]

I would again like to thank all who helped me solve my problem. Amazing how
Access allows you to do the same thing so many ways! (Confusing too.)
Here is the default value code in the first text box ( named BeginningDate)

'returns 1st day current month
=DatePart("m",Date()) & "/01" & "/" & DatePart("yyyy",Date())

And in that box's after update event:
'function returns last day of month from public function MonthEnd
Me!EndingDate.Value = MonthEnd(Me!BeginningDate)

The line which caused the #NAME? error in both text boxes was this one in the
MonthEnd Function:
MonthEnd = DatePart("m", BeginningDate) & "/" & LastDay & "/" & DatePart("yyyy",
Date)

which I changed to:
MonthEnd = DatePart("m", BeginningDate) & "/" & LastDay & "/" & DatePart("yyyy",
BeginningDate)

Now Works Great--awaiting feedback from Access 2003 user
I also appreciate the DateSerial way. Seems to eliminate a lot of
concatenation!
Thanks again to all !!!

 

[Richard Harison]

I would again like to thank all who helped me solve my problem. Amazing how
Access allows you to do the same thing so many ways! (Confusing too.)

Here is the default value code in the first text box ( named BeginningDate)
'returns 1st day current month
=DatePart("m",Date()) & "/01" & "/" & DatePart("yyyy",Date())

And in that box's after update event:
'function returns last day of month from public function MonthEnd
Me!EndingDate.Value = MonthEnd(Me!BeginningDate)

The line which caused the #NAME? error in both text boxes was this one in the
MonthEnd Function:

'LastDay calculated from Case Structure within function
MonthEnd = DatePart("m", BeginningDate) & "/" & LastDay & "/" & DatePart("yyyy",
Date)

which I changed to:
MonthEnd = DatePart("m", BeginningDate) & "/" & LastDay & "/" & DatePart("yyyy",
BeginningDate)

Now Works Great--awaiting feedback from Access 2003 user
I also appreciate the DateSerial way. Seems to eliminate a lot of
concatenation! All your suggestions going into my snippet library!
Thanks again to all !!!

-- the Best
Richard Harison

 

[BruceM]

Do you have a field or text box named Date? That could cause that error, I
believe. Date is a reserved word in Access, so with a field of the same
name Access may not be able to resolve the ambiguity. For listings of
reserved words:
http://office.microsoft.com/en-us/assistance/HP011353121033.aspx
http://support.microsoft.com/kb/248738/EN-US/
http://support.microsoft.com/default.aspx?scid=kb;en-us;209187

I still think you're doing it the hard way by using a custom function to
determine the last day of the month, but that's up to you.

I would again like to thank all who helped me solve my problem. Amazing
how
Access allows you to do the same thing so many ways! (Confusing too.)

Here is the default value code in the first text box ( named
BeginningDate)
'returns 1st day current month
=DatePart("m",Date()) & "/01" & "/" & DatePart("yyyy",Date())

And in that box's after update event:
'function returns last day of month from public function MonthEnd
Me!EndingDate.Value = MonthEnd(Me!BeginningDate)

The line which caused the #NAME? error in both text boxes was this one in
the
MonthEnd Function:

'LastDay calculated from Case Structure within function
MonthEnd = DatePart("m", BeginningDate) & "/" & LastDay & "/" &
DatePart("yyyy",
Date)

which I changed to:
MonthEnd = DatePart("m", BeginningDate) & "/" & LastDay & "/" &
DatePart("yyyy",
BeginningDate)

Now Works Great--awaiting feedback from Access 2003 user
I also appreciate the DateSerial way. Seems to eliminate a lot of
concatenation! All your suggestions going into my snippet library!
Thanks again to all !!!

-- the Best
Richard Harison

 

[Richard Harison]

Thanks--I realize Date is a reserved word.That, and for descriptive reasons, I
would always name a control TodayDate, BeginDate, EndDate etc.

--
All the Best
Richard Harison

Do you have a field or text box named Date? That could cause that error, I
believe. Date is a reserved word in Access, so with a field of the same name
Access may not be able to resolve the ambiguity. For listings of reserved
words:
http://office.microsoft.com/en-us/assistance/HP011353121033.aspx
http://support.microsoft.com/kb/248738/EN-US/
http://support.microsoft.com/default.aspx?scid=kb;en-us;209187

I still think you're doing it the hard way by using a custom function to
determine the last day of the month, but that's up to you.