Solved

ms access query

Posted on 2016-09-20
17
50 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 25

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 25

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
 
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 25

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
Backup Your Microsoft Windows Server®

Backup 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 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
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 …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now