By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,325 Members | 1,920 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,325 IT Pros & Developers. It's quick & easy.

Opposite of Union Query

P: 29
I saw a post about a year ago, that pondered if an opposite to a union query was possible, i.e. an intersect query. however, the OP was concerned about varous fields intersectiong with one another.

i have a different approach.

i have a table of records. there is a date field a start time field and an end time field. (not together, due to outside programming compatability issues) i want to be able to sort through the records through a block of time. i.e. all records that started on day 1 after 3 am, but finished on day 2 before 6 pm.

i tired the having clause, but i don't have an aggregarate function. i tried a where date>start tate and start time >= start time and date <= end date and endtime <= end time.

hwoever, if if something happened after the end time after the start date, but was before the end date, it won't show up,

say i have these events

Date Start Time end time
1/1/2000 1:00 PM 1:53 PM
1/1/2000 6:53 PM 7:35 PM
1/2/200 4:45 PM 5:30 PM

and i wanted events between noon on 1/1/2000 and 6:00 PM on 1/2/200, i would miss 1/1/200 6:53 because the end time was after end time for the econd date, but it was still in my range of accecpted values.
Nov 28 '06 #1
Share this Question
Share on Google+
17 Replies


nico5038
Expert 2.5K+
P: 3,072
First I would like to advise you to change the fieldname Date into another name, e.g. RecDate.
Next you need to make sure to use a WHERE clause and that the comparison is performed on a datefield with a time, else Access will use 00:00:00 for the time...

When still uncertain, post your query with a description of the erroneous results here so we can have a look.

Nic;o)
Nov 28 '06 #2

NeoPa
Expert Mod 15k+
P: 31,661
As we don't have the comparison dates and times you want to select from I'll use a couple of prompt fields :
[Enter Start Date/Time] and [Enter End Date/Time].
I'm also assuming that the [Date] field you mention is in Date/time format but with no time part and that [Start Time] and [End Time] are strings.
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Table
  3. WHERE (([Enter End Date/Time]>=[Date] + CDate([Start Time]))
  4.   AND ([Enter Start Date/Time]<=[Date] + CDate([End Time])))
I've used your field name ([Date]) here but support Nico's suggestion that it's a name to be avoided as you'd want to avoid the problems it will bring you.
Nov 28 '06 #3

P: 29
i know i shouldn't use date as a field name, however, that's the way the database needed to be set up. easier for me to wrie a few lines of VBA to deal with the date as a field name, than to change a lot of c code to fix the same problem. (not my doing, i just do what i'm told)

next, here's my VBA code to produce the string.
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT tblCoils.Time, tblCoils.Date, tblCoils.CoilID, tblCoils.CoilDataTable, tblCoils.Grade, tblCoils.Furnace"
  2. strSQL = strSQL + ", tblCoils.TapA, tblCoils.TapB, tblCoils.TapC, tblCoils.TapD, tblCoils.Program, tblCoils.VaLimit"
  3. strSQL = strSQL + ", tblCoils.IaLimit, tblCoils.KwLimit, tblCoils.ShimsFld, tblCoils.BowFld, tblCoils.HeatingDelay"
  4. strSQL = strSQL + ", tblCoils.OperHeatDelay, tblCoils.VMMString, tblCoils.TargetDoTemp, tblCoils.ActualDoTemp, tblCoils.PeakFront"
  5. strSQL = strSQL + ", tblCoils.PeakBack, tblCoils.[4thPassTemp], tblCoils.HeatStartTime, tblCoils.FinishTime, tblCoils.TotalHoldTime"
  6. strSQL = strSQL + ", tblCoils.LastFinishTime, tblCoils.StandardHeatTime, tblCoils.Thickness, tblCoils.Width, tblCoils.Weight"
  7. strSQL = strSQL + ", tblCoils.MinFront, tblCoils.MinBack, tblCoils.ChargeTemp, tblCoils.Report FROM tblCoils "
  8. strSQL = strSQL + "WHERE (((tblCoils.Date)>=#" & [StartDate] & "# And (tblCoils.Date)<=#" & [EndDate] & "#) AND ((tblCoils.HeatStartTime)>=#" & [StartTime] & "#) AND ((tblCoils.FinishTime)<=#" & [EndTime] & "#));"
the form has 3 data entr boxes, StartDate, EndDate, StartTime, EndTime.
This is what i'm using for my data entry
SD = 12/15/03
ST= 2AM
ED=11/20/06
ET=11:53PM
(note, these are correctlly entyered in the form. the dates and times are Medium formats)

my records have the follwoing Fields (Date - ST - ET)
1)12/15/2003 ----- 11:18:43 P ---- 1:48:AM (next day)
2)6/30/90 ----------- 2:48:43 P ----- 5:18:43 P
3)1/5/93 ------------- 3:48:43 P ----- 7:18:43 P
4)2/18/95 ----------- 4:48:43 P ----- 5:14:43 P
5)11/20/2006 ------ 5:48:443 P --- 7:18:43 P
6)11/28/2004 ------ 1:30:00 A ---- 11:55:00 PM
7)9/14/2006 -------- 6:48:43 P ---- 11:18:43 P

