How to join date/time field when "days" are same but "time" differ

Good evening everyone,

I have one TRANSACTION table that contains:
1."date/time" field (YYMMDD hh:mm:ss) 
2."quantity field" 

And another PRICE table that contans:
1."date/time" field (YYMMDD hh:mm:ss) 
2."price field" 

Now, I want to join the "date/time" fields so I can perform a simple 
"quantity * price" calculation on each transaction-row.

But the time differes (hh:mm:ss) and I just can't find a way to make Access 
accept a relation between the days (it outputs nothing). How can I make it 
ignore difference in the "time-section" and only look at the YYMMDD section?

Kindly,
Mikael
Sweden
0
Utf
11/8/2007 10:17:00 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
1172 Views

Similar Articles

[PageSpeed] 38

Use the DateValue function.

I have to question why your Price table has a single Date/Time value in it, 
though. Shouldn't you perhaps have EffectiveFrom and EffectiveTo fields, and 
you'd then match the transaction's date using TransactionTime BETWEEN 
Price.EffectiveFrom AND Price.EffectiveTo?

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Mikael Lindqvist" <MikaelLindqvist@discussions.microsoft.com> wrote in 
message news:2A455D89-C105-4C26-8823-56559F2FAA1A@microsoft.com...
> Good evening everyone,
>
> I have one TRANSACTION table that contains:
> 1."date/time" field (YYMMDD hh:mm:ss)
> 2."quantity field"
>
> And another PRICE table that contans:
> 1."date/time" field (YYMMDD hh:mm:ss)
> 2."price field"
>
> Now, I want to join the "date/time" fields so I can perform a simple
> "quantity * price" calculation on each transaction-row.
>
> But the time differes (hh:mm:ss) and I just can't find a way to make 
> Access
> accept a relation between the days (it outputs nothing). How can I make it
> ignore difference in the "time-section" and only look at the YYMMDD 
> section?
>
> Kindly,
> Mikael
> Sweden 


0
Douglas
11/8/2007 10:37:13 PM
Well, that is certainly one way to do it. But since my price will vary almost 
with every day of the month I believe relation link between "transaction 
table" and "price table" is the most convinient approach (I don't want to 
"hard-code" between intervals).

So my question again, is it possible to match DATE/TIME even if the "time 
part" varies (hours, minutes, seconds). I want the relation to ONLY look at 
the YYMMDD part!

Kindly,
Mikael

"Mikael Lindqvist" wrote:

> Good evening everyone,
> 
> I have one TRANSACTION table that contains:
> 1."date/time" field (YYMMDD hh:mm:ss) 
> 2."quantity field" 
> 
> And another PRICE table that contans:
> 1."date/time" field (YYMMDD hh:mm:ss) 
> 2."price field" 
> 
> Now, I want to join the "date/time" fields so I can perform a simple 
> "quantity * price" calculation on each transaction-row.
> 
> But the time differes (hh:mm:ss) and I just can't find a way to make Access 
> accept a relation between the days (it outputs nothing). How can I make it 
> ignore difference in the "time-section" and only look at the YYMMDD section?
> 
> Kindly,
> Mikael
> Sweden
0
Utf
11/9/2007 6:49:00 AM
Use the DateValue function.  It strips off the time.  It does require that 
you give it a non-null valid date or date string.

SELECT Transaction.Date
, Transaction.Quantity
, Price.Price
, Transaction.Quantity * Price.Price as ExtendedPrice
FROM Transaction INNER JOIN Price
ON DateValue(Transaction.Date) = DateValue(Price.Date)

Another way to do this would be to build 3 queries
SELECT DateValue([Date]) as DateOnly, Quantity
FROM Transaction

SELECT DateValue([Date]) as DatePrice, Price.Price
FROM Price

Now join those two queries on DateOnly and DatePrice

