Opposite of Union Query

[shades234]

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.

[nico5038]

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)

[NeoPa]

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.

SELECT *
FROM Table
WHERE (([Enter End Date/Time]>=[Date] + CDate([Start Time]))
  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.

[shades234]

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.

strSQL = "SELECT tblCoils.Time, tblCoils.Date, tblCoils.CoilID, tblCoils.CoilDataTable, tblCoils.Grade, tblCoils.Furnace"
strSQL = strSQL + ", tblCoils.TapA, tblCoils.TapB, tblCoils.TapC, tblCoils.TapD, tblCoils.Program, tblCoils.VaLimit"
strSQL = strSQL + ", tblCoils.IaLimit, tblCoils.KwLimit, tblCoils.ShimsFld, tblCoils.BowFld, tblCoils.HeatingDelay"
strSQL = strSQL + ", tblCoils.OperHeatDelay, tblCoils.VMMString, tblCoils.TargetDoTemp, tblCoils.ActualDoTemp, tblCoils.PeakFront"
strSQL = strSQL + ", tblCoils.PeakBack, tblCoils.[4thPassTemp], tblCoils.HeatStartTime, tblCoils.FinishTime, tblCoils.TotalHoldTime"
strSQL = strSQL + ", tblCoils.LastFinishTime, tblCoils.StandardHeatTime, tblCoils.Thickness, tblCoils.Width, tblCoils.Weight"
strSQL = strSQL + ", tblCoils.MinFront, tblCoils.MinBack, tblCoils.ChargeTemp, tblCoils.Report FROM tblCoils "
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?

[nico5038]

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)

[shades234]

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)

Private Sub Command2_Click()
 
Dim strSQL As String
Dim dbsC1894 As Database
Dim rstShift As Recordset
 
Set dbsC1894 = CurrentDb
 
strSQL = "DELETE FROM tblShift;"
 
dbsC1894.Execute (strSQL)
 
strSQL = "INSERT INTO tblShift ( [Time], [Date], CoilID, CoilDataTable, Grade, Furnace, TapA, TapB, TapC, TapD, Program, VaLimit, IaLimit"
strSQL = strSQL + ", KwLimit, ShimsFld, BowFld, HeatingDelay, OperHeatDelay, VMMString, TargetDoTemp, ActualDoTemp, PeakFront, PeakBack"
strSQL = strSQL + ", 4thPassTemp, HeatStartTime, FinishTime, TotalHoldTime, LastFinishTime, StandardHeatTime, Thickness, Width, Weight"
strSQL = strSQL + ", MinFront, MinBack, ChargeTemp, Report ) "
strSQL = strSQL + "SELECT tblCoils.Time, tblCoils.Date, tblCoils.CoilID, tblCoils.CoilDataTable, tblCoils.Grade, tblCoils.Furnace"
strSQL = strSQL + ", tblCoils.TapA, tblCoils.TapB, tblCoils.TapC, tblCoils.TapD, tblCoils.Program, tblCoils.VaLimit, tblCoils.IaLimit"
strSQL = strSQL + ", tblCoils.KwLimit, tblCoils.ShimsFld, tblCoils.BowFld, tblCoils.HeatingDelay, tblCoils.OperHeatDelay, tblCoils.VMMString"
strSQL = strSQL + ", tblCoils.TargetDoTemp, tblCoils.ActualDoTemp, tblCoils.PeakFront, tblCoils.PeakBack, tblCoils.[4thPassTemp]"
strSQL = strSQL + ", tblCoils.HeatStartTime, tblCoils.FinishTime, tblCoils.TotalHoldTime, tblCoils.LastFinishTime, tblCoils.StandardHeatTime"
strSQL = strSQL + ", tblCoils.Thickness, tblCoils.Width, tblCoils.Weight, tblCoils.MinFront, tblCoils.MinBack, tblCoils.ChargeTemp"
strSQL = strSQL + ", tblCoils.Report FROM tblCoils;"
 
dbsC1894.Execute (strSQL)
 
Set rstShift = dbsC1894.OpenRecordset("tblShift")
 
rstShift.MoveFirst
 
Do Until rstShift.EOF
 
If rstShift!Date < [StartDate] Then
    rstShift.Delete
 
Else
    If rstShift!Date > [EndDate] Then
        rstShft.Delete
    End If
End If
 
rstShift.MoveNext
Loop
 
rstShift.Close
 
Set rstShift = dbsC1894.OpenRecordset("tblShift")
 
rstShift.MoveFirst
 
Do Until rstShift.EOF
 
If rstShift!Date = [StartDate] Then
    If rstShift!HeatStartTime < [StartTime] Then
        rstShift.Delete
    End If
End If
 
rstShift.MoveNext
Loop
rstShift.Close
 
 
Set rstShift = dbsC1894.OpenRecordset("tblShift")
 
rstShift.MoveFirst
 
Do Until rstShift.EOF
 
If rstShift!Date = [EndDate] Then
    If rstShift!FinishTime > [EndTime] Then
        rstShift.Delete
    End If
End If
 
rstShift.MoveNext
Loop
rstShift.Close
 
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)

[nico5038]

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

Did you try the other where clause ?

Nic;o)

[shades234]

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)

[shades234]

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.

[shades234]

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

[nico5038]

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)

[NeoPa]

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?

[shades234]

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?

[NeoPa]

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.

[MMcCarthy]

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.

[shades234]

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

[NeoPa]

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.

[nico5038]

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)