Solved

ms access query

Posted on 2016-09-20
17
67 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 28

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 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 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
Industry Leaders: 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 50

Accepted Solution

by:
Gustav Brock earned 250 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 28

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 50

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 47

Expert Comment

by:Dale Fye (Access MVP)
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 50

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 50

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

749 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