Between midday a and midday b

[krowe]

hi

hope you can help me,

I have a table with date/time field in. I have a date entry form with a 'from date' and a 'to date'.

I have a query that uses the form a criteria to selct the date range, however I need the query to return only those entries received between midday the first date and midday the second date.

This is my where clause at the moment:

WHERE (((tblJobs.Date_Time) Between [Forms]![frmDateSelect]![Text2] And [Forms]![frmDateSelect]![Text11]))

how would l amend this to stipulate only after 12:00 on [Text2] and before 12:00 on [Text11].

Thanks

Kev

 

[RainLover]

Kev

After the 63 posts you have accumulated I would have thought that your skills would be such that you use a better naming convention.

Text1 and Text2 is for total novices which you are not.

 

[krowe]

Thanks for your helpful post

I am very much an novice (hence being on here all the time asking basic questions like this!) having had no training in Access and being completely self taught.

I take your point about the naming of the boxes, and will change them to something more useful in future, however I hope someone can still help with my original question. ..

 

[pr2-eugin]

Have you tried using Format??

WHERE ((tblJobs.Date_Time) BETWEEN (Format([Forms]![fmrDateSelect]![Text2],"dd/mm/yyyy 12:00:00")) AND (Format([Forms]![frmDateSelect]![Text11],"dd/mm/yyyy 11:59:59")));

 

[MSAccessRookie]

hi

hope you can help me,

I have a table with date/time field in. I have a date entry form with a 'from date' and a 'to date'.

I have a query that uses the form a criteria to selct the date range, however I need the query to return only those entries received between midday the first date and midday the second date.

This is my where clause at the moment:

WHERE (((tblJobs.Date_Time) Between [Forms]![frmDateSelect]![Text2] And [Forms]![frmDateSelect]![Text11]))

how would l amend this to stipulate only after 12:00 on [Text2] and before 12:00 on [Text11].

Thanks

Kev

In keeping with Ranlover's observation that you have more than basic skills, I would like to give you some suggestions and see what you can do with them. Resolution of your issue will require simple String Concatenation along with the use of the functions cStr, cDate() and Format()

• Format the Dates as Short Dates (Date Only)
• Convert to Text and add the time part
• Convert back to Date if required

Give it a try and get back to us with any questions.

-- Rookie

 

[spikepl]

A shortcut would logically be:

WHERE (((tblJobs.Date_Time) Between ([Forms]![frmDateSelect]![Text2] + 0.5) And ([Forms]![frmDateSelect]![Text11]+0.5)))

 

[spikepl]

A general comment: no need to convert datetimes to text and it is inadvisable in general , when you don't have to, and you don't here. It can can get you into problems if you are not in US locale.

Ther is also a function DateAdd, so an alternative way is:

WHERE (((tblJobs.Date_Time) Between DateAdd("h", 12, [Forms]![frmDateSelect]![Text2]  ) And  DateAdd("h", 12, [Forms]![frmDateSelect]![Text11])))

This function does exactly the same as in my previous post: increments the Double value that represents DateTime by 0.5 - half a day or 12 hours.

 

[krowe]

Thanks, that make a lot of sense now, wish i was experienced enough at all this to be able to work out a solution like that

 

[RainLover]

Thanks for your helpful post

I am very much an novice (hence being on here all the time asking basic questions like this!) having had no training in Access and being completely self taught.

I take your point about the naming of the boxes, and will change them to something more useful in future, however I hope someone can still help with my original question. ..

I think you may be better than you think. It is just that some things haven't quite clicked. They will soon.

If you have a look at the Sky Drive link in my signature, I wrote an article on naming conventions. It might help.

Good luck with the learning curve.