Bizarre error involving date function

[Richard Harison]

I tried it in both the default value property of an unbound text box and in a VB
function
control: =Format(DateSerial(Year(Date()),Month(Date()),1),"m\/d\/yyyy")
VBFunction: MonthEnd=
Format(DateSerial(Year(Date()),Month(Date()),1),"m\/d\/yyyy")
(Function is dimmed as date)--I can see, as a string why it wouldn't work there

Marsh:

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.

That's what I ended up doing. Thanks

All the Best
Richard Harison

 

[Marshall Barton]

Well, as I tried to explain. the first one should not use
the Format function. The second one, in VBA, appears to be
setting the Value property, instead of the De property. In
this case, you should also not use the Format function.

If you had tried to set the DefaultValue property in VBA, it
would require the # signs in the Format function.
--
Marsh
MVP [MS Access]

I tried it in both the default value property of an unbound text box and in a VB
function
control: =Format(DateSerial(Year(Date()),Month(Date()),1),"m\/d\/yyyy")
VBFunction: MonthEnd=
Format(DateSerial(Year(Date()),Month(Date()),1),"m\/d\/yyyy")
(Function is dimmed as date)--I can see, as a string why it wouldn't work there

Marsh:

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.

That's what I ended up doing. Thanks

All the Best
Richard Harison

 

[Richard Harison]

Thanks again Marshall!
So Value is looking for a date and default value is looking for a
string....Right?
I think what got me so concerned was your point about different date format
delimiter settings in windows.
Here's what I finalized, and it is working great. The scenario is a form with 2
unbound text box controls

txtbox 1--BeginningDate:
Default Value: =DateSerial(Year(Date()),Month(Date()),1)
(switched from the datepart routine I was using)

After update event:
Function (dimensioned as Date) EndMonth is Called
(called in case user changes beginning date)
Me!EndingDate.Value = MonthEnd(Me!BeginningDate)
Here is the code in EndMonth:
MonthEnd = DateSerial(Year(BeginningDate), Month(BeginningDate) + 1, 0)

txtbox2-- EndingDate
Default Value: =MonthEnd([BeginningDate])

How'd I do?

 

[Marshall Barton]

Thanks again Marshall!
So Value is looking for a date and default value is looking for a
string....Right?
I think what got me so concerned was your point about different date format
delimiter settings in windows.
Here's what I finalized, and it is working great. The scenario is a form with 2
unbound text box controls

txtbox 1--BeginningDate:
Default Value: =DateSerial(Year(Date()),Month(Date()),1)
(switched from the datepart routine I was using)

After update event:
Function (dimensioned as Date) EndMonth is Called
(called in case user changes beginning date)
Me!EndingDate.Value = MonthEnd(Me!BeginningDate)
Here is the code in EndMonth:
MonthEnd = DateSerial(Year(BeginningDate), Month(BeginningDate) + 1, 0)

txtbox2-- EndingDate
Default Value: =MonthEnd([BeginningDate])

How'd I do?

From my point of view, it looks like you are right on!

One thing left to consider is if you want the BeginningDate
AfterUpdate event to always set the EndingDate value. What
if the user sets the EndingDate text box before setting the
BeginningDate?? Should you leave the user's value alone or
reset it? If you want to leave it the way the user set it,
just check if it has a non-Null value:

If IsNull(EndingDate) _
Then Me!EndingDate.Value = MonthEnd(Me!BeginningDate)

You are also correct that the Value property contains a
value and the DefaultValue property contains a string that
is an expression.

 

[Richard Harison]

Thanks!
In 90% of the cases the user wants a current monthly report, and will just click
Ok--both boxes containing present month default values.
He/She may modify the contents of either box--agreed that one must check the
enddate box if the beginning box is modified and wants the enddate box to be
something else than the end of whatever month was input into the begin date box.
If the user changes the enddate box first, it would be because he wants a report
starting with the current month, but ending in a shorter or longer period than
the end of the month.
To be honest, I never thought that anybody would attempt to set the enddate box
first and then somehow null out the default contents.
To err on the side of safety, I will include your "if IsNull(EndingDate)" code,
adding a msgbox: lease Enter Accurate Ending Date," or the like
--
All the Best
Richard Harison

From my point of view, it looks like you are right on!

