First time, so please be patient with me. This is not the most eloquent way of doing it, but it's effective. What you can do is make a column in each table that can be used for your joins with the same datatype and number.
Your first table with the datetime time, it actually is a number, a double somewhere between zero and one. Access/Excel/Yadda store datetimes as a double with 1=1day. Your time is actually being stored as the (number of seconds in your time)/86400 (the number of seconds in a day). To see this, create a column in this table, and populate it with an update query with cdbl([time]). For my example, the time 01:23 comes out to the double .057638888889.
Your second table is the tricky one. With that formatting, you are actually storing a time liek 15:45 as an interger 1545. But in order to get it to match, we have to seperate the hour from the minute, figure out how many seconds that is, then divide it by 86400. I did it with the following formula: ((Int([time]/100)*3600)+(60*([time] Mod 100)))/86400. Create a column in your second table and populate it with this formula.
So basically, you join on these two create columns, or CDbl([table1].[time])=((Int([table2].[time]/100)*3600)+(60*([table2].[time] Mod 100)))/86400
I got it to work, but like I said, there are other ways. You could take the double from the CDbl and multiply by 86400, then convert that integer of number of seconds into the integer version of table 2. Or you could do a CDate conversion on that big long expression in table 2 and link that to the time in table 1.
Hope that helped.
Sign In
Create Account
