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
5 1661
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.
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
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;
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]));
ok that helps a lot more thanx a ton
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Robert Scarborough |
last post by:
I have a Table in a Typed Dataset which contains a Date field called
EventDate.
I've ensured that the field is defined as Date as opposed to DateTime in the
Typed Dataset.
When I generate an...
|
by: Neil |
last post by:
I have been coding with ASP for some time now. I am using an ACCESS
database. I am in the UK and wish to use DD/MM/YYYY format for dates.
I have had no end of problems and possible solutions to...
|
by: StBond |
last post by:
Hi everyone,
I am new to Access and Visual Basic so things my be getting across a
bit cloudy. I only started using VB for one week.
I am having a little problem with the database that I am...
|
by: aqmaiya |
last post by:
Hello,
there is string value 'Dec 06, 2000' I want to convert that string
date to SHORT FORMAT like '2000-12-06-. Please help me how do I do
that? I'm new in Jython.
Thanks,
aqmaiya
|
by: Henning M |
last post by:
Hi all,
I having some problems with Access and selecting records between dates..
When I try this in access, it works fine!!
"Select * from Bilag Where Mdates Between #1/1/2006# And...
|
by: Easystart |
last post by:
Hi,
Sorry for my English. English is not my native tougue.
I am working in MS Access 2000 with a SQLServer 2000 Backend database.
MS Access 2000 is my GUI front end that has SQLServer linked...
|
by: hardik |
last post by:
hi friends,
i am really surprized the way access behaves in date fields i mean
it's all ok when you have us time zone or us servers but if you have
diffrent timezone like uk then access creates...
|
by: smaczylo |
last post by:
Hello, I've recently been asked to work with Microsoft Access, and
while I feel quite comfortable with Excel, I'm at a complete loss with
databases. If someone could help me with this issue I'm...
|
by: phpfreak2007 |
last post by:
Hi everyone...
I want to copy text from clipboard and paste into my project.I am able to do it but problem is Its not in same formate..That means I want bold letter as bold and with same...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |