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  2752 
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: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: kcodez |
last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: DJRhino1175 |
last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this -
If...
|
by: Rina0 |
last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
|
by: DJRhino |
last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer)
If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _
310030356 Or 310030359 Or 310030362 Or...
|
by: lllomh |
last post by:
Define the method first
this.state = {
buttonBackgroundColor: 'green',
isBlinking: false, // A new status is added to identify whether the button is blinking or not
}
autoStart=()=>{
|
by: lllomh |
last post by:
How does React native implement an English player?
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
| |
