Why does the function DMin("Abs([SomeTable]![FrstDate]- #" & [ScdDate] &
"#)";"SomeTable") not work properly with datefields.
It always finds the closest difference with a later date only, if an earlier
date in the table is closer or even equal, it still comes up with the later
date.
It works fine with numeric fields. (of course without the number signs)
Anybody knows why?
8  2819 
I wouldn't expect it to come up with a date at all, I would expect a number
(the number of days between the dates). You are then asking for the smallest
of this number. So, if FrstDate is 2/21/2005 and ScdDate is 2/21/2005, the
answer will be zero. Since you're using Abs, a negative number isn't
possible, so zero will be the DMin.
What exactly are you trying to do?
--
Wayne Morgan
MS Access MVP
"Gompie" <go****@gmx.net> wrote in message
news:cv**********@rl0001.unimaas.nl... Why does the function DMin("Abs([SomeTable]![FrstDate]- #" & [ScdDate] &
"#)";"SomeTable") not work properly with datefields.
It always finds the closest difference with a later date only, if an
earlier
date in the table is closer or even equal, it still comes up with the
later
date.
It works fine with numeric fields. (of course without the number signs)
Anybody knows why?
I wouldn't expect it to come up with a date at all, I would expect a number
(the number of days between the dates). You are then asking for the smallest
of this number. So, if FrstDate is 2/21/2005 and ScdDate is 2/21/2005, the
answer will be zero. Since you're using Abs, a negative number isn't
possible, so zero will be the DMin.
What exactly are you trying to do?
--
Wayne Morgan
MS Access MVP
"Gompie" <go****@gmx.net> wrote in message
news:cv**********@rl0001.unimaas.nl... Why does the function DMin("Abs([SomeTable]![FrstDate]- #" & [ScdDate] &
"#)";"SomeTable") not work properly with datefields.
It always finds the closest difference with a later date only, if an
earlier
date in the table is closer or even equal, it still comes up with the
later
date.
It works fine with numeric fields. (of course without the number signs)
Anybody knows why?
Sorry,
Didn't make myself clear enough.
It does come up with the number of days between the dates of course.
I'm trying to find the difference in days between a date-control on a form
and the closest date in a table.
Looking at it more closely I found that it almost always comes up with the
correct difference but only with a later date, oddly enough sometimes it
seems to give a random number.
I never find any relation with an earlier or equal date though.
I know that using Abs gives the difference without a sign.
But with numeric field this function works flawlesly, with lower, higher or
equal values it always finds the correct difference with the nearest value,
why not with dates?
Gompie
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:MV*******************@newssvr12.news.prodigy. com... I wouldn't expect it to come up with a date at all, I would expect a
number (the number of days between the dates). You are then asking for the
smallest of this number. So, if FrstDate is 2/21/2005 and ScdDate is 2/21/2005, the
answer will be zero. Since you're using Abs, a negative number isn't
possible, so zero will be the DMin.
What exactly are you trying to do?
--
Wayne Morgan
MS Access MVP
"Gompie" <go****@gmx.net> wrote in message
news:cv**********@rl0001.unimaas.nl... Why does the function DMin("Abs([SomeTable]![FrstDate]- #" & [ScdDate] &
"#)";"SomeTable") not work properly with datefields.
It always finds the closest difference with a later date only, if an
earlier
date in the table is closer or even equal, it still comes up with the
later
date.
It works fine with numeric fields. (of course without the number signs)
Anybody knows why?
Is the field in the table defined as a Date/Time field? If not, then Access
will treat it as a division problem if the values are in short date format.
What happens if you use DateDiff to do the subtraction? DMin("Abs([SomeTable]![FrstDate]- #" & [ScdDate] & "#)";"SomeTable")<<
Since you are telling it to look in SomeTable, you should be able to just
specify the field name instead of the table name and field name. Usually,
when they are specified together, such as in a query, a dot is used instead
of a bang (i.e. "." instead of "!"). If you remove the table reference in
the first argument, what happens? You are using a semi-color instead of a
comma for the argument divider, is this correct for your local?
--
Wayne Morgan
MS Access MVP
"Gompie" <go****@gmx.net> wrote in message
news:cv**********@rl0001.unimaas.nl... Sorry,
Didn't make myself clear enough.
It does come up with the number of days between the dates of course.
I'm trying to find the difference in days between a date-control on a form
and the closest date in a table.
Looking at it more closely I found that it almost always comes up with the
correct difference but only with a later date, oddly enough sometimes it
seems to give a random number.
I never find any relation with an earlier or equal date though.
I know that using Abs gives the difference without a sign.
But with numeric field this function works flawlesly, with lower, higher
or
equal values it always finds the correct difference with the nearest
value,
why not with dates?
Gompie
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message
news:MV*******************@newssvr12.news.prodigy. com... I wouldn't expect it to come up with a date at all, I would expect a
number (the number of days between the dates). You are then asking for the
smallest of this number. So, if FrstDate is 2/21/2005 and ScdDate is 2/21/2005,
the
answer will be zero. Since you're using Abs, a negative number isn't
possible, so zero will be the DMin.
What exactly are you trying to do?
--
Wayne Morgan
MS Access MVP
"Gompie" <go****@gmx.net> wrote in message
news:cv**********@rl0001.unimaas.nl... > Why does the function DMin("Abs([SomeTable]![FrstDate]- #" & [ScdDate]
> &
> "#)";"SomeTable") not work properly with datefields.
> It always finds the closest difference with a later date only, if an
> earlier
> date in the table is closer or even equal, it still comes up with the
> later
> date.
> It works fine with numeric fields. (of course without the number signs)
> Anybody knows why?
>
>
Wayne,
Thanks for putting so much effort in it.
The field in the table is indeed defined as Date/Time field.
As you suggested I tried to remove the table name and it gives the same
results, also replacing the bang with a dot makes no difference. The
semi-colon is the local setting for the argument divider, a comma gives a
syntax error.
As I said exactly the same function works flawlesly with numeric fields in
the same form and tables, so I think the problem has to do with the dates.
Only I don't have a clue what it is.
I can't use DateDiff as I don't know the exact date in the table as I want
the difference with the nearest date.
Hope you can come up with something.
Gompie
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:aH******************@newssvr30.news.prodigy.c om... Is the field in the table defined as a Date/Time field? If not, then
Access will treat it as a division problem if the values are in short date
format. What happens if you use DateDiff to do the subtraction?
DMin("Abs([SomeTable]![FrstDate]- #" & [ScdDate] & "#)";"SomeTable")<<
Since you are telling it to look in SomeTable, you should be able to just
specify the field name instead of the table name and field name. Usually,
when they are specified together, such as in a query, a dot is used
instead of a bang (i.e. "." instead of "!"). If you remove the table reference in
the first argument, what happens? You are using a semi-color instead of a
comma for the argument divider, is this correct for your local?
--
Wayne Morgan
MS Access MVP
"Gompie" <go****@gmx.net> wrote in message
news:cv**********@rl0001.unimaas.nl... Sorry,
Didn't make myself clear enough.
It does come up with the number of days between the dates of course.
I'm trying to find the difference in days between a date-control on a
form and the closest date in a table.
Looking at it more closely I found that it almost always comes up with
the correct difference but only with a later date, oddly enough sometimes it
seems to give a random number.
I never find any relation with an earlier or equal date though.
I know that using Abs gives the difference without a sign.
But with numeric field this function works flawlesly, with lower, higher
or
equal values it always finds the correct difference with the nearest
value,
why not with dates?
Gompie
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message
news:MV*******************@newssvr12.news.prodigy. com... I wouldn't expect it to come up with a date at all, I would expect a
number (the number of days between the dates). You are then asking for the
smallest of this number. So, if FrstDate is 2/21/2005 and ScdDate is 2/21/2005,
the
answer will be zero. Since you're using Abs, a negative number isn't
possible, so zero will be the DMin.
What exactly are you trying to do?
--
Wayne Morgan
MS Access MVP
"Gompie" <go****@gmx.net> wrote in message
news:cv**********@rl0001.unimaas.nl...
> Why does the function DMin("Abs([SomeTable]![FrstDate]- #" &
[ScdDate] > &
> "#)";"SomeTable") not work properly with datefields.
> It always finds the closest difference with a later date only, if an
> earlier
> date in the table is closer or even equal, it still comes up with the
> later
> date.
> It works fine with numeric fields. (of course without the number
signs) > Anybody knows why?
>
>
Where is this being used, a control source for a textbox, in code, or a
query? I just tried what you have in the control source of a textbox and it
returned the correct answer. For ScdDate, I used the name of a second
textbox on the form. I typed a date into that textbox and the calculated
textbox returned the correct number of days to the nearest date, whether it
be before or after the entered date.
--
Wayne Morgan
MS Access MVP
"Gompie" <go****@gmx.net> wrote in message
news:cv**********@rl0001.unimaas.nl... Wayne,
Thanks for putting so much effort in it.
The field in the table is indeed defined as Date/Time field.
As you suggested I tried to remove the table name and it gives the same
results, also replacing the bang with a dot makes no difference. The
semi-colon is the local setting for the argument divider, a comma gives a
syntax error.
As I said exactly the same function works flawlesly with numeric fields in
the same form and tables, so I think the problem has to do with the dates.
Only I don't have a clue what it is.
I can't use DateDiff as I don't know the exact date in the table as I want
the difference with the nearest date.
Hope you can come up with something.
Gompie
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message
news:aH******************@newssvr30.news.prodigy.c om... Is the field in the table defined as a Date/Time field? If not, then
Access will treat it as a division problem if the values are in short date
format. What happens if you use DateDiff to do the subtraction?
>>DMin("Abs([SomeTable]![FrstDate]- #" & [ScdDate] & "#)";"SomeTable")<<
Since you are telling it to look in SomeTable, you should be able to just
specify the field name instead of the table name and field name. Usually,
when they are specified together, such as in a query, a dot is used
instead of a bang (i.e. "." instead of "!"). If you remove the table reference in
the first argument, what happens? You are using a semi-color instead of a
comma for the argument divider, is this correct for your local?
--
Wayne Morgan
MS Access MVP
"Gompie" <go****@gmx.net> wrote in message
news:cv**********@rl0001.unimaas.nl... > Sorry,
> Didn't make myself clear enough.
> It does come up with the number of days between the dates of course.
> I'm trying to find the difference in days between a date-control on a form > and the closest date in a table.
> Looking at it more closely I found that it almost always comes up with the > correct difference but only with a later date, oddly enough sometimes
> it
> seems to give a random number.
> I never find any relation with an earlier or equal date though.
> I know that using Abs gives the difference without a sign.
> But with numeric field this function works flawlesly, with lower,
> higher
> or
> equal values it always finds the correct difference with the nearest
> value,
> why not with dates?
> Gompie
>
>
> "Wayne Morgan" <co***************************@hotmail.com> wrote in
> message
> news:MV*******************@newssvr12.news.prodigy. com...
>> I wouldn't expect it to come up with a date at all, I would expect a
> number
>> (the number of days between the dates). You are then asking for the
> smallest
>> of this number. So, if FrstDate is 2/21/2005 and ScdDate is 2/21/2005,
>> the
>> answer will be zero. Since you're using Abs, a negative number isn't
>> possible, so zero will be the DMin.
>>
>> What exactly are you trying to do?
>>
>> --
>> Wayne Morgan
>> MS Access MVP
>>
>>
>> "Gompie" <go****@gmx.net> wrote in message
>> news:cv**********@rl0001.unimaas.nl...
>> > Why does the function DMin("Abs([SomeTable]![FrstDate]- #" & [ScdDate] >> > &
>> > "#)";"SomeTable") not work properly with datefields.
>> > It always finds the closest difference with a later date only, if an
>> > earlier
>> > date in the table is closer or even equal, it still comes up with
>> > the
>> > later
>> > date.
>> > It works fine with numeric fields. (of course without the number signs) >> > Anybody knows why?
>> >
>> >
>>
>>
>
>
The Scddate comes from a control source on a form linked to a date field of
another table, the answer comes in a calculated texbox.
I tried what you said also and made an unbound textbox on the form as
Scddate, but then I get only random answers.
As it works with you, I gathered it maybe had to do with the date
formatting, so made a second unformatted textbox that gets the date from the
first textbox, so it shows the datenumber and used this as Scddate, but got
the same random results.
Can't understand why.
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:YY*******************@newssvr11.news.prodigy. com... Where is this being used, a control source for a textbox, in code, or a
query? I just tried what you have in the control source of a textbox and
it returned the correct answer. For ScdDate, I used the name of a second
textbox on the form. I typed a date into that textbox and the calculated
textbox returned the correct number of days to the nearest date, whether
it be before or after the entered date.
--
Wayne Morgan
MS Access MVP
If ScdDate is coming from another form, you'll need to use the full path to
that form (Forms!FormName!ScdDate). If you have two objects on this form
called ScdDate, then you may have a naming conflict. If the field is
ScdDate, try naming the textbox txtScdDate.
--
Wayne Morgan
MS Access MVP
"Gompie" <go****@gmx.net> wrote in message
news:cv**********@rl0001.unimaas.nl... The Scddate comes from a control source on a form linked to a date field
of
another table, the answer comes in a calculated texbox.
I tried what you said also and made an unbound textbox on the form as
Scddate, but then I get only random answers.
As it works with you, I gathered it maybe had to do with the date
formatting, so made a second unformatted textbox that gets the date from
the
first textbox, so it shows the datenumber and used this as Scddate, but
got
the same random results.
Can't understand why. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: John Hunter |
last post by:
I have a list of two tuples containing x and y coord
(x0, y0)
(x1, y1)
...
(xn, yn)
Given a new point x,y, I would like to find the point in the list
closest to x,y. I have to do this a...
|
by: tertius |
last post by:
Hi,
I'm trying to round my float total to the nearest .05 cents.
12.01 should produce 12.00
0.14 should produce 0.10
2.28 " 2.25
703.81 " 703.80
...
|
by: nkunapa |
last post by:
Hi:
Is there a way in XPATH to find the nearest node of the node in
context with a certain attribute value. Here is my problem. I have the
following XML and I am trying to add all the nodes with...
|
by: Stewart Allen |
last post by:
I'm trying to find the next value in a table that a student needs to achieve
once he has already passed one grade.
*tblStudents*
StudentID (PK)
FirstName
*tblGradingDates*
DateID ...
|
by: Stewart Allen |
last post by:
Hi,
I'm having trouble with the DMin function in finding the next date in the
table that is greater than or is the same the date entered;
The dates in the table's field are
04/18/02
06/16/02...
|
by: Gompie |
last post by:
Why does the function DMin("Abs(!- #" & &
"#)";"SomeTable") not work properly with datefields.
It always finds the closest difference with a later date only, if an earlier
date in the table is...
|
by: Valvalis |
last post by:
Hello,
I am trying to set a class attribute of a text.item element to the value of its nearest ancestor. I want to do this in the case that the class of the text.item is currently a blank string....
|
by: Ed Marzan |
last post by:
Greetings,
I have a query that returns varying prices for the same item in the
following manner.
Item1 Price1
Item1 Price2
Item1 Price3
Item1 Price4
|
by: lindabaldwin |
last post by:
Hi everyone,
I have a code that I can use in Excel to round to the nearest 0.5, but I cannot get it to work in Access. When I try to execute it I get the following error message: "Compile error:...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