-- 
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Mikael Lindqvist" <MikaelLindqvist@discussions.microsoft.com> wrote in 
message news:2B0521ED-D4B5-4C55-916F-7797DFDA6823@microsoft.com...
> Well, that is certainly one way to do it. But since my price will vary 
> almost
> with every day of the month I believe relation link between "transaction
> table" and "price table" is the most convinient approach (I don't want to
> "hard-code" between intervals).
>
> So my question again, is it possible to match DATE/TIME even if the "time
> part" varies (hours, minutes, seconds). I want the relation to ONLY look 
> at
> the YYMMDD part!
>
> Kindly,
> Mikael
>
> "Mikael Lindqvist" wrote:
>
>> Good evening everyone,
>>
>> I have one TRANSACTION table that contains:
>> 1."date/time" field (YYMMDD hh:mm:ss)
>> 2."quantity field"
>>
>> And another PRICE table that contans:
>> 1."date/time" field (YYMMDD hh:mm:ss)
>> 2."price field"
>>
>> Now, I want to join the "date/time" fields so I can perform a simple
>> "quantity * price" calculation on each transaction-row.
>>
>> But the time differes (hh:mm:ss) and I just can't find a way to make 
>> Access
>> accept a relation between the days (it outputs nothing). How can I make 
>> it
>> ignore difference in the "time-section" and only look at the YYMMDD 
>> section?
>>
>> Kindly,
>> Mikael
>> Sweden 


0
John
11/9/2007 12:36:40 PM
I told you: use the DateValue function.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Mikael Lindqvist" <MikaelLindqvist@discussions.microsoft.com> wrote in 
message news:2B0521ED-D4B5-4C55-916F-7797DFDA6823@microsoft.com...
> Well, that is certainly one way to do it. But since my price will vary 
> almost
> with every day of the month I believe relation link between "transaction
> table" and "price table" is the most convinient approach (I don't want to
> "hard-code" between intervals).
>
> So my question again, is it possible to match DATE/TIME even if the "time
> part" varies (hours, minutes, seconds). I want the relation to ONLY look 
> at
> the YYMMDD part!
>
> Kindly,
> Mikael
>
> "Mikael Lindqvist" wrote:
>
>> Good evening everyone,
>>
>> I have one TRANSACTION table that contains:
>> 1."date/time" field (YYMMDD hh:mm:ss)
>> 2."quantity field"
>>
>> And another PRICE table that contans:
>> 1."date/time" field (YYMMDD hh:mm:ss)
>> 2."price field"
>>
>> Now, I want to join the "date/time" fields so I can perform a simple
>> "quantity * price" calculation on each transaction-row.
>>
>> But the time differes (hh:mm:ss) and I just can't find a way to make 
>> Access
>> accept a relation between the days (it outputs nothing). How can I make 
>> it
>> ignore difference in the "time-section" and only look at the YYMMDD 
>> section?
>>
>> Kindly,
>> Mikael
>> Sweden 


0
Douglas
11/9/2007 10:51:29 PM
Reply:

Similar Artilces:

I need an excel spreadsheet timeline with days of the month in co.
I need an excel spreadsheet timeline with days of the month horizontally in columns for minimum three to four months. I'm using the rows for arrows stretching across the days. I created one, but it's difficult to work with. Would like an easy one. Thank you. Hi maybe check out the template gallery at microsoft.com -- Regards Frank Kabel Frankfurt, Germany "Shinecatcher" <Shinecatcher@discussions.microsoft.com> schrieb im Newsbeitrag news:25339288-4BA3-4EE1-B86A-805837F2D9F5@microsoft.com... > I need an excel spreadsheet timeline with days of the month horizo...

Bug in CDateTimeCtrl for 2 days only?
Our application is exhibiting a strange bug today out of the blue. The CDateTimeCtrl is exhibiting a strange behavior. I have it set so that the format is MMMM YYYY so we don't see the day. If it's set as a spin control or if you use the arrows to inc/dec the month it doesn't show February. It goes from January to <blank) then to March. By default it's populated with today's date. Jan 30 and Jan 31 cause problems. It's behaving like it think the month is actually February where 30th and 31st don't exist. Shouldn't the control automatically i...

Calculating Time/Days worked
Hope you will bare with me while I explain. Once again I am working with a sheet, or series of sheets that hel employees calculate their time for days and hours worked. I decided against the protection button, as it was more of a proble with employees unchecking it. So, I am left with one last functio before it is complete. Let me remind you wonderful people I am new to excel and this is m first real project. Ok, my last 'function' I would like is this... Paydays are calculated from the 11th of each month to the 25th, the again from the 26th of each month to 10th of the the next month...

Suming Up Time over the course of days
I have a sheet the contains event durations on specific days. Some days have more than one event. I would like to add up all the event times for each day on a second sheet and (line) chart the results there. The list get longer with each event. Tuesday Nov.2 2010 10:16 am 00:37:22 Tuesday Nov.2 2010 08:28 am 00:15:48 Monday Nov.1 2010 08:30 am 01:03:07 Sunday Oct.31 2010 06:36 pm 00:09:20 Sunday Oct.31 2010 06:17 pm 00:03:26 Sunday Oct.31 2010 05:27 pm 00:07:42 Sunday Oct.31 2010 11:15 am 00:27:19 Sunday Oct.31 2010 08:56 am 00:45:26 Saturday Oct.30 2010 12:44 pm ...

How to join date/time field when "days" are same but "time" differ
Good evening everyone, I have one TRANSACTION table that contains: 1."date/time" field (YYMMDD hh:mm:ss) 2."quantity field" And another PRICE table that contans: 1."date/time" field (YYMMDD hh:mm:ss) 2."price field" Now, I want to join the "date/time" fields so I can perform a simple "quantity * price" calculation on each transaction-row. But the time differes (hh:mm:ss) and I just can't find a way to make Access accept a relation between the days (it outputs nothing). How can I make it ignore difference in the "ti...

Outlook 2003 POP save on server x days
Hi, In Outlook 2003 - Accessing POP3 Mail - When you select save on server x number of days - When does the time count for days begin? Lets say I have old E-Mails in Outlook - and on the server - I choose to leave on server 30 days - Will all E-Mail older than 30 days be removed from the server - immediately? Or will the time count 30 days from when I chose to leave them on the server - so 30 days from when I chose to leave on server for x days - they will begin being removed from the server? Thank You it's 30 days from the date you downloaded them into outlook. I...

default appointment times problems
When i re-open outlook and go to the calender, all the appointments times have changed to 1am on the start day - 1am on the day after it waqs meant to finish. So a 12 day appointment is now shown as over 2 days. How do you change the default appointment time settings so an appointment which is just typed into the day is from 9am - 5pm for example and will stay as a 1 day event when opening and closing outlook? All day events are 12 -12, not 9-5. If you want it 9-5, you need to make it for 9-5. All day appointment change to 1 -1 if you change the time zone or DST settings aft...

O2003: All-day-events are spread over 2 days after changing time zone of Windows computer!
Hello Group, I have moved to a country with a different time zone (+1h the the former one). After changing my time zone in Windows, all my All-day-events in Outlook have been changed to Non-all-day-events that spread over 2 days, e.g.: Start time: Fr, 06.04, 01:00 End time: Sa, 07.04, 01:00 This is very annoying. Has anybody a solution to that? Thanx for any advice! Best regards Tom ...

add 4 days to date/time
Hi Have had help with this, but still short of goal criterea 1) Weekday anything after 1600 is classed as next day's date 2) anything after 1600 on friday, classed as Monday 3) sat and sun is classed as monday Need to add 4 days Examples fri 3/3 07:00:00 result should be 9/3 fri 3/3 16:51:00 result should be 10/3 sat 4/3 any time result should be 10/3 sun 5/3 any time result should be 10/3 Mon 6/3 00:51:00 result should be 10/3 Any help, direction would be appriciated regards -- bnt Message posted via http://www.officekb.com Sorry Brian, I though...

