Bizarre error involving date function

[Marshall Barton]

A lot of languages do have an "escape" character for the
next character. But then you run into the same old
confusion of how to escape the escape character. Have you
ever seen a unix regular expression with 8 \s in a row?

 

[Marshall Barton]

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!

Your default value expression omitted the # signs, so, as I
explained to Bruce, there are potential problems lurking in
there.

I also thought that Bruce had convinced you to scrap your
MonthEnd function in favor of the DateSerial function. The
least you should do is modify the MonthEnd function to a
single line as Bruce explained.

 

[Richard Harison]

Yes, I will do that. But I don't understand the use of # in my case for the
default valueI am using datepart and date() not literals like #01/01/2007#

Oh yes..and the dateserial function using Month+1 eluded me for a moment. Then
I realize that using a zero for the day argument meant the last day of the
month. Can't find that trick an any of my Access books! Neat!

 

[Marshall Barton]

Yes, I will do that. But I don't understand the use of # in my case for the
default value
I am using datepart and date() not literals like #01/01/2007#

Oh yes..and the dateserial function using Month+1 eluded me for a moment. Then
I realize that using a zero for the day argument meant the last day of the
month. Can't find that trick an any of my Access books! Neat!

It doesn't matter how you are calculating the date, the
DefaultValue property stores it as a string. Then when it
is used, the string is evaluated as an expression and the
result is placed in the control's Value property.

Your expression will be converted to a string using your
Windows local settings (which might be any legal short date
format). The common USA setting would set the DefaultValue
property to 9/14/06, that is ambiguous. Depending on other
properties, it could be interpreted as 9 divided by 14
divided by 6, a rather small number. If you are getting the
result you want, it's only because a combination of things
just happen to be set to the right things.

To be safe, you should use:
=Format(DateSerial(Year(Date()),Month(Sate()),1)),"\#m\/d\/yyyy\#")

Keep the DateSerial function in mind. It is an extremely
versatile function because it can deal with month and day
values that can range up to 32,767. For example, these all
return the same result:
DateSerial(6,9,14)
DateSerial(2006,1,257)
DateSerial(70,1,13406)
DateSerial(1970,432,288)
DateSerial(2007,1,-108)

 

[Richard Harison]

Gotcha! I knew that the default value is set by a String expression. I once
used this to get a date to keep repeating until the user changes it:
Me!SchDate.DefaultValue = """" & Me!SchDate & """"
As an old QuickBasic programmer the need for 2 sets of quadruple quotes drove me
around the bend. {I know--could have used chr$(32)}
I also understand the backslash before the # symbols. Means "take next chr
literally... but what do the uppercase Vs mean? VB Help doesn't mention them
in format examples
Thanks again to all of you for being so kind!

 

[Richard Harison]

