Nearest value with Dmin

[Gompie]

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 Morgan]

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 Morgan]

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?

[Gompie]

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 Morgan]

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?
>
>

[Gompie]

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?
>
>

[Wayne Morgan]

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?
>> >
>> >
>>
>>
>
>

[Gompie]

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

[Wayne Morgan]

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.