Elapsed time Calc...over 30 days long... #2
B2 C2 D2 09/29/04 9:59 AM 11/30/04 05:23 PM 2 Days 08 Hours 02 Minutes D2's formula is C2-B2. D2's custom format is d "Days" hh "Hours" mm "Minutes" I'd like this to correctly calculate. Later, I'd like to calculate based on work hours (8am until 6pm), take out weekends and holidays. I've tried to use Networkdays, but it has not come out correctly yet. You can email me at SeanRinVA at gmail dot com Thanks! ...

Elapsed date & time in terms of days, to the second
Hello, I receive a spreadsheet on a daily basis, and one column has a start date, and then a corresponding column has the start time for that date. Also, there is a third and fourth column with end dates in one, and end times in another. I would to calculate the elapsed time between the start and end dates, to the nearest second, weekends not to be included. For example A1: Fri. 19-Nov-04, B1: 11:00:00 a.m. C1: Mon. 22-Nov-04, D1: 11:00:00 p.m. So the elapsed work time would be 1.5 days If anyone knows how this can be accomplished, no matter the complexity of the formula...

Count number of days that fall on a weekend or in a time range?
Hello! I am trying to reconcile my cell phone bill. I have copied and pasted it into Excel 2003. I want to find all entries that fall on Saturday or Sunday, cut/paste that portion into another area of the sheet, then sum the number of minutes used on those days. I also want to find all entries that fall between 9:00PM and 6:00AM in the remainder of the bill and sum those entries. Any suggestions? Thank you for helping! Gregg Hill Hi lets assume the following: - column A: your dates as Excel date format (check this with =ISNUMBER(A1) -> should return TRUE) - column B: the starting ti...

On POS (not RMS) Adjusting Time Clock Entry Defaults to Todays Date
When an employee negelects to time out and we make adjustments on a subsequent day, the Time Clock display will default to today's date even though the correct date is selected in the Employee's Time Clock window. You have to select Save and Close, then reopen the Time Clock event, then adjust the time out time to the correct date. Anybody else notice this bug? M Kalmus Dogtooth Coffee Company ...

Adding up days from Date/Time entered
I'm new to Access and have a Table where I'd like to put in a Beginning date that an item was sent out on rent, and then put a date where it was returned from being on rent. I'd like to set up a record that sums the number of days that item was out, based on the previous two records. I'm looking for this to be shown in the Form view, as a user is putting in the dates. Thank you Jeffrey, Look at the DateDiff function in the Help file. Steve santus@penn.com "Jeffrey Wilson" <Jeffrey Wilson@discussions.microsoft.com> wrote in message ...

Start Time and End time it maybe for 1 day or it maybe for 2 days
Here is my sheet. I go out Jan 1 @21:00 and get back Jan 2 @09:00 then the next day out @09:00 and back in @21:00. Is there a way to write a formula that will do nothing if C2 is empty then go to the next Day ( C3 ) and count the hours for D3? at the same time if i was to go out and back in like cell B4 on the same day. So i need to have a formula to work for both in one cell and have it choose which one to use. Not sure how i would put it. Should i use IF, OR, AND. this one has me thinking. Who knows it just might be simple. Any ideas???? A B C ...

OWA Works only 2 days at a time
I'm running SBS 2003 with Exchange 2003, and I frequently access my mailbox from outside over the web using OWA. My problem is that if I haven't rebooted the server within the last 2 days or so OWA won't work. I get a "Page can not be displayed message" in my browser. If I then reboot the server, OWA works fine for about the next 2 days. Does anyone know how to fix this? ...

Working time and days
I would like to be able to caculate working time when the cell format is 4/1/2005 6:40:26 AM (not seperated into date and time) Cell A1 dd/mm/yyyy h:mm:ss (First Day) Cell A2 dd/mm/yyyy h:mm:ss (Last day) Working hours would be 8:00 to 17:00 Public holidays need to be taken out too The result should be working days hours and minutes or just hours and minutes, so that Saturday, Sunday, public holidays & between the hours of 17:00 to 8:00 to the next working day would not count. I've tried using: =((NETWORKDAYS(A1,C1)-2)*9)/24+TIME(17,00,0)-B1+D1-TIME(8,00,0) But I think this ...

