change date format

[Misiek]

Hello All,

I had a request from my boss to add time to out date field - "tTaskDueDate"
I have changed default value from Date() to Now() in the form.

I had this code working fine until now:

Select Case Me.cboAPduedate
        Case 1
        strWhere = strWhere & "tTaskDueDate = date()-1 And " 'Yesterday
        Case 2
        strWhere = strWhere & "tTaskDueDate <= date() AND " 'Today and older
        Case 3
        strWhere = strWhere & "tTaskDueDate = date()+1 AND " 'Tomorrow
        Case 4
        strWhere = strWhere & "tTaskDueDate = date() + 7 - Weekday(Date())+2 AND " 'next Monday
        Case 5
        strWhere = strWhere & "tTaskDueDate = date() + 7 - Weekday(Date())+3 AND " 'next Tuesday
        Case 6
        strWhere = strWhere & "tTaskDueDate = date() + 7 - Weekday(Date())+4 AND " 'next Wednesday
        Case 7
        strWhere = strWhere & "tTaskDueDate = date() + 7 - Weekday(Date())+5 AND " 'next Thursday
        Case 8
        strWhere = strWhere & "tTaskDueDate = date() + 7 - Weekday(Date())+6 AND " 'next Friday
        Case 9
        strWhere = strWhere & "tTaskDueDate = date() + 7 - Weekday(Date()) AND " 'next Saturday
        Case 10
        strWhere = strWhere & "tTaskDueDate = date() + 7 - Weekday(Date())+1 AND " 'next Sunday
        Case 11
        strWhere = strWhere & "tTaskDueDate >= date() + 7 - Weekday(Date())+2 and tTaskDueDate <= date() + 7 - Weekday(Date())+7 AND " 'Next week
    End Select

Then the query stopped working properly, doesn't bring up records with date or with date and time, doesn't give any errors either.

I've done some searching and found about FORMAT.

Format([tTaskDueDate],'yyyy-mm-dd')

I managed to make the YESTERDAY, TODAY and TOMORROW work, but can't work out anything after that, gives my runtime error 3464 data type mismatch, I obviously didn't format something correctly. I believe the WEEKDAY part.
my new code looks like this:

Select Case Me.cboAPduedate
        Case 1
        strWhere = strWhere & "Format([tTaskDueDate],'yyyy-mm-dd') = Format(Date()-1, 'yyyy-mm-dd') AND " 'Yesterday
        Case 2
        strWhere = strWhere & "Format([tTaskDueDate],'yyyy-mm-dd') <= Format(Date(), 'yyyy-mm-dd') AND " 'Today and older
        Case 3
        strWhere = strWhere & "Format([tTaskDueDate],'yyyy-mm-dd') = Format(Date()+1, 'yyyy-mm-dd') AND " 'Tomorrow
        Case 4
        strWhere = strWhere & "Format([tTaskDueDate],'yyyy-mm-dd') = Format(Date()+7, 'yyyy-mm-dd') - Weekday(date()+2) AND " 'next Monday
        Case 5
        strWhere = strWhere & "Format([tTaskDueDate],'yyyy-mm-dd') = date() + 7 - Weekday(Date())+3 AND " 'next Tuesday
        Case 6
        strWhere = strWhere & "Format([tTaskDueDate],'yyyy-mm-dd') = date() + 7 - Weekday(Date())+4 AND " 'next Wednesday
        Case 7
        strWhere = strWhere & "Format([tTaskDueDate],'yyyy-mm-dd') = date() + 7 - Weekday(Date())+5 AND " 'next Thursday
        Case 8
        strWhere = strWhere & "Format([tTaskDueDate],'yyyy-mm-dd') = date() + 7 - Weekday(Date())+6 AND " 'next Friday
        Case 9
        strWhere = strWhere & "Format([tTaskDueDate],'yyyy-mm-dd') = date() + 7 - Weekday(Date()) AND " 'next Saturday
        Case 10
        strWhere = strWhere & "Format([tTaskDueDate],'yyyy-mm-dd') = date() + 7 - Weekday(Date())+1 AND " 'next Sunday
        Case 11
        strWhere = strWhere & "Format([tTaskDueDate],'yyyy-mm-dd') >= date() + 7 - Weekday(Date())+2 and tTaskDueDate <= date() + 7 - Weekday(Date())+7 AND " 'Next week
    End Select

I would greatly appreciate any help.
Many thanks

 

[CJ_London]

the format function returns a string, which you are then trying to compare to a date.

suggest use the datevalue function instead.

Also your criteria seems more complext than it needs to be. Why not something like

WHERE datevalue(tTaskDueDate) BETWEEN Date-1 and Date+14
rather than selecting each day?

 

[Misiek]

Need to select each day separate as this is a TASK PLANNER application, we enter tasks ahead of time and then on the day we execute whatever is due. Tomorrow, Next Monday, etc are there to preview whats coming and we can reschedule.

I will look into DateValue, Thanks



EDIT:

I tried with the datevalue, but it didn't work at all with "date()"
I have worked out where I made mistake with the FORMAT and its all OK now and working with both date+time and dates only.

Thanks anyway

 

[Gasman]

EDIT:

I tried with the datevalue, but it didn't work at all with "date()"
I have worked out where I made mistake with the FORMAT and its all OK now and working with both date+time and dates only.

Thanks anyway

Care to share?, as it might help others, and I'm thinking of myself here I must admit

 

[Misiek]

sure,

Of what I was able to find, the FORMAT only works with manual date entry. In my case it gave an error when tried with datevalue(myfield) or datevalue(date()).

Therefore I have returned to FORMAT.
The solution proposed by CJLondon, wouldn't work in my case, as users need to see records only for specific day.
That's why in my form I have a combo box, then in the source I've created a list of options which are then linked to search criteria through CASE statement as per code below:

Select Case Me.cboMyComboFieldFromTheForm
        Case 1
        strWhere = strWhere & "Format(myDateField,'yyyy-mm-dd') = Format(Date()-1, 'yyyy-mm-dd') AND " 'Yesterday
        Case 2
        strWhere = strWhere & "Format(myDateField,'yyyy-mm-dd') <= Format(Date(), 'yyyy-mm-dd') AND " 'Today and older unclompleted
        Case 3
        strWhere = strWhere & "Format(myDateField,'yyyy-mm-dd') = Format(Date()+1, 'yyyy-mm-dd') AND " 'Tomorrow
        Case 4
        strWhere = strWhere & "Format(myDateField,'yyyy-mm-dd') = Format(Date()+7 - Weekday(date()+2), 'yyyy-mm-dd')  AND " 'next Monday
        Case 5
        strWhere = strWhere & "Format(myDateField,'yyyy-mm-dd') = Format(Date() + 7 - Weekday(Date())+3, 'yyyy-mm-dd') AND " 'next Tuesday
        
..... etc

Good Luck