One thing left to consider is if you want the BeginningDate
AfterUpdate event to always set the EndingDate value. What
if the user sets the EndingDate text box before setting the
BeginningDate?? Should you leave the user's value alone or
reset it? If you want to leave it the way the user set it,
just check if it has a non-Null value:

If IsNull(EndingDate) _
Then Me!EndingDate.Value = MonthEnd(Me!BeginningDate)

You are also correct that the Value property contains a
value and the DefaultValue property contains a string that
is an expression.

Thanks again Marshall!
So Value is looking for a date and default value is looking for a
string....Right?
I think what got me so concerned was your point about different date format
delimiter settings in windows.
Here's what I finalized, and it is working great. The scenario is a form with
2
unbound text box controls

txtbox 1--BeginningDate:
Default Value: =DateSerial(Year(Date()),Month(Date()),1)
(switched from the datepart routine I was using)

After update event:
Function (dimensioned as Date) EndMonth is Called
(called in case user changes beginning date)
Me!EndingDate.Value = MonthEnd(Me!BeginningDate)
Here is the code in EndMonth:
MonthEnd = DateSerial(Year(BeginningDate), Month(BeginningDate) + 1, 0)

txtbox2-- EndingDate
Default Value: =MonthEnd([BeginningDate])

 

[BruceM]