How to exclude weekends/holidays from plotted time series
Excel is inserting holidays and weekend days into my time series. I want to stop it doing this and to use only my date series. The x-axis has defaulted to a time-scale. To change the setting: Select the chart Choose Chart>Options Select the Axes tab For Category (X) axis, choose Category, click OK Debbie424242 wrote: > Excel is inserting holidays and weekend days into my time series. I want to > stop it doing this and to use only my date series. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html Thanks - this enables me to plot the ...

Count-down timer? (e.g. 10 days left...9...8...)
Is there a way to program a count-down timer in Excel? For example, say I need to buy toner cartridges every 3 months, and I just bought some. Then I can reset the timer, and it'll indicate another 3 months. When the three months are up, another cell will turn go from "Toner in stock" to "Buy toner." Does anyone know how to program excel to do something like this? Thanks! I wish. all forms have a timer event but unfortunately it can be use only on 1 event. some of my users are buyers and they have requested this also but the most i can do is use the info the...

Calculate Date and Times (based on Business days)
I have two date & time fields that I need to calcualte the number of days line is open. I have my first data point in B2 and the one to subtract is in Q2. Thanks, Diane apology but I don't understand your requirement..... -- Hope this help Please click the Yes button below if this post have helped answer your needs Thank You cheers, francis "Diane" wrote: > I have two date & time fields that I need to calcualte the number of days > line is open. I have my first data point in B2 and the one to subtract is in > Q2. > ...

calculate # of days from two different times
If A1=Date Reported, B2=Date Resolved, B3=Resolution Period What formular should I use to: 1) calculate the resolution period in days, excluding weekends and public holidays. 2) display "pending" in the Resolution Period cell if the Date Resolved cell does not have a time value 3) display "impossible" in the Resolution Period cell if the value in the Date Resolved Cell is less than the value in the Date reported cell 4) display "invalid" in the Resolution Period cell if either the Date Resolved Cell or the Date Reported cell has no values. Please assist... -...

no appt. times for family birthdays
How do I set up a list of family birthdays and anniversaries in my Outlook 2003 Calendar without having to create an appointment time that shows up on the display? make it an all day appointment? -- "angieking" <angieking@discussions.microsoft.com> wrote in message news:C7FDC419-8FC0-4C8C-8CA2-0EEBCACC6111@microsoft.com... > How do I set up a list of family birthdays and anniversaries in my Outlook > 2003 Calendar without having to create an appointment time that shows up > on > the display? ...

Display calculated time in Months and Days
XP, Access 2003, Novice I am trying to display the time to the next record update in Months AND Days. Here is the control source property of the text box: =IIf(Date()=[UpdateNext],1,DateDiff("d",Date(),[UpdateNext])) UpdateNext is a short date. But DateDiff is monolingual: Days OR Months OR Years. Can this be done in a query? Thanks Peter No it can't be done in a query and there is no vb function. But Graham Seach wrote one that does the job: See "A More Complete DateDiff Function" at http://www.accessmvp.com/djsteele/Diff2Dates.html With...

Calculate difference in hours of actual day/time data versus fixed weekdays
Hi, I am faced with a problem of performing calculations on dates and weekdays. I have a planned schedule of events that occur regularly on a particular day of the week. (e.g every Wed 02:00). I am currently performing an analysis based on realtime data with the following format "Thu 1/15/2004 17:00" (ddd mm/dd/yyyy hh:mm). I need to calculate by how many hours the event is early or late. See below for an illustration: Event | Planned Time | Instance 1 of event | | |Actual Difference| -----------------------------------------------------...

Brithdays & Anniversaries get recreated from time to time
Apparently Outlook occasionally (I guess at the begin of a year) recreates recurring appointments from the birthdays and anniversaries that it finds in my Contacts-entries. What's annying about this is, that for lots of birthdays I now have duplicates, i.e. TWO appointments. And what's desastrous is that all these new appointments have a reminder set at 12pm of that day. Thus, in the last couple of days repeatedly the alarm of my PDA has gone off to remind me of some friends birthday and woke up me and my wife at midnight (which each time caused some major quarrel and my PDA a...