Arrrgh! I meant chr$(34) :<(

--
All the Best
Richard Harison

Gotcha! I knew that the default value is set by a String expression. I once
used this to get a date to keep repeating until the user changes it:
Me!SchDate.DefaultValue = """" & Me!SchDate & """"
As an old QuickBasic programmer the need for 2 sets of quadruple quotes drove
me around the bend. {I know--could have used chr$(32)}
I also understand the backslash before the # symbols. Means "take next chr
literally... but what do the uppercase Vs mean? VB Help doesn't mention them
in format examples
Thanks again to all of you for being so kind!

 

[Marshall Barton]

Gotcha! I knew that the default value is set by a String expression. I once
used this to get a date to keep repeating until the user changes it:
Me!SchDate.DefaultValue = """" & Me!SchDate & """"
As an old QuickBasic programmer the need for 2 sets of quadruple quotes drove me
around the bend. {I know--could have used chr$(32)}
I also understand the backslash before the # symbols. Means "take next chr
literally... but what do the uppercase Vs mean? VB Help doesn't mention them
in format examples

Those are not Vs, they are \ and /
The reason for escaping the / is that unescaped / will be
replaced by the date separator character specified in
Windows settings, which could be incompatible with Access.

 

[BruceM]

Haven't seen that. Must be a lot of fun explaining in a programming class
just how that works.

A lot of languages do have an "escape" character for the
next character. But then you run into the same old
confusion of how to escape the escape character. Have you
ever seen a unix regular expression with 8 \s in a row?
--
Marsh
MVP [MS Access]

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

 

[Brendan Reynolds]

I used to teach evening classes, and I can just imagine myself standing
there in front of the class saying "backslash backslash backslash backslash
.... <deep breath> ... backslash backslash backslash backslash" ...

--
Brendan Reynolds
Access MVP

Haven't seen that. Must be a lot of fun explaining in a programming class
just how that works.

A lot of languages do have an "escape" character for the
next character. But then you run into the same old
confusion of how to escape the escape character. Have you
ever seen a unix regular expression with 8 \s in a row?
--
Marsh
MVP [MS Access]

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.


BruceM wrote:

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.

"Marshall Barton" wrote
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.


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?

 

[BruceM]

I learned about it here.

Yes, I will do that. But I don't understand the use of # in my case for
the default valueI am using datepart and date() not literals like #01/01/2007#

Oh yes..and the dateserial function using Month+1 eluded me for a moment.
Then I realize that using a zero for the day argument meant the last day
of the month. Can't find that trick an any of my Access books! Neat!

 

[Richard Harison]

Clears it all up, THANKS!
Glad I posted before trying it!

 

[Richard Harison]

Actually when I use that line, whether in the VB function or in the textbox
default value property, I get a "type mismatch" error.

 

[BruceM]

It wouldn't be the default value of an unbound text box, it would be the
control source. I believe that the expression as entered has an extra
parentheses after the 1. Also, I need to say that I could not get it to
work as written either. I had to leave out the # signs:
=Format(DateSerial(Year(Date()),Month(Date()),1),"m\/d\/yyyy")
If I left the # signs in the expression they appeared in the text box along
with the date. Also, I found that it worked the same way with or without
the backslashes. I wish I understood some of these nuances better than I
do. I know there have been times when I have needed the # signs, but at
this hour on Firday afternoon it's not coming to me.

 

[Richard Harison]

Hi Bruce...
I think the parentheses are OK. Is not the closing parentheis you mention the
closer form the dateserial function? (The opening parenthesis coming after the
word DateSerial)

 

[Marshall Barton]

Actually when I use that line, whether in the VB function or in the textbox
default value property, I get a "type mismatch" error.

There is a typo (Sate instead of Date) in what I posted. If
you already spotted that, please post a Copy/Paste of what
you are using.

 

[Richard Harison]

Hi Bruce...
I think the parentheses are OK. Is not the closing parenthesis you mention the
closer for the dateserial function? {The opening parenthesis coming after the
word DateSerial}
It is interesting that it would not work for you either. I know that the
default value wants to see a string, and a traditional date string absolute is
enclosed by #s. Maybe Marsh has some input on this!

 

[Richard Harison]

What you wrote--except that I did spot & change the typo (Sate)

 

[BruceM]

Only one parentheses is needed after the 1 to close out the DateSerial
function.

 

[Richard Harison]

As I see it---there is only 1 closing parenthesis--- after the 1 and before the
comma delimiter. (Date function requires opening & closing parentheses--even tho
there is no argument ever supplied)

 

[Marshall Barton]

Hi Bruce...
I think the parentheses are OK. Is not the closing parenthesis you mention the
closer for the dateserial function? {The opening parenthesis coming after the
word DateSerial}
It is interesting that it would not work for you either. I know that the
default value wants to see a string, and a traditional date string absolute is
enclosed by #s. Maybe Marsh has some input on this!

I'm getting lost here. What are you guys using and where
are you using it?

If you are trying to place the expression:

=Format(DateSerial(Year(Date()),Month(Date()),1),"m\/d\/yyyy")
directly in the DefaultValue property, you are telling
Access to use the text string 9/1/2006 as the value when a
new record is created (the result of the expression is a
string because the Format function always returns a string
value). This is distinctly different from using a date
value as the default. If the bound field is a date type
field, it should not accept a text string (although Access
coerces types so often it's difficult to be sure what
actually happens). When you include the # signs, you are
telling Access to use #9/1/2006# as the default value, which
is a Date type value.

All that is not related to our original discussion about
using VBA code to set the DefaultValue. Don't get lost in
all the various levels of evaluations. When you use a
statement in a VBA procedure, VBA first processes the
statement and stores the result (a text string that is
itself an expression) in the DefaultValue property. Then,
when the default value is applied, Access (different from
VBA) evaulates the **expression** in the property and the
result of that evaluation is the value placed in the
control's Value property.

If you just want to have the default value property to be
the first of the current month without using VBA, you can
set the DefaultValue property in form design to the
expression:
=DateSerial(Year(Date()),Month(Date()),1)
and don't forget that the = sign is optional.