?
Solved

ms access query

Posted on 2016-09-20
17
Medium Priority
?
77 Views
Last Modified: 2016-09-21
Hi guys, anybody can assist on date function

I have this at this moment and results is start and finish date on default time

Between Format(DateValue([Enter the Start Date:])+TimeValue("08:30"),"dd/mm/yyyy hh:nn") And Format(DateValue([Enter the Finish Date:])+TimeValue("20:30"),"dd/mm/yyyy hh:nn")

How to schedule time on every day between selected dates (week)?
Example: during 5 days(Entered start and finish date) between 08:30 - 20:30 everyday.
0
Comment
Question by:Darius
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 3
  • +1
17 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41806346
Are you getting any error with this?
0
 

Author Comment

by:Darius
ID: 41806370
No error on provided query and results is:
2016/09/12 08:30 - 2016/09/15 20:30

Now I need modified query to retrieve results:
schedule default time for every day between selected dates
(
2016/09/12 08:30 - 2016/09/12 20:30
2016/09/13 08:30 - 2016/09/13 20:30
2016/09/14 08:30 - 2016/09/14 20:30
2016/09/15 08:30 - 2016/09/15 20:30
)
0
 
LVL 29

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 total points
ID: 41806378
Pls try this

WHERE (

               (CAST(startDate AS DATE) BETWEEN @StartDate AND @EndDate)
         AND (CAST(ENDDate AS DATE) BETWEEN @StartDate AND @EndDate)
         AND (CAST(startDate AS TIME) BETWEEN '08:30' AND '20:30')
         AND (CAST(ENDDate AS TIME) BETWEEN '08:30' AND '20:30')

         )
1
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 51

Accepted Solution

by:
Gustav Brock earned 1000 total points
ID: 41806392
First, specify in your query [Enter the Start Date:] and [Enter the Finish Date:] As DateTime.

Then use this criteria for your date field:
Where 
    (DateValue([YourDateField]) Between [Enter the Start Date:] And [Enter the Finish Date:])
    And
    (TimeValue([YourDateField]) Between TimeSerial(8, 30, 0) And TimeSerial(20, 30, 0))

Open in new window

/gustav
1
 

Author Comment

by:Darius
ID: 41806441
Guys,

I believe Pawan function working as well but I've used it incorrectly (I need to do more investigation on this)...

Gustav, your function is working as expected!

Thank you all for help!
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41806446
Great Darius, glad that your problem is fixed.  Could you please mark one or more solution as accepted and close the question. Thank you!
1
 

Author Closing Comment

by:Darius
ID: 41806458
Guys, Thank you!
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41806463
Pawan's suggestion is T-SQL for SQL Server only, so if you tried that in a normal Access query, it can't work. That may explain your trouble.
You will need an SQL Server linked table and a pass-through query to use T-SQL.

/gustav
1
 
LVL 48

Expert Comment

by:Dale Fye
ID: 41806487
When you use the format function, you are converting the value to a string, try:

Between cdate(Format(DateValue([Enter the Start Date:])+TimeValue("08:30"),"dd/mm/yyyy hh:nn"))
AND cdate(Format(DateValue([Enter the Finish Date:])+TimeValue("20:30"),"dd/mm/yyyy hh:nn"))

it might work without the cdate and format functions at all:

Between DateValue([Enter the Start Date:])+TimeValue("08:30")
AND DateValue([Enter the Finish Date:])+TimeValue("20:30")
0
 

Author Comment

by:Darius
ID: 41806978
now I tried to improve my query to get follow action results:
(
1. [Enter date] it gives 5 days results with default specific time
2. Gives day 6th with default time (time different)
3. Gives day 7th with default time (time different)
)
0
 

Author Comment

by:Darius
ID: 41806984
SELECT tblByDate_1.Subject, Count(tblByDate_1.Subject) AS CountOfSubject
FROM tblByDate_1
WHERE (((CDate(Format(DateValue([tblByDate_1].[Received]),"dd/mm/yyyy")))
Between [Enter the Start date:] And              => (five day from entered day)
AND ((TimeValue([tblByDate_1].[Received])) Between TimeSerial(8,0,0) And TimeSerial(20,30,0))
AND ((tblByDate_1.From) In ("darius_1.com","darius_2.com","darius_3.com")))
OR
(((CDate(Format(DateValue([tblByDate_1].[Received]),"dd/mm/yyyy")))
=                                                                            => (6th day of entered day)
AND ((TimeValue([tblByDate_1].[Received])) Between TimeSerial(10,0,0) And TimeSerial(18,0,0))
AND ((tblByDate_1.From) In ("darius_1.com","darius_2.com","darius_3.com")))
OR
(((CDate(Format(DateValue([tblByDate_1].[Received]),"dd/mm/yyyy")))
=                                                                             => (7th day of entered day)
AND ((TimeValue([tblByDate_1].[Received])) Between TimeSerial(10,0,0) And TimeSerial(16,0,0))
AND ((tblByDate_1.From) In ("darius_1.com","darius_2.com","darius_3.com")))

GROUP BY tblByDate_1.Subject
ORDER BY Count(tblByDate_1.Subject) DESC;
0
 

Author Comment

by:Darius
ID: 41807008
I going to open new question?
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41807011
It could be something like:
Where 
    ((DateValue([YourDateField]) Between [Enter the Start Date:] And DateAdd("d", 5, [Enter the Start Date:]))
    And
    (TimeValue([YourDateField]) Between TimeSerial(8, 30, 0) And TimeSerial(20, 30, 0)))
    Or
    ((DateValue([YourDateField]) = DateAdd("d", 6, [Enter the Start Date:]))
    And
    (TimeValue([YourDateField]) Between TimeSerial(10, 0, 0) And TimeSerial(18, 0, 0)))
    Or
    ((DateValue([YourDateField]) = DateAdd("d", 7, [Enter the Start Date:]))
    And
    (TimeValue([YourDateField]) Between TimeSerial(10, 0, 0) And TimeSerial(16, 0, 0)))

Open in new window

/gustav
1
 

Author Comment

by:Darius
ID: 41807077
Thanks again, - it works for me!
0
 

Author Comment

by:Darius
ID: 41807086
Gustav how did you added code view with scrollbar under...
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 41807110
Mark the code (turns blue), then click CODE from the bar.

/gustav
0
 

Author Comment

by:Darius
ID: 41808180
simple...  Thank you
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question