Help required query for part of week remaining (1 Viewer)

SmudgerGTS

New member
Local time
Today, 12:56
Joined
Mar 16, 2020
Messages
23
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
19,134
Hi. You could try something like:
Code:
Between Date() And DateAdd("d",8-Weekday(Date),Date)

Hope it helps...
 

SmudgerGTS

New member
Local time
Today, 12:56
Joined
Mar 16, 2020
Messages
23
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

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
19,134
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
19,134
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

New member
Local time
Today, 12:56
Joined
Mar 16, 2020
Messages
23
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

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
19,134
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:

SQL:
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

New member
Local time
Today, 12:56
Joined
Mar 16, 2020
Messages
23
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

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
19,134
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

New member
Local time
Today, 12:56
Joined
Mar 16, 2020
Messages
23
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

I’m here to help
Staff member
Local time
Today, 04:56
Joined
Oct 29, 2018
Messages
19,134
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.
 

Users who are viewing this thread

Top Bottom