Union query

[Guest]

I have a table that contains records with both a time field and a date field.

i have a form that displays the records, however i only dwant a certain
number of records displayed, namely those within a certain date/time block.
I am planning on another form that will set up a query to "fill" the first
form.

so, here's the setup. btlCoils is the main table with all the records.
frmSFR is the form that displays the records from the selected block. frmSFR
is based off of qrySFRRorm. frmChangeDateTime is the form that will generate
the SQL tatement for qrySFRForm. good enough?

now, on the change date and time form, there are 4 fields, start/end time
and start/end date. (event blocks may carry over from night one day to
morning the next day) i was thinking i need a union query to accomplish this.

would the sql statement look something like

SELECT ( list of tbl.field) FROM tblCoils WHERE ((tblCoils.Date)>= "start
date" AND (tblCoils.Time)>="start time")
UNION
SELECT ( list of tbl.field) FROM tblCoils WHERE ((tblCoils.Date)<= "end
date" AND (tblCoils.Time)<="finish time")

???

this statement would be generated in VBA, then placed into the qrySFRForm,
the query would then run, then the form frmSFRRorm would run.

am i missing anything?

thanks

 

[Guest]

i made a mistake. It should be an intersecion. I want all records between
that come after (or at the time of ) Start Date and Start time. and all
records that have fields that come before End Date/End time.

is this a having clause? or is there an intersect function in SQL?

 

[John Vinson]

SELECT ( list of tbl.field) FROM tblCoils WHERE ((tblCoils.Date)>= "start
date" AND (tblCoils.Time)>="start time")
UNION
SELECT ( list of tbl.field) FROM tblCoils WHERE ((tblCoils.Date)<= "end
date" AND (tblCoils.Time)<="finish time")

???

this statement would be generated in VBA, then placed into the qrySFRForm,
the query would then run, then the form frmSFRRorm would run.

am i missing anything?

You're comparing the date/time values in the (misnamed - both Date and
Time are reserved words and should NOT be used as fieldnames) to text
strings. No date/time value will ever be greater than or equal to the
text string "start date".

In addition, if you correct this by using >= [Start date] - using
square brackets instead of quote marks to prompt for a start date -
you'll still get the wrong answer. Think about it: if you respond with
11/28/2006 for [Start date] and 11:30pm for [Start time], 1/1/2007 for
[Finish date] and 01:30am for [Finish time], you'll get NOTHING -
because you're asking for records where [Time] is, in the same record,
later than 11:30pm and earlier than 1:30am. Of course no records will
qualify!

You'll be MUCH better off storing the date and time together in one
field, say CoilDateTime; and using both the date and time in your
criteria.

John W. Vinson[MVP]

 

[John Spencer]

Simplest way is to combine the Date and time fields into one for comparison
purposes. How you do so depends on whether or not Date and Time fields are
actual date time fields and whether or not they are always populated.

WHERE TblCoils.Date + tblCoils.Time > [Start DateTime]

WHERE CDate(NZ(TblCoils.Date,0)) + CDate(Nz(TblCoils.Time,0)) > [Start
DateTime]

Another way to do this would be like the following. The second method could
be a lot faster since it will be able to use any indexes that exist on
tblCoils.Date and tblCoils.Time. Unless you have a large dataset, the
performance difference will probably not be noticed.

WHERE
(TblCoils.Date > [Start Date] + 1
Or (TblCoils.Date = [Start Date] AND tblCoils.Time > [Start
Time]))

AND (TblCoils.Date < [End Date]
Or (TblCoils.Date = [End Date] + 1 AND tblCoils.Time > [End
Time]))