Formate access 07 dates

[pyroguanyu]

this is the first time i have used access.
and i have been assighnd the job of getting queries of hh/mm/ss,
there is 20487 entrees that i need to dived up into four time frames
but the problem is access has it in a mm/dd/yyyy hh/mm/ss and
i can not just say >hh/mm/ss and <hh/mm/ss
because it comes up blank as if there are no dates i have attempted to format the table field but it will not let me remove the mm/dd/yyyy from the hh/mm/ss

is there any way to have it so i don't have to make 180 criteria entrees for each of the four time slots?

if it helps the time frames are 9:00AM - 12:00PM, 12:00PM - 3:00PM, 3:00PM - 5:00PM, and 5:00PM - 8:00PM

[puppydogbuddy]

this is the first time i have used access.
and i have been assighnd the job of getting queries of hh/mm/ss,
there is 20487 entrees that i need to dived up into four time frames
but the problem is access has it in a mm/dd/yyyy hh/mm/ss and
i can not just say >hh/mm/ss and <hh/mm/ss
because it comes up blank as if there are no dates i have attempted to format the table field but it will not let me remove the mm/dd/yyyy from the hh/mm/ss

is there any way to have it so i don't have to make 180 criteria entrees for each of the four time slots?

if it helps the time frames are 9:00AM - 12:00PM, 12:00PM - 3:00PM, 3:00PM - 5:00PM, and 5:00PM - 8:00PM

As you noted Access datefield in the table has a date and time components. The way you can extract the date and time components separately is to use Access's built-in functions as follows. For illustrative purposes, I have only selected the datefield in the query below.

Select datefield, DateValue(datefield) As yourDate, TimeValue(datefield) As yourTime from yourTable.

[pyroguanyu]

As you noted Access datefield in the table has a date and time components. The way you can extract the date and time components separately is to use Access's built-in functions as follows. For illustrative purposes, I have only selected the datefield in the query below.

Select datefield, DateValue(datefield) As yourDate, TimeValue(datefield) As yourTime from yourTable.

this seems to be what i need but i can not figure out how to do this....is there any way to be more detailed with it?

i am still trying to get this to work i am just not sour on what part of the table im sopose to be changing

and thanx very much

[puppydogbuddy]

Since you are so new to Access, I suggest you read the information provided in the following link, particularly the info on Advanced select queries using Access functions. If you are not aware, you should be aware that you can look at an Access query in design view (the grid), or you can look (and also, copy/paste) in Sql view and then switch back to design view.

http://www.fmsinc.com/TPapers/queries/index.html

Your original request did not indicate anything about updating your table. Consequently the query I illustrated for you is a select query that just extracts the time component from the datefield in your table using an alias name. You can extend the example I gave you by creating aliases for each time interval you listed, as shown below. Remember to replace fieldnames that I used for illustrative purposes with the actual table/field names. After you have changed the names, try pasting the sql below in the Sql view of the query in the query designer.

SELECT yourTable.datefield, Format(DateValue([datefield]),"mm/dd/yyyy") AS yourDate, TimeValue([datefield]) AS yourTime, Format(TimeValue([datefield])>=#09:00:00# And TimeValue([datefield])<=#11:59:59#,"HH:MM:SS") AS [9 To 12AM], Format(TimeValue([datefield])>=#12:00:00# And TimeValue([datefield])<=#14:59:59#,"HH:M:SS") AS [12 To 3PM], Format(TimeValue([datefield]) >= #15:00:00# And TimeValue([datefield]) <= #17:59:59#,"HH:MM:SS") AS [3 To 6PM], Format(TimeValue([datefield]) >= #18:00:00# And TimeValue([datefield]) <= #20:59:59#,"HH:MM:SS") AS [6 To 9PM] FROM yourTable;

[puppydogbuddy]

See this link for an easier method than the one I suggested, which also contains the syntax for updating a time field that you have added to your table.

http://bytes.com/forum/thread433456.html

Update YourTable Set YourTable.TimeField = ([DateField] -
Int([DateField]));

[pyroguanyu]

ok that helps a lot more thanx a ton