Criteria for date and time

[Mike Hughes]

Access 2010

The production DB table has field called DT_AND_TIME_SCHEDULED and the data is stored as:
5/9/2007 1:30:00 PM
I can’t change the format of the table field because it is our production DB.

So if I’m looking to find any records that are scheduled anytime on 11/15/2011 what would I put in the criteria for the DT_AND_TIME_SCHEDULED field in my query?
Thanks Mike

 

[Mr. B]

Create a new column in your query and use the following to create a "Date Only" field:

DateOnly: Month([TableName].[DT_AND_TIME_SCHEDULED]) & "/" & Day([TableName].[DT_AND_TIME_SCHEDULED]) & "/" & Year([TableName].[DT_AND_TIME_SCHEDULED])

Then you can use a normal date as your criteria.

Replace the "TableName" in the statement above with the actual name of your table.

 

[vbaInet]

I would just use DateValue():

DateValue([DT_AND_TIME_SCHEDULED])

That will return only the date part of the date/time field.

 

[Mike Hughes]

I get this error message when I try to save the query before running it.

"Syntax error (missing operator) in query expression. DateOnly: Month([NOLDBA_COURT_CV_107].[]) & "/" & Day([NOLDBA_COURT_CV_107].[DT_AND_TIME_SCHEDULED]) & "/" & Year([NOLDBA_COURT_CV_107.].[DT_AND_TIME_SCHEDULED]) "

SELECT
NOLDBA_COURT_CV_107.DOCKET_NO,
NOLDBA_COURT_CV_107.EVENT_SEQ,
NOLDBA_COURT_CV_107.DT_RECEIVED,
NOLDBA_COURT_CV_107.SEQ_NUM,
NOLDBA_COURT_CV_107.DT_OF_DOCUMENT,
NOLDBA_COURT_CV_107.CD_NOTIFICATION_TYPE,
NOLDBA_COURT_CV_107.CD_NOTICE_ACTION,
NOLDBA_COURT_CV_107.CD_CASE_TYPE,
NOLDBA_COURT_CV_107.ID_COURT_SCHEDULED,
NOLDBA_COURT_CV_107.ID_LOCATION_SCHEDULED,
NOLDBA_COURT_CV_107.DT_AND_TIME_SCHEDULED,
NOLDBA_COURT_CV_107.IND_REVIEWED_OR_ACK,
NOLDBA_COURT_CV_107.IND_ACK_ALT_WORKER,
NOLDBA_COURT_CV_107.SEQ_EVENT_GLOBAL_BEG,
NOLDBA_COURT_CV_107.SEQ_EVENT_GLOBAL_END,
DateOnly: Month([NOLDBA_COURT_CV_107].[]) & "/" & Day([NOLDBA_COURT_CV_107].[DT_AND_TIME_SCHEDULED]) & "/" & Year([NOLDBA_COURT_CV_107.].[DT_AND_TIME_SCHEDULED]) INTO [1 CV107 TBL]

FROM NOLDBA_COURT_CV_107;

 

[MSAccessRookie]

I get this error message when I try to save the query before running it.

"Syntax error (missing operator) in query expression. DateOnly: Month([NOLDBA_COURT_CV_107].[]) & "/" & Day([NOLDBA_COURT_CV_107].[DT_AND_TIME_SCHEDULED]) & "/" & Year([NOLDBA_COURT_CV_107.].[DT_AND_TIME_SCHEDULED]) "

SELECT
NOLDBA_COURT_CV_107.DOCKET_NO,
NOLDBA_COURT_CV_107.EVENT_SEQ,
NOLDBA_COURT_CV_107.DT_RECEIVED,
NOLDBA_COURT_CV_107.SEQ_NUM,
NOLDBA_COURT_CV_107.DT_OF_DOCUMENT,
NOLDBA_COURT_CV_107.CD_NOTIFICATION_TYPE,
NOLDBA_COURT_CV_107.CD_NOTICE_ACTION,
NOLDBA_COURT_CV_107.CD_CASE_TYPE,
NOLDBA_COURT_CV_107.ID_COURT_SCHEDULED,
NOLDBA_COURT_CV_107.ID_LOCATION_SCHEDULED,
NOLDBA_COURT_CV_107.DT_AND_TIME_SCHEDULED,
NOLDBA_COURT_CV_107.IND_REVIEWED_OR_ACK,
NOLDBA_COURT_CV_107.IND_ACK_ALT_WORKER,
NOLDBA_COURT_CV_107.SEQ_EVENT_GLOBAL_BEG,
NOLDBA_COURT_CV_107.SEQ_EVENT_GLOBAL_END,
DateOnly: Month([NOLDBA_COURT_CV_107].[]) & "/" & Day([NOLDBA_COURT_CV_107].[DT_AND_TIME_SCHEDULED]) & "/" & Year([NOLDBA_COURT_CV_107.].[DT_AND_TIME_SCHEDULED]) INTO [1 CV107 TBL]

FROM NOLDBA_COURT_CV_107;

What are the expected values of the items market in RED above?

 

[vbaInet]

Just re-iterating, I would just use DateValue():

SELECT
NOLDBA_COURT_CV_107.DOCKET_NO,
NOLDBA_COURT_CV_107.EVENT_SEQ,
NOLDBA_COURT_CV_107.DT_RECEIVED,
NOLDBA_COURT_CV_107.SEQ_NUM,
NOLDBA_COURT_CV_107.DT_OF_DOCUMENT,
NOLDBA_COURT_CV_107.CD_NOTIFICATION_TYPE,
NOLDBA_COURT_CV_107.CD_NOTICE_ACTION,
NOLDBA_COURT_CV_107.CD_CASE_TYPE,
NOLDBA_COURT_CV_107.ID_COURT_SCHEDULED,
NOLDBA_COURT_CV_107.ID_LOCATION_SCHEDULED,
NOLDBA_COURT_CV_107.DT_AND_TIME_SCHEDULED,
NOLDBA_COURT_CV_107.IND_REVIEWED_OR_ACK,
NOLDBA_COURT_CV_107.IND_ACK_ALT_WORKER,
NOLDBA_COURT_CV_107.SEQ_EVENT_GLOBAL_BEG,
NOLDBA_COURT_CV_107.SEQ_EVENT_GLOBAL_END,
[COLOR=Red]DateValue([/COLOR][NOLDBA_COURT_CV_107].[DT_AND_TIME_SCHEDULED][COLOR=Red])[/COLOR] [COLOR=Blue]AS DateOnly[/COLOR]
FROM NOLDBA_COURT_CV_107;

Also notice that I took off the INTO part because you should not be saving derived values.

 

[Mike Hughes]

Between the two red brackets should be DT_AND_TIME_SCHEDULED

 

[Brianwarnock]

I would follow vbaInet's advice and use Datevalue, but if you don't then wrap the long winded expression in CDate(..........) to convert the string created into a date.

Brian

 

[vbaInet]

Between the two red brackets should be DT_AND_TIME_SCHEDULED

It seems you are not taking any notice to my posts?