ms access query

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.
DariusAsked:
Who is Participating?
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
Pawan KumarDatabase ExpertCommented:
Are you getting any error with this?
0
 
DariusAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Pawan KumarConnect With a Mentor Database ExpertCommented:
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
 
DariusAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
DariusAuthor Commented:
Guys, Thank you!
0
 
Gustav BrockCIOCommented:
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
 
Dale FyeCommented:
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
 
DariusAuthor Commented:
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
 
DariusAuthor Commented:
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
 
DariusAuthor Commented:
I going to open new question?
0
 
Gustav BrockCIOCommented:
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
 
DariusAuthor Commented:
Thanks again, - it works for me!
0
 
DariusAuthor Commented:
Gustav how did you added code view with scrollbar under...
0
 
Gustav BrockCIOCommented:
Mark the code (turns blue), then click CODE from the bar.

/gustav
0
 
DariusAuthor Commented:
simple...  Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.