date format and control source

[Joanne]

I have a date field, formated to return the number of the day of the
month, like so =Day(Date()) and this works great. THing is it is in
the control source property of this control because it is coded as an
expression in the expression builder.
Since that is the case, I cannot put the table field as the control
source so the value will write to my table and then allow me to use it
as a bookmark in an msword doc.
I tried putting this format in the table itself under format and under
mask for the date/time field, but it did no good. I have no idea how
to use it perhaps in vb editor, though as I surf these newsgroups for
informatio and get blessed by the answers to my questions, I doubt
that there is much you cannot achieve in vb if you only know how to
code it.
Could someone please point me in the right direction - trying to come
up with a viable search statement for the google groups proved
daunting.
THanks for your time and help
Joanne

 

[Ivan Grozney]

You can put the "date" field as an unbound to get the
day. Then set your table value = to the unbound value
after the unbound value is updated.

Create a new unbound field called UBDate or whatever works
for you. Put in the =Day(date()) as it's "control" Then
in the after update event for UBDate, put in
Me.<whatever your control is called> = Me.UBDate

in VB it would look like...

Private Sub UBDate_AfterUpdate()
Me.<whatever your control is called> = Me.UBDate
End Sub

hth

 

[Joanne]

Thanks a bunch for your answer to my problem Ivan
Tomorrow promises to be much less stressful and much more successful
as a result of your generosity and expertise
Joanne

 

[Marshall Barton]

I have a date field, formated to return the number of the day of the
month, like so =Day(Date()) and this works great. THing is it is in
the control source property of this control because it is coded as an
expression in the expression builder.
Since that is the case, I cannot put the table field as the control
source so the value will write to my table and then allow me to use it
as a bookmark in an msword doc.
I tried putting this format in the table itself under format and under
mask for the date/time field, but it did no good. I have no idea how
to use it perhaps in vb editor, though as I surf these newsgroups for
informatio and get blessed by the answers to my questions, I doubt
that there is much you cannot achieve in vb if you only know how to
code it.
Could someone please point me in the right direction - trying to come
up with a viable search statement for the google groups proved
daunting.

Joanne, you don't really want to put a derived value such as
this in your table.

If Word merge field can't extract the day of the month from
the datafield, then add Day(Date()) as a calculated field in
a query along with all(?) the fields in the table. Then set
Word merge to use the query instead of the table.

 

[Albert D. Kallal]

I have a date field, formated to return the number of the day of the
month, like so =Day(Date()) and this works great. THing is it is in
the control source property of this control because it is coded as an
expression in the expression builder.
Since that is the case, I cannot put the table field as the control
source so the value will write to my table and then allow me to use it
as a bookmark in an msword doc.

Use a query for the msword stuff, and simply make one of the columns in the
query:


MyDayay([YourDateField])

That way, you never have to store the value anyway. And, no reason to store
such a easy value.

 

[Joanne]

Marshall
Thanks for your input on my problem.
I am using access to automate word to do this job.
My bookmarks in word come from my table in access which holds the user
input.
My form in access is built on a query containing all the fields in my
table.
So "DNumber" is the field I want to hold the day number, so in the
query criteria line I put =Day(Date()) as the criteria and in the
control properties I put 'DNumber' as the control source but now when
I open the form there is no value in the 'DNumber' control at all.
I need this also for a month name, and I am using the same method as
above but with the following statement as criteria (these statements
were provided by MVP Ken)
=Format(Date()," mmmm")
and I am getting the same 'no value' in the control on opening the
form.
The purpose of this is in my doc I need to fill in this type of a
sentence "on the ______ day of ________________"
The only way the day number and the month name show up is if I have
the statements as their control source, but then, I can't write them
to the table for further use.

Do you see what I am doing wrong with your instructions?
Thanks for your time
Joanne

 

[Marshall Barton]

See comments in-line below.
--
Marsh
MVP [MS Access]

I am using access to automate word to do this job.
My bookmarks in word come from my table in access which holds the user
input.

Word merge can use a query instead of a table. These simple
calculations can easily be done in the query instead of
messing around in the form and table.

My form in access is built on a query containing all the fields in my
table.
OK


So "DNumber" is the field I want to hold the day number, so in the
query criteria line I put =Day(Date()) as the criteria and in the
control properties I put 'DNumber' as the control source but now when
I open the form there is no value in the 'DNumber' control at all.

The Criteria line in the form's query restricts the records
to only those that already have todays day number in the
DNumber field. That doesn't seem to make sense to me. I
suggested that you add the DNumber FIELD (not criteria) in a
new column in the query
DNumber: Day(Date())

This way the query will calculate todays day number whenever
the query is run.

Using the current date for this doesn't appear to be
particularly useful, don't you have another field in the
table with the full date of whatever activity you're
recording in the table?

I need this also for a month name, and I am using the same method as
above but with the following statement as criteria (these statements
were provided by MVP Ken)
=Format(Date()," mmmm")
and I am getting the same 'no value' in the control on opening the
form.

Use that expression the same way I outlined above for the
day number.

The purpose of this is in my doc I need to fill in this type of a
sentence "on the ______ day of ________________"
The only way the day number and the month name show up is if I have
the statements as their control source, but then, I can't write them
to the table for further use.

Not so! As I said above, another way to get these values to
word merge is with a query instead of your table. The query
(and your form) can easily calculate these values whenever
needed, which is much better that storing it in your table.

If you insist on having these values in the table, then use
Ivan's technique to get the form to calculate and save the
values.

 

[Joanne]

Marshall
Sometimes I am a bit slow on the uptake. Sorry about that.
I actually didn't realize that I could just type a field into the
query grid.
Anyway, now I know, I followed your instructions, the fields are
Dnumber: Day(Date())
MName: FormatFormat(Date()," mmmm") and all is working well.

More than having this problem solved is the new knowledge I have about
queries and calculated fields.
Thanks again for your time and patience

 

[Marshall Barton]

Marshall
Sometimes I am a bit slow on the uptake. Sorry about that.
I actually didn't realize that I could just type a field into the
query grid.
Anyway, now I know, I followed your instructions, the fields are
Dnumber: Day(Date())
MName: FormatFormat(Date()," mmmm") and all is working well.

More than having this problem solved is the new knowledge I have about
queries and calculated fields.
Thanks again for your time and patience

You're welcome. It's good to hear that you've both gained
some knowledge and solved the problem. Fuel for the future,
keep on truck'n ;-)