i should get records 1, 5, 6, 7 however i don't get record 6 back.

is that clear?
Nov 29 '06 #4

nico5038
Expert 2.5K+
P: 3,072
Hmm, lets try for the WHERE clause:

strSQL = strSQL + "WHERE (tblCoils.Date + tblCoils.HeatStartTime between #" & [StartDate] & " " & [StartTime]"# and #" & [EndDate] & " " & [EndTime] & "#) And tblCoils.Date + tblCoils.FinishTime between #" & [StartDate] & " " & [StartTime]"# and #" & [EndDate] & " " & [EndTime] & "#));"

This constructs with the + a datetime start and end field.
The startdate is tested to be between the start and enddate and so is the the enddate. Thus only periods within the start/end datetime will be selected.

Nic;o)
Nov 29 '06 #5

P: 29
ok, so i did some reasearch, and found thought of a round about way of getting what i want. i add a query and a table to my DB.

(i guess i don't need the query because of the basic statements i use, let me know though)

i add a table called 'tblShift' and a query called 'qryShiftTable'

the query will be both a delte, then add, query. i'll delete all old contents, then add the tblCoils (in its entirety) back to it. then i'll cycle through the records to pick and chose what i want. (it should be obvious from my code)

then my orginial query, qrySFRForm, just contains all records in the tblShift table.

so here,s my code for the matter (and it does work)
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command2_Click()
  2.  
  3. Dim strSQL As String
  4. Dim dbsC1894 As Database
  5. Dim rstShift As Recordset
  6.  
  7. Set dbsC1894 = CurrentDb
  8.  
  9. strSQL = "DELETE FROM tblShift;"
  10.  
  11. dbsC1894.Execute (strSQL)
  12.  
  13. strSQL = "INSERT INTO tblShift ( [Time], [Date], CoilID, CoilDataTable, Grade, Furnace, TapA, TapB, TapC, TapD, Program, VaLimit, IaLimit"
  14. strSQL = strSQL + ", KwLimit, ShimsFld, BowFld, HeatingDelay, OperHeatDelay, VMMString, TargetDoTemp, ActualDoTemp, PeakFront, PeakBack"
  15. strSQL = strSQL + ", 4thPassTemp, HeatStartTime, FinishTime, TotalHoldTime, LastFinishTime, StandardHeatTime, Thickness, Width, Weight"
  16. strSQL = strSQL + ", MinFront, MinBack, ChargeTemp, Report ) "
  17. strSQL = strSQL + "SELECT tblCoils.Time, tblCoils.Date, tblCoils.CoilID, tblCoils.CoilDataTable, tblCoils.Grade, tblCoils.Furnace"
  18. strSQL = strSQL + ", tblCoils.TapA, tblCoils.TapB, tblCoils.TapC, tblCoils.TapD, tblCoils.Program, tblCoils.VaLimit, tblCoils.IaLimit"
  19. strSQL = strSQL + ", tblCoils.KwLimit, tblCoils.ShimsFld, tblCoils.BowFld, tblCoils.HeatingDelay, tblCoils.OperHeatDelay, tblCoils.VMMString"
  20. strSQL = strSQL + ", tblCoils.TargetDoTemp, tblCoils.ActualDoTemp, tblCoils.PeakFront, tblCoils.PeakBack, tblCoils.[4thPassTemp]"
  21. strSQL = strSQL + ", tblCoils.HeatStartTime, tblCoils.FinishTime, tblCoils.TotalHoldTime, tblCoils.LastFinishTime, tblCoils.StandardHeatTime"
  22. strSQL = strSQL + ", tblCoils.Thickness, tblCoils.Width, tblCoils.Weight, tblCoils.MinFront, tblCoils.MinBack, tblCoils.ChargeTemp"
  23. strSQL = strSQL + ", tblCoils.Report FROM tblCoils;"
  24.  
  25. dbsC1894.Execute (strSQL)
  26.  
  27. Set rstShift = dbsC1894.OpenRecordset("tblShift")
  28.  
  29. rstShift.MoveFirst
  30.  
  31. Do Until rstShift.EOF
  32.  
  33. If rstShift!Date < [StartDate] Then
  34.     rstShift.Delete
  35.  
  36. Else
  37.     If rstShift!Date > [EndDate] Then
  38.         rstShft.Delete
  39.     End If
  40. End If
  41.  
  42. rstShift.MoveNext
  43. Loop
  44.  
  45. rstShift.Close
  46.  
  47. Set rstShift = dbsC1894.OpenRecordset("tblShift")
  48.  
  49. rstShift.MoveFirst
  50.  
  51. Do Until rstShift.EOF
  52.  
  53. If rstShift!Date = [StartDate] Then
  54.     If rstShift!HeatStartTime < [StartTime] Then
  55.         rstShift.Delete
  56.     End If
  57. End If
  58.  
  59. rstShift.MoveNext
  60. Loop
  61. rstShift.Close
  62.  
  63.  
  64. Set rstShift = dbsC1894.OpenRecordset("tblShift")
  65.  
  66. rstShift.MoveFirst
  67.  
  68. Do Until rstShift.EOF
  69.  
  70. If rstShift!Date = [EndDate] Then
  71.     If rstShift!FinishTime > [EndTime] Then
  72.         rstShift.Delete
  73.     End If
  74. End If
  75.  
  76. rstShift.MoveNext
  77. Loop
  78. rstShift.Close
  79.  
  80. End Sub
so this worked oout, a bit more than i wanted to do, but the queries and whatnot are not time criticle, and there would never be more than say 2000 records (and that's really pushing it, it would probably be uner 1k due to production times and maitenance)
Nov 29 '06 #6

nico5038
Expert 2.5K+
P: 3,072
Much work and using a recordset processing loop is a lot slower than a query.

Did you try the other where clause ?

Nic;o)
Nov 29 '06 #7

P: 29
yeah, i tried the whereclause, but there were parenthese issues. i'm going through now, trying to determine where they are needed, and such.

Much work and using a recordset processing loop is a lot slower than a query.

Did you try the other where clause ?

Nic;o)
Nov 29 '06 #8

