Hello
A while ago I made a search in Access form, that works on changing the recordsource. Don't ask me why I did it that way, but now I just want to stick with it. The solution worked great, but now, it doesn't anymore. After some debugging, I figured out the problem.
Let's say we have selected "This week" as for "tasks that are due this week". I calculate the start and end of the week (dates). Then I add WHERE to my query, containing those dates. I checked, the dates are inserted into the query correctly, but it won't show anything. So I went and checked the query in the query designer when the WHERE sentence was added, and I noticed that it turned the day and month around, but only for StartOfWeek
For example, I inserted via code the sentence (WHERE Sth.Date >= #" & StartOfWeek & "# And Sth.Date <= #" & EndOfWeek & "#"). Let's take StartOfWeek = 10.8.2015 and EndOfWeek = 16.8.2015.
When I would go into query designer to check the condition, it would say:
">= #8.10.2015# And <= #16.8.2015#"
I don't get it why it started doing this, anyone has any idea?
You may not understand the table names, but it shouldn't matter.
Also, sSql is just the standard query without any filters on it.
A while ago I made a search in Access form, that works on changing the recordsource. Don't ask me why I did it that way, but now I just want to stick with it. The solution worked great, but now, it doesn't anymore. After some debugging, I figured out the problem.
Let's say we have selected "This week" as for "tasks that are due this week". I calculate the start and end of the week (dates). Then I add WHERE to my query, containing those dates. I checked, the dates are inserted into the query correctly, but it won't show anything. So I went and checked the query in the query designer when the WHERE sentence was added, and I noticed that it turned the day and month around, but only for StartOfWeek
For example, I inserted via code the sentence (WHERE Sth.Date >= #" & StartOfWeek & "# And Sth.Date <= #" & EndOfWeek & "#"). Let's take StartOfWeek = 10.8.2015 and EndOfWeek = 16.8.2015.
When I would go into query designer to check the condition, it would say:
">= #8.10.2015# And <= #16.8.2015#"
I don't get it why it started doing this, anyone has any idea?
Code:
tempDate = Date - Weekday(Date, 2) + 1
StartOfWeek = "#" & Day(tempDate) & "/" & Month(tempDate) & "/" & Year(tempDate) & "#"
tempDate = Date - Weekday(Date, 2) + 7
EndOfWeek = "#" & Day(tempDate) & "/" & Month(tempDate) & "/" & Year(tempDate) & "#"
'MsgBox sSql & "WHERE Naloge.Opraviti >= " & StartOfWeek & " AND Naloge.Opraviti <= " & EndOfWeek /Debugging purpose
[Forms]![Seznam opravil]![Naloge subform].Form.RecordSource = sSql & "WHERE Naloge.Opraviti >= " & StartOfWeek & " AND Naloge.Opraviti <= " & EndOfWeek
You may not understand the table names, but it shouldn't matter.
Also, sSql is just the standard query without any filters on it.