Help required query for part of week remaining

[SmudgerGTS]

Hi, I have a query in place that pulls delivery dates from a table. The current query pulls the whole week by using

Field : DatePart("ww",[DelDate])
Criteria : DatePart("ww",Date())
and
Field : Year([DelDate])
Crireria : Year(date())

I would like to be able to pull only the deliveries for the remaining days of the week (including today), so today is Thursday, I'd like to pull Thurs, Fri, Sat & Sun. New week starts on Monday if possible.

Thanks

 

[bob fitz]

Perhaps you just need >date() in the criteria of [DelDate]

 

[theDBguy]

Hi. You could try something like:

Between Date() And DateAdd("d",8-Weekday(Date),Date)

Hope it helps...

 

[SmudgerGTS]

Thanks all, used Bobs with >= so as it included today

 

[SmudgerGTS]

DBG, think in hindsight your code would be a better fit. However I cant get it to work, perhaps I have it in the wrong place ?

I have put it in the Criteria of the query for the field DelDate but keep getting the errror "Data type mismatch in criteria expression" should I be replacing part of the code with my field names ? As you can tell I have no idea, thanks for your guidance.

 

[theDBguy]

DBG, think in hindsight your code would be a better fit. However I cant get it to work, perhaps I have it in the wrong place ?

I have put it in the Criteria of the query for the field DelDate but keep getting the errror "Data type mismatch in criteria expression" should I be replacing part of the code with my field names ? As you can tell I have no idea, thanks for your guidance.

Hi. First, let's make sure you don't have any Null values in the DelDate field. So, check all your records and let us know if there are any Null values in that column.

 

[SmudgerGTS]

No Nulls, all date filed populated with correct dates

 

[theDBguy]

No Nulls, all date filed populated with correct dates

Next question then is: Is it a Date/Time data type? The Date() and DateAdd() function returns a date value, so if the field is not a Date/Time field, it might get a "type mismatch" error. Can you post the full SQL statement of the query? Thanks.

 

[SmudgerGTS]

Does this look correct ?

SELECT TabDELDetail.DelDate, TabDELDetail.DelCustomer, TabDELDetail.DelSO, TabDELDetail.DelWhere, TabDELDetail.DelTime, TabDELDetail.DelTF, TabDELDetail.DelNotes, TabDELDetail.DelID
FROM TabDELDetail
WHERE (((TabDELDetail.DelDate) Between Date() And DateAdd("d",8-Weekday("Date"),"Date")) AND ((DatePart("ww",[DelDate]))=DatePart("ww",Date())) AND ((Year([DelDate]))=Year(Date())));

 

[theDBguy]

Does this look correct ?

SELECT TabDELDetail.DelDate, TabDELDetail.DelCustomer, TabDELDetail.DelSO, TabDELDetail.DelWhere, TabDELDetail.DelTime, TabDELDetail.DelTF, TabDELDetail.DelNotes, TabDELDetail.DelID
FROM TabDELDetail
WHERE (((TabDELDetail.DelDate) Between Date() And DateAdd("d",8-Weekday("Date"),"Date")) AND ((DatePart("ww",[DelDate]))=DatePart("ww",Date())) AND ((Year([DelDate]))=Year(Date())));

Well, not sure what you're trying to do there, but going back, for a second, to my suggested solution, try removing the rest of the WHERE condition for the moment just to see if you still get an error. So, in other words, try this:

SELECT TabDELDetail.DelDate, TabDELDetail.DelCustomer, TabDELDetail.DelSO, TabDELDetail.DelWhere, TabDELDetail.DelTime, TabDELDetail.DelTF, TabDELDetail.DelNotes, TabDELDetail.DelID
FROM TabDELDetail
WHERE TabDELDetail.DelDate Between Date() And DateAdd("d",8-Weekday(Date()),Date())

PS. I think the "type mismatch" error you were getting was because "Date" was in quotes in your SQL statement.

 

[SmudgerGTS]

Thats it, thanks very much.

 

[theDBguy]

Thats it, thanks very much.

You're welcome. Glad to hear you got it sorted out. Good luck with your project.

 

[SmudgerGTS]

Thanks, do you remember a few weeks ago you helped me with a cross tab query to get delivery numbers in columns with the date as the column headers ? Just wonderd if you had had any thought as to how I could get extra data into that report. I would like to get delivery time and customer name bought across. I tried creating another query but could not find an ID field to make the link with. Do you still have the copy of my database I sent ?

 

[theDBguy]

Thanks, do you remember a few weeks ago you helped me with a cross tab query to get delivery numbers in columns with the date as the column headers ? Just wonderd if you had had any thought as to how I could get extra data into that report. I would like to get delivery time and customer name bought across. I tried creating another query but could not find an ID field to make the link with. Do you still have the copy of my database I sent ?

Hi. I think so. I thought you already got it sorted out. Can you give me a link to take another look?

 

[SmudgerGTS]

Yes, we sorted out the tabulation but I could not work out how to get the time data shown. In my first post on that thread I show a image of the tabulated data with colours on the delivery numbers - those colours indicate a particular time the delivery is required. At present on my reports I have the colours shown by the use of conditional formatting.

Here is the link https://www.access-programmers.co.uk/forums/threads/trying-to-transpose-data-for-form.310190/

Thanks again for your help, no rush on this one, as and when you have a bit of spare time.

 

[theDBguy]

Yes, we sorted out the tabulation but I could not work out how to get the time data shown. In my first post on that thread I show a image of the tabulated data with colours on the delivery numbers - those colours indicate a particular time the delivery is required. At present on my reports I have the colours shown by the use of conditional formatting.

Here is the link https://www.access-programmers.co.uk/forums/threads/trying-to-transpose-data-for-form.310190/

Thanks again for your help, no rush on this one, as and when you have a bit of spare time.

Okay, thanks. I'll let you know.