Two problems with field data when using Dates & Currencies

[postman]

Two problems with field data when using Dates & Currencies.

Although I am able to format the fields to "LOOK" correct, they
are still long expressions in the fields i.e.

Dates:
I want this to be the field data: 04/04/2005 nothing else, so
my append query (criteria:date()) will list dates of today only
to be appended. BUT because the table date data is this:
4/04/2005 1:09:18 PM (short date or dd/mm/yyyy format), my query
will not list anything! I cannot set the field to hold "date"
only without having to manually edit the time bit out!

Currency:
Although I have format set to 2 decimal places in all my tables
and forms, there are up to 4 places actual in the field,
although these currencies are brought in from another data base
by a linked form and are copy/pasted into the forms datasheet
they are still up to 4 decimal places long. I only want 2
actual.

What's the secret?
Please help.

 

[Allen Browne]

Your options are:
a) Change the Criteria in your query, or
b) Get rid of the time component stored in the table.

a)
Internally, Access treats date/time values as a number where the whole
number represents the date, and the fraction the time of day. You can
therefore set the Criteria to anything greater than or equal to today, and
less than tomorrow. The Criteria row under your date field in your query
will read:

= #4/4/2005# And < #5/4/2005#

assuming your regional settings are set for dd/mm/yyyy.

b)
You can use an Update query to dump the time part and just keep the dates.
1. Create a query into this table.

2. Change it to an Update query (Update on Query menu).
Access adds an Update row to the grid.

3. Drag the date field into the grid.
In the Update row under this field, enter:
DateValue([MyDate])
substituting your field name for MyDate.

4. Run the query.

Often the time value gets into the field because the Default Value is set to
=Now() when it should have been =Date(). If so, prevent the problem from
recurring by changing the Default Value of the field in the table, or the
Default Value of the text box on the form.

 

[postman]

Thanks Allen,

Just on the currency decimal places; it seems whenever I click in a feild 4
decimal places are revealed. What I would like to achieve is 2 dp's only or
even better rounding to the nearest 25c upto the dollar as actual data
(query?) that would really make my day.

Thanks in advance.

Your options are:
a) Change the Criteria in your query, or
b) Get rid of the time component stored in the table.

a)
Internally, Access treats date/time values as a number where the whole
number represents the date, and the fraction the time of day. You can
therefore set the Criteria to anything greater than or equal to today, and
less than tomorrow. The Criteria row under your date field in your query
will read:

= #4/4/2005# And < #5/4/2005#

assuming your regional settings are set for dd/mm/yyyy.

b)
You can use an Update query to dump the time part and just keep the dates.
1. Create a query into this table.

2. Change it to an Update query (Update on Query menu).
Access adds an Update row to the grid.

3. Drag the date field into the grid.
In the Update row under this field, enter:
DateValue([MyDate])
substituting your field name for MyDate.

4. Run the query.

Often the time value gets into the field because the Default Value is set
to =Now() when it should have been =Date(). If so, prevent the problem
from recurring by changing the Default Value of the field in the table, or
the Default Value of the text box on the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Two problems with field data when using Dates & Currencies.

Although I am able to format the fields to "LOOK" correct, they
are still long expressions in the fields i.e.

Dates:
I want this to be the field data: 04/04/2005 nothing else, so
my append query (criteria:date()) will list dates of today only
to be appended. BUT because the table date data is this:
4/04/2005 1:09:18 PM (short date or dd/mm/yyyy format), my query
will not list anything! I cannot set the field to hold "date"
only without having to manually edit the time bit out!

Currency:
Although I have format set to 2 decimal places in all my tables
and forms, there are up to 4 places actual in the field,
although these currencies are brought in from another data base
by a linked form and are copy/pasted into the forms datasheet
they are still up to 4 decimal places long. I only want 2
actual.

What's the secret?
Please help.

 

[Allen Browne]

Same concepts.

To modify the date in MyField so it rounds to the nearest cent, create an
update query, and update the field to:
Round([MyField], 2)
To prevent the problem recurring, include the Round() in whatever expression
you use to assign the value to the field.

To round to the nearest 25c, try something like this:
CCur(CLng(4 * Nz([MyField],0))/4)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,

Just on the currency decimal places; it seems whenever I click in a feild
4 decimal places are revealed. What I would like to achieve is 2 dp's only
or even better rounding to the nearest 25c upto the dollar as actual data
(query?) that would really make my day.

Thanks in advance.

Your options are:
a) Change the Criteria in your query, or
b) Get rid of the time component stored in the table.

a)
Internally, Access treats date/time values as a number where the whole
number represents the date, and the fraction the time of day. You can
therefore set the Criteria to anything greater than or equal to today,
and less than tomorrow. The Criteria row under your date field in your
query will read:

= #4/4/2005# And < #5/4/2005#

assuming your regional settings are set for dd/mm/yyyy.

b)
You can use an Update query to dump the time part and just keep the
dates.
1. Create a query into this table.

2. Change it to an Update query (Update on Query menu).
Access adds an Update row to the grid.

3. Drag the date field into the grid.
In the Update row under this field, enter:
DateValue([MyDate])
substituting your field name for MyDate.

4. Run the query.

Often the time value gets into the field because the Default Value is set
to =Now() when it should have been =Date(). If so, prevent the problem
from recurring by changing the Default Value of the field in the table,
or the Default Value of the text box on the form.

Two problems with field data when using Dates & Currencies.

Although I am able to format the fields to "LOOK" correct, they
are still long expressions in the fields i.e.

Dates:
I want this to be the field data: 04/04/2005 nothing else, so
my append query (criteria:date()) will list dates of today only
to be appended. BUT because the table date data is this:
4/04/2005 1:09:18 PM (short date or dd/mm/yyyy format), my query
will not list anything! I cannot set the field to hold "date"
only without having to manually edit the time bit out!

Currency:
Although I have format set to 2 decimal places in all my tables
and forms, there are up to 4 places actual in the field,
although these currencies are brought in from another data base
by a linked form and are copy/pasted into the forms datasheet
they are still up to 4 decimal places long. I only want 2
actual.

What's the secret?
Please help.