Needing help with time query

[guest]

I am working with a linked table from another database; I cannot change the nature of this table. But I do have to query it for set shifts using a user input date. I made a make table query that isolates the data I need but I cannot get it to display the correct times mainly because of the way that the linked table keeps its time/date, here is its format;

MM/DD/YYYY TT:TT:TT AM/PM

I can get it to display correctly for the current date using;

Like Date() & "*" And >#6:00:00 AM# And <#2:30:00 PM#

but it isn't the current date I need, I need it to display the correct times for first shift (6AM-2:30PM) on the records collected yesterday.

Here is the SQL for the make table query;

SELECT [dbo_PDTRP FKT].[PDOP], [dbo_PDTRP FKT].[PDSTAT], [dbo_PDTRP FKT].[PDDAT], [dbo_PDTRP FKT].[PD TESTS], [dbo_PDTRP FKT].TYPE, [dbo_PDTRP FKT].PDVEN, [dbo_PDTRP FKT].[PDFP] INTO Datequeryauto
FROM [dbo_PDTRP FKT]
GROUP BY [dbo_PDTRP FKT].[PDOP], [dbo_PDTRP FKT].[PDSTAT], [dbo_PDTRP FKT].[PDDAT], [dbo_PDTRP FKT].[PD TESTS], [dbo_PDTRP FKT].TYPE, [dbo_PDTRP FKT].PDVEN, [dbo_PDTRP FKT].[PDFP]
HAVING ((([dbo_PDTRP FKT].[PDDAT]) Like [enter date] & "*"));

[guest]

I guess my question is unclear?

[NeoPa]

I don't know yet Guest, I've only just got around to looking at it.
Life... you understand.

[NeoPa]

Firstly, your query is (roughly) equivalent to :

SELECT [PDOP], [PDSTAT], [PDDAT], [PD TESTS], [TYPE], [PDVEN], [PDFP]
INTO DateQueryAuto
FROM [dbo_PDTRP FKT]
WHERE ([PDDAT] Like [Enter Date] & "*");

And I'm guessing this is what you really want.
However, if you want to select records dependent simply on the date part (no times required - and assuming the field is a Date/Time field), then you need something like :

SELECT [PDOP], [PDSTAT], [PDDAT], [PD TESTS], [TYPE], [PDVEN], [PDFP]
INTO DateQueryAuto
FROM [dbo_PDTRP FKT]
WHERE (Int([PDDAT])=[Enter Date]);

Dates can get complicated, especially when you have to deal with literals, but in this case we're lucky as, except for the Int() function call (which strips the time part of a Date/Time field) everything we do is as dates.
FYI In a Date/time field, the date is stored as a whole number of days since 30/12/1899 and the time part is stored as a fraction of a day. Using Int() to lose the fractional part converts any Date/time value to the simple date.

[guest]

I am sorry that I made myself unclear, I do not need to query by date, rather I need the query to isolate data that meets criterion specific to time of day. this is because I need to return records that are shift specific.

The query draws from a table containing all the records from a date. So all the records are from say 1/1/2007. But now that the records from that date are isolated I need to split them again into shifts (and as I said before, the field that I am working with keeps both the time and date within the same record ie. "1/1/2007 3:00:00 PM”). So if I were to use the int() function the return a simple date, I will then be unable to split the day into shift times (sadly there is no 'shift' field -=weep=- )

So, now to try to put it a bit more simple. I need to split a date (not the current date) into specific time-oriented shifts.

[NeoPa]

Separate out the time by saying [DateField] - Int([DateField]) and compare it to a time literal #09:00:00#.

[MMcCarthy]

Does this help?

SELECT [dbo_PDTRP FKT].[PDOP], [dbo_PDTRP FKT].[PDSTAT], 
Format([dbo_PDTRP FKT].[PDDAT].''\#mm/dd/yyyy hh:nn:ss\#'),
[dbo_PDTRP FKT].[PD TESTS], [dbo_PDTRP FKT].TYPE, [dbo_PDTRP FKT].PDVEN, [dbo_PDTRP FKT].[PDFP] INTO Datequeryauto
FROM [dbo_PDTRP FKT]
GROUP BY [dbo_PDTRP FKT].[PDOP], [dbo_PDTRP FKT].[PDSTAT], [dbo_PDTRP FKT].[PDDAT], [dbo_PDTRP FKT].[PD TESTS], [dbo_PDTRP FKT].TYPE, [dbo_PDTRP FKT].PDVEN, [dbo_PDTRP FKT].[PDFP]
HAVING ((([dbo_PDTRP FKT].[PDDAT]) Like [enter date] & "*"));

Also make sure your date format in the new table is set to 'General Date'

[chaosedict]

Heyhey, i posted this as guest before i had an account here ^^. Now i am a member and VERY impressed, thank you NeoPa, that worked GREAT and i have been able to move onto the next step.

[NeoPa]

Well that's cool.
I didn't even know you could post a question as a guest :confused:
Glad to hear that solved your problem. Lots of us have had to find our way around these sorts of problems in the past, finding solutions where the basic SQL syntax doesn't seem to provide support. We're happy to share our experience and are pleased when it helps.