Solved

ms access query

Posted on 2016-09-20
17
60 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
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 49

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 49

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 49

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 49

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

This article will show you how to use shortcut menus in the Access run-time environment.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

791 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