473,320 Members | 2,003 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Formate access 07 dates

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
Jul 9 '08 #1
5 1661
puppydogbuddy
1,923 Expert 1GB
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.
Expand|Select|Wrap|Line Numbers
  1. Select datefield, DateValue(datefield) As yourDate, TimeValue(datefield) As yourTime from yourTable.
Jul 10 '08 #2
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.
Expand|Select|Wrap|Line Numbers
  1. 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
Jul 10 '08 #3
puppydogbuddy
1,923 Expert 1GB
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.
Expand|Select|Wrap|Line Numbers
  1. 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;
Jul 11 '08 #4
puppydogbuddy
1,923 Expert 1GB
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

Expand|Select|Wrap|Line Numbers
  1. Update YourTable Set YourTable.TimeField = ([DateField] -
  2. Int([DateField]));
Jul 11 '08 #5
ok that helps a lot more thanx a ton
Jul 11 '08 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

4
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...
9
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...
3
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...
2
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
5
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...
5
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...
2
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...
1
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...
2
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
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...
1
isladogs
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...
0
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...
0
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...
0
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....
0
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
0
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.