P: 29
i edited the whereclause to be strSQL = strSQL + "WHERE ((tblCoils.Date + tblCoils.HeatStartTime) between (#" & [StartDate] & " " & [StartTime] & "#) and (#" & [EndDate] & " " & [EndTime] & "#) And (tblCoils.Date + tblCoils.FinishTime) between (#" & [StartDate] & " " & [StartTime] & "#) and (#" & [EndDate] & " " & [EndTime] & "#));"

that queried, but i didn't get the 2003 now.


yeah, i tried the whereclause, but there were parenthese issues. i'm going through now, trying to determine where they are needed, and such.
Nov 29 '06 #9

P: 29
i edited the whereclause to be strSQL = strSQL + "WHERE ((tblCoils.Date + tblCoils.HeatStartTime) between (#" & [StartDate] & " " & [StartTime] & "#) and (#" & [EndDate] & " " & [EndTime] & "#) And (tblCoils.Date + tblCoils.FinishTime) between (#" & [StartDate] & " " & [StartTime] & "#) and (#" & [EndDate] & " " & [EndTime] & "#));"

that queried, but i didn't get the 2003 now. i think it's because the FinishTime is less than the start time (due to it carring over to the next day). i.e. when Date+finishtime must be between SD.ST and ED/ET, it sees that date+finish when it is, but the finish time is actually the next day, but the code views as the project starting at 11 PM on that day, but Ending at 1:40 AM of the same day, i.e. before it started.

do you see what i'm getting at?

sorry, threads won't let me edit after 5 minutes
Nov 29 '06 #10

nico5038
Expert 2.5K+
P: 3,072
OK, you're paying the price for not using two date/time fields :-)

Try:
strSQL = strSQL + "WHERE ((tblCoils.Date + tblCoils.HeatStartTime) between (#" & [StartDate] & " " & [StartTime] & "#) and (#" & [EndDate] & " " & [EndTime] & "#) And
(tblCoils.Date + tblCoils.FinishTime) + IIF((tblCoils.Date + tblCoils.FinishTime)<(tblCoils.Date + tblCoils.HeatStartTime),1,0) between (#" & [StartDate] & " " & [StartTime] & "#) and (#" & [EndDate] & " " & [EndTime] & "#));"

The added " + IIF(end < start,1,0) " part will add an additional day when the enddate is less as the startdate.

Nic;o)
Nov 29 '06 #11

NeoPa
Expert Mod 15k+
P: 31,661
Just curious, why did you not just use the WHERE clause I laid out earlier?
Did you find it not to work in any way?
Nov 30 '06 #12

P: 29
well, because i didn't understand your vauge explination. next, i know how the d/t works, and yes, i know it's stupid to not have the time and date in the same field (as i said abefore, it's easier for me to deal with it, than to rewrite an entire control program)

i'm gonna try nicos suggestion. i think that may do the trick.

Just curious, why did you not just use the WHERE clause I laid out earlier?
Did you find it not to work in any way?
Nov 30 '06 #13

NeoPa
Expert Mod 15k+
P: 31,661
well, because i didn't understand your vauge explination.
I'm sorry my explanation doesn't match up to your specification.
You should really demand your money back.

I hope it's clear that I find your attitude offensive and reflecting an extraordinary lack of good manners.

It's funny though, I can piece together your response even notwithstanding the apparently random spelling.
Nov 30 '06 #14

MMcCarthy
Expert Mod 10K+
P: 14,534
well, because i didn't understand your vauge explination. next, i know how the d/t works, and yes, i know it's stupid to not have the time and date in the same field (as i said abefore, it's easier for me to deal with it, than to rewrite an entire control program)

i'm gonna try nicos suggestion. i think that may do the trick.
shades234,

Our experts spend some time working out answers to your questions. As this is their own time, which they volunteer, I think it would be more appropriate for you to say ...

"Thank you but I feel the other code would better suit me"

or something along those lines.

It is not good to criticise someone who is trying to help you.
Nov 30 '06 #15

P: 29
well, nico's code worked great. Thank you.

as for everyone else, good job trying. better luck next time.

i never ment to "belittle" or "insult" or whatever anyone's post. in my haste of writing a response, it may appear more hurtful than it really is. all i was attempting to convey was i was unsure of NeoPa's answer. since i had actual lines of code from another poster, i was focusing more on that, only because i can see the flow of the code, rather than the flow of "suggestion." what would you rather follow someone who gave you a good set of specifications and a base blueprint, or someone who said, use a door, some 2x4s, windows, plywood, and shingles. I, for one, would take the blueprint. maybe that's just me and how i work.

I'm sorry if this bothers anyone. it's just how i am.

once again, thanks
Dec 1 '06 #16

NeoPa
Expert Mod 15k+
P: 31,661
I appreciate your point, but when requesting help and someone's time, the least you should do is test and respond to all attempts.
In fact it would have saved everyone some time, but at the end of the day - I'll leave you to worry about your manners.
Dec 1 '06 #17

nico5038
Expert 2.5K+
P: 3,072
Glad I could help.

I see from the other comments that we proved once again that solving problems just using text is hard. A good advice is to keep asking when a comment isn't clear. Assumptions are in this business "killing" and easily lead to unnecessary flames that only produce losers.

Success with your application !

Nic;o)
Dec 1 '06 #18

Post your reply

Sign in to post your reply or Sign up for a free account.