DlookUp on two tables using Double As ID (1 Viewer)

[LjushaMisha]

Hello.
I have 2 tables. One is [tblTimes] with two fields, first field is [IdTime], wich is actually a numeric value of, for example, 6/24 = 0,25. --> field size = Double. The second field is text and the value is 06:00. First part of table:
IdTime MyTime
0,00000 00:00
0,01042 00:15
0,02083 00:30
0,03125 00:45
and so on

In the other table I'd like to dLookp [MyTime] using following line in query field:
Time: DLookUp("[MyTime]";"tblTimes";"[IdTime] = " & [table02].[id02Time])
(you have probably noticed I'm using "continental" setups.

If I change [IDTime] and [ID02Time] to Integer everything works great, but it doesn't have a sense.

You my also ask me why using this type of table, why not using whole numbers, etc.

But mainly I'm interested what is "WRONG" with DOUBLE field size, format STANDARD, Decimal Places: 6 (normally in both tables same setups. Also I've established JOIN PROPERTIES to INNER JOIN - show records equal in both tables.

ANY HELP ??? PLEASE!!!! :banghead:

 

[theDBguy]

Hi. I think you said you want to use DLookup() at the table level, is this correct? If so, I am not sure this is possible. If we're talking about creating a "calculated" column in the table design, I am not sure it can refer to another record within the same table, much less a record from another table.

 

[LjushaMisha]

No.
Trying to "connect" two tables in query. As I said, with [IDTime] = Integer type it works great. (I've done it just as check experiment)
With [IDTime] = Double type (which is "must be") I get ERROR in every record

 

[theDBguy]

No.
Trying to "connect" two tables in query. As I said, with [IDTime] = Integer type it works great. (I've done it just as check experiment)
With [IDTime] = Double type (which is "must be") I get ERROR in every record

Can you post the complete SQL of the query, please? Thanks.

 

[LjushaMisha]

SELECT Table02.Id2Order, Table02.Buyer, Table02.Place,
LookUp("[MyTime]","tableTimes","[idTime] = " & [table02].[id02Time])
AS TimeNew, Table02.Quantity
FROM tblTimes INNER JOIN Table02 ON tblTimes.IdTimes = Table02.Id02Time;

I hope it is OK. I've made translation from my language names of tables and fields

 

[theDBguy]

I hope it is OK. I've made translation from my language names of tables and fields

Hi. What do you get if you tried it this way?

[FONT=Consolas][FONT=Consolas]SELECT Table02.Id2Order, Table02.Buyer, Table02.Place, tblTimes.MyTime [/FONT][FONT=Consolas]AS TimeNew, [/FONT][FONT=Consolas]Table02.Quantity[/FONT]
[FONT=Consolas]FROM tblTimes [/FONT][/FONT]
[FONT=Consolas][FONT=Consolas]INNER JOIN Table02 [/FONT][/FONT]
[FONT=Consolas][FONT=Consolas]ON tblTimes.IdTimes = Table02.Id02Time;[/FONT][/FONT]

 

[The_Doc_Man]

TheDBguy gave you a sample to look at. I'm going to take the other road and tell you what I see that might make this not work.

SELECT Table02.Id2Order, Table02.Buyer, Table02.Place,
LookUp("[MyTime]",[COLOR="Red"]"tableTimes"[/COLOR],"[idTime] = " & [table02].[id02Time])
AS TimeNew, Table02.Quantity
FROM [COLOR="RoyalBlue"]tblTimes[/COLOR] INNER JOIN Table02 ON tblTimes.IdTimes = Table02.Id02Time;

You are using two different names for tables here. If this was merely a typo then OK - but if the names actually WERE different, then there is a place for things to go wrong by naming a non-existent table. And DLookup doesn't give the best error messages - it just returns nulls when it can't find things.

So ... WERE those table names actually different (but should not have been)?

But mainly I'm interested what is "WRONG" with DOUBLE

A second comment - about seconds... Using a Date field or a DOUBLE that relates to a date and time means you are going to be subject to issues of rounding. A DOUBLE has something like 53 bits of mantissa. So there are just over 86k seconds (but less than 128k) in a day, meaning you consume 17 bits for time. The date in 2019 is greater than 32k but less than 64k (as an integer number of days since the reference date), so dates consume another 16 bits. I.e. 33 bits out of 53 available bits leaves 20 bits of fractional seconds in that DOUBLE. The problem is, keeping time in sexagesimal format means dividing by 60 TWICE (and by 24 once), and one of the factors of 60 is 5, which in binary results in a repeating decimal ('cause 5 isn't an even number). So there will be rounding factors to consider as the result of diddling with repeating decimals.

It is theoretically possible for the date/time conversion routines to return the same date and time for literally half-a-million different mantissas. That half-million different mantissas will display as the exact same time in seconds but will have different fractions of a second. Unfortunately, making an exact match situation (the JOIN) isn't going to work so well for fields of type DOUBLE or type DATE because of the nature of time computations.

Changing times to some integer format works perfectly because integers have no fractions, so exact matches will be trivial.

 

[LjushaMisha]

Hi. Sorry not answering you immediately but it was 23:00 hours at me and I went to sleep.

1. Yes, typing mistake while "Translating"
2. Nubers produced by dividing with 24 are already ROUNDED

Other. This is only "study" case. I'm following tutorials of Mr. Steve Bishop on YouTube (Chanel All In One, VBA Intermediate series - episode 2 https://www.youtube.com/watch?v=gatYg5cDDnI&list=PLLNPs0V_Il0-agYJKDyuv0RnPcSXz7oHd&index=2)
and he succedeed to do it.) So I was following his instructions and I can't reach the goal. On the screen (and also looking the syntax of the DLookUp statement) he is dealing with numbers not text.

So I made database called Forum.accdb which is sample database in which you have two queries. One getting ERROR and other working good.

 

[LjushaMisha]

To The_Doc_Man, to TheDBguy:

SQL query suggested by theDBguy worked perfectly (THANKS), and I could call the Thread SOLVED.

BUT I'D STILL LIKE TO FIND ANSWER REGARDING NON WHOLE NUMBER AS PRIMARY KEY ID FIELD AND DLOOKUP FUNCTION

 

[LjushaMisha]

TheDBguy gave you a sample to look at. I'm going to take the other road and tell you what I see that might make this not work.

SELECT Table02.Id2Order, Table02.Buyer, Table02.Place,
LookUp("[MyTime]",[COLOR=red]"tableTimes"[/COLOR],"[idTime] = " & [table02].[id02Time])
AS TimeNew, Table02.Quantity
FROM [COLOR=royalblue]tblTimes[/COLOR] INNER JOIN Table02 ON tblTimes.IdTimes = Table02.Id02Time;

You are using two different names for tables here. If this was merely a typo then OK - but if the names actually WERE different, then there is a place for things to go wrong by naming a non-existent table. And DLookup doesn't give the best error messages - it just returns nulls when it can't find things.

So ... WERE those table names actually different (but should not have been)?



A second comment - about seconds... Using a Date field or a DOUBLE that relates to a date and time means you are going to be subject to issues of rounding. A DOUBLE has something like 53 bits of mantissa. So there are just over 86k seconds (but less than 128k) in a day, meaning you consume 17 bits for time. The date in 2019 is greater than 32k but less than 64k (as an integer number of days since the reference date), so dates consume another 16 bits. I.e. 33 bits out of 53 available bits leaves 20 bits of fractional seconds in that DOUBLE. The problem is, keeping time in sexagesimal format means dividing by 60 TWICE (and by 24 once), and one of the factors of 60 is 5, which in binary results in a repeating decimal ('cause 5 isn't an even number). So there will be rounding factors to consider as the result of diddling with repeating decimals.

It is theoretically possible for the date/time conversion routines to return the same date and time for literally half-a-million different mantissas. That half-million different mantissas will display as the exact same time in seconds but will have different fractions of a second. Unfortunately, making an exact match situation (the JOIN) isn't going to work so well for fields of type DOUBLE or type DATE because of the nature of time computations.

Changing times to some integer format works perfectly because integers have no fractions, so exact matches will be trivial.

Re: DlookUp on two tables using Double As ID
Hi. Sorry not answering you immediately but it was 23:00 hours at me and I went to sleep.

1. Yes, typing mistake while "Translating"
2. Nubers produced by dividing with 24 are already ROUNDED

Other. This is only "study" case. I'm following tutorials of Mr. Steve Bishop on YouTube (Chanel All In One, VBA Intermediate series - episode 2 https://www.youtube.com/watch?v=gatY...Xz7oHd&index=2)
and he succedeed to do it.) So I was following his instructions and I can't reach the goal. On the screen (and also looking the syntax of the DLookUp statement) he is dealing with numbers not text.

So I made database called Forum.accdb which is sample database in which you have two queries. One getting ERROR and other working good.
Attached Files

Forum.accdb (1.48 MB, 3 views)

 

[theDBguy]

To The_Doc_Man, to TheDBguy:

SQL query suggested by theDBguy worked perfectly (THANKS), and I could call the Thread SOLVED.

BUT I'D STILL LIKE TO FIND ANSWER REGARDING NON WHOLE NUMBER AS PRIMARY KEY ID FIELD AND DLOOKUP FUNCTION

Hi. I'll try to look at your attachment later, if no one else does. Currently suffering from slow Internet connection at the moment.

 

[LjushaMisha]

thanks in advance. It is not urgent. Take your time.

 

[The_Doc_Man]

In the database that you posted as #10, you have this query called qryDecimalPlaces. I present it below, reformatted for readability and one highlight.

SELECT 
    tblDecimalTable02.Id2Order, 
    tblDecimalTable02.Buyer, 
    tblDecimalTable02.Place, 
    DLookUp("[MyTime]",[COLOR="Red"]"tblTimes[/COLOR]","[IdTimes] = " & [tblDecimalTable02].Id02Time) AS TimeNew, 
    tblDecimalTable02.Quantity
FROM 
    tblDecimalTimes INNER JOIN 
    tblDecimalTable02 ON 
        tblDecimalTimes.IDTime = tblDecimalTable02.Id02Time;

There IS no tblTimes in your posted database. DLookup is returning #Error because there is no such domain as you named in the domain argument of DLookup.

 

[LjushaMisha]

Hi.
Have you tried to replace tblTimes with tblDecimalTimes?
Did it work?
I still get error.

 

[gemma-the-husky]

real numbers (ie, numbers with a portion AFTER the decimal point are risky to use as reliable indexes) The reason is that many numbers cannot be represented perfectly in binary. For example 0.1 cannot be expressed with 100% accuracy as a binary number. Therefore accurately comparing singles and doubles is prone to error. Since time/date is a double, it falls into this category. I really wouldn't expect a lookup with a double to always return the correct answer. Hence it is probably not safe/reliable to expect a dlookup to succeed with 100% accuracy when used with a real number.

General it is better to compare the absolute result of a real number comparison with a small "delta" difference, rather than testing for equality.


eg, I struggle to understand how this returns true for the equality test, but returtns a number not equal to zero <>0 for the expression evaluations.
(although I bet somebody can explain)

Sub check()
Dim a As Single
Dim b As Double

a = 0.1
b = 0.1

MsgBox (a = b)
MsgBox (a - b)
MsgBox (b - a)
End Sub

 

[The_Doc_Man]

Dave, you might try one more test. See if MsgBox (b = a ) returns true or false. Normally we would think of equality as being commutative, but since those variables are of different data types, there is an implied temporary type conversion and the compiler has to pick one (and only one) of them to convert. If it converts b to Single, there's your answer. And the description of the "=" comparison operator suggests that it is "directional" - like everything else in VBA.

 

[gemma-the-husky]

I just tried that test, but it returned true.

 

[LjushaMisha]

OMG. Where have we came with my "simple" question. This also is a way to see how extensive knowledge is needed to work with VBA.

Regarding everything I've decided to make things simpler.
Dividing "time" 12,25 with 24 will ROUNDED on 5 decimal places and I'll get 0,51042
Next I'll multiple the result with 1.000.000
In reality it is not the value behind decimal point what I need. Just need TWO EQUAL ID fields in TWO tables.
This way I'll get INTEGER in both tables and problem will (I hope) disappear.
If needed I'll make other calculations backwords.
And I'll now mark this threat as SOLVED.
Thanks to ALL trying to help me. Some way you did, at least opened my eyes.
Thanks again
LjushaMisha

 

[The_Doc_Man]

Here is a thought for you. There are only 86,400 seconds in a day, so if you are working only on times, that time can be multiplied by 86,400 (=24*60*60) to produce a number between 0 and 86,399, and that you can then convert to a LONG. (Can't convert to an integer because it won't go that high.) If you take the date and convert it to LONG also, it will be an integer in the 40,000+ range. If you can handle the idea of a compound index, you would get two values that are exact.

Here is another thought for you: Convert the times to long time format for both because the automatic time formatting routines will truncate the fractions that are probably giving you headaches. Normally you would want to use a date variable for this purpose, but in your case that JOIN requirement makes this a special case. If both times are in long time format, they should match if they really ARE the same time.

 

[The_Doc_Man]

And one more idea: So you have these times. Convert them to text and then back to the DOUBLE. The conversion to text will give you a string that ends in units of seconds with no issues. The conversion back to DOUBLE should be stable in the sense that if you convert some time like "09:38:00" you will ALWAYS get back the same fraction. While using a DOUBLE is iffy for the general case, if you take steps to assure a "stable" conversion process, you should be able to assert the relationship.