The expression to which I intended to refer was:
=Format(DateSerial(Year(Date()),Month(Date()),1)),"\#m\/d\/yyyy\#")
You said that you were getting a "type mismatch" error. I replied in part
that while I was uncertain about why you were receiving the error, the
expression has an extra parentheses.
Only one parentheses is needed after the 1 in the Day part of DateSerial.
The expression:
=Format(DateSerial(Year(Date()),Month(Date()),1),"m\/d\/yyyy")
is one I provided to show what worked for me (without the # signs). I know
that the Date function requires the parentheses in all cases, which is why I
used them for Year and Month in DateSerial. In the Year argument of
DateSerial there is one parentheses to close the Year function, and another
to close the Date function. Same for Month. The Day part of DateSerial is
a number, not a function, so needs no parentheses other than the one to
close DateSerial.

 

[BruceM]

I had some misunderstandings about the use of Default Value and Control
Source. I had thought of Default Value a property of a bound control, but
of course it can also be an unbound control. Unlike Control Source it can
be changed (if the user wants to use a different date, for instance). When
the expression is entered as the Control Source the date can't be changed.
However, in my experiments (Access 2000) I cannot apply a date format using
the Format function in an expression either in the Default Value property
for the control or when I assign the controls's DefaultValue in the form's
Current event. In either case the format ends up being from my system
settings; to apply a format I need to use the control's Format property.
In my limited experience I have found that I prefer to use VBA to set values
such as the date values being discussed in this thread, just because it
makes it easier to look at all such expressions at once.

Thanks again Marshall!
So Value is looking for a date and default value is looking for a
string....Right?
I think what got me so concerned was your point about different date
format
delimiter settings in windows.
Here's what I finalized, and it is working great. The scenario is a form
with 2
unbound text box controls

txtbox 1--BeginningDate:
Default Value: =DateSerial(Year(Date()),Month(Date()),1)
(switched from the datepart routine I was using)

After update event:
Function (dimensioned as Date) EndMonth is Called
(called in case user changes beginning date)
Me!EndingDate.Value = MonthEnd(Me!BeginningDate)
Here is the code in EndMonth:
MonthEnd = DateSerial(Year(BeginningDate), Month(BeginningDate) + 1, 0)

txtbox2-- EndingDate
Default Value: =MonthEnd([BeginningDate])

How'd I do?

From my point of view, it looks like you are right on!

One thing left to consider is if you want the BeginningDate
AfterUpdate event to always set the EndingDate value. What
if the user sets the EndingDate text box before setting the
BeginningDate?? Should you leave the user's value alone or
reset it? If you want to leave it the way the user set it,
just check if it has a non-Null value:

If IsNull(EndingDate) _
Then Me!EndingDate.Value = MonthEnd(Me!BeginningDate)

You are also correct that the Value property contains a
value and the DefaultValue property contains a string that
is an expression.

 

[Richard Harison]

right!

--
All the Best
Richard Harison

The expression to which I intended to refer was:
=Format(DateSerial(Year(Date()),Month(Date()),1)),"\#m\/d\/yyyy\#")
You said that you were getting a "type mismatch" error. I replied in part
that while I was uncertain about why you were receiving the error, the
expression has an extra parentheses.
Only one parentheses is needed after the 1 in the Day part of DateSerial. The
expression:
=Format(DateSerial(Year(Date()),Month(Date()),1),"m\/d\/yyyy")
is one I provided to show what worked for me (without the # signs). I know
that the Date function requires the parentheses in all cases, which is why I
used them for Year and Month in DateSerial. In the Year argument of
DateSerial there is one parentheses to close the Year function, and another to
close the Date function. Same for Month. The Day part of DateSerial is a
number, not a function, so needs no parentheses other than the one to close
DateSerial.

 

[Marshall Barton]

What you say is correct. The reason is that the
DefaultValue property is a string that contains an
**expression**.

That is why you really should use the # signs when setting
the DefaultValue property. When the default value is
applied to a new record, the expression in the DefaultValue
property will be evaluated and the resulting value placed in
the Value property. Since the Value property is just a
value, all the standard formatting mechanisms will then come
into play. First the data type of the value (and, if used,
the data type of the text box's bound field), then the text
box's Format property and if that is unspecified, then the
system settings.

 

[BruceM]

It helps a lot to realize that DefaultValue is a string, regardless of the
field's format or any other considerations. I didn't know that. Thanks so
much for taking the time to explain, and for the clarity of the explanation.

What you say is correct. The reason is that the
DefaultValue property is a string that contains an
**expression**.

That is why you really should use the # signs when setting
the DefaultValue property. When the default value is
applied to a new record, the expression in the DefaultValue
property will be evaluated and the resulting value placed in
the Value property. Since the Value property is just a
value, all the standard formatting mechanisms will then come
into play. First the data type of the value (and, if used,
the data type of the text box's bound field), then the text
box's Format property and if that is unspecified, then the
system settings.
--
Marsh
MVP [MS Access]

I had some misunderstandings about the use of Default Value and Control
Source. I had thought of Default Value a property of a bound control, but
of course it can also be an unbound control. Unlike Control Source it can
be changed (if the user wants to use a different date, for instance).
When
the expression is entered as the Control Source the date can't be changed.
However, in my experiments (Access 2000) I cannot apply a date format
using
the Format function in an expression either in the Default Value property
for the control or when I assign the controls's DefaultValue in the form's
Current event. In either case the format ends up being from my system
settings; to apply a format I need to use the control's Format property.
In my limited experience I have found that I prefer to use VBA to set
values
such as the date values being discussed in this thread, just because it
makes it easier to look at all such expressions at once.

"Marshall Barton" wrote

 

[Guest]

For some reason this is the only way I can post a question. When I choose
new and than question it just beeps at me.

Anyway here is my question, and if your not the correct person for this
question could you be so kind to post this question for me, because like I
said I can not ask this question as a new question.

No programs were changed and for some bizarre reason, just right out of the
blue we started getting a message. So I tracked it down to this:
If keyascii = 27 then
"escape key pressed"
End If

There is no escape key pressed, all of the PC's that are involved are
getting the same error and they are all on this database. This leads me to
beleave that there is a problem with the table or the network itself. Please
if anyone has had this problem please respond as soon as possible. We are
using MS win XP with Access 2003.

Please help Thank You

 

[Marshall Barton]

For some reason this is the only way I can post a question. When I choose
new and than question it just beeps at me.

Anyway here is my question, and if your not the correct person for this
question could you be so kind to post this question for me, because like I
said I can not ask this question as a new question.

No programs were changed and for some bizarre reason, just right out of the
blue we started getting a message. So I tracked it down to this:
If keyascii = 27 then
"escape key pressed"
End If

There is no escape key pressed, all of the PC's that are involved are
getting the same error and they are all on this database. This leads me to
beleave that there is a problem with the table or the network itself. Please
if anyone has had this problem please respond as soon as possible. We are
using MS win XP with Access 2003.

Please help Thank You

Sorry, but I have no idea.

Posting a question for someone else is not a productive
exercise and it would probably be lost in the vast sea of
all questions.

 

[David Cox]

you are probably getting a compile error because the syntax is wrong.

whatever you want to do with "escape key pressed" you are not doing it.
Assign it somewhere, or put it in a msgbox, or whatever else, but as you
have it it is meaningless.