Access not using date properly (1 Viewer)

[mlahajnar]

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?

            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.

 

[mlahajnar]

Update:

So I tried to switch the dates (position in the sentence) around to see if the problem goes away for some reason, only to find out that it's still affecting the date, altho now StartOfWeek displays properly, EndOfWeek gets the date switched.

I thought that I would just switch the date by hand before actually entering it in, what are your thought?

 

[arnelgp]

try adding Format function in your query statement:

(WHERE Sth.Date >= #" & Format(StartOfWeek,"mm\/dd\/yyyy") & "# And Sth.Date <= #" & Format(EndOfWeek,"mm\/dd\/yyyy") & "#")

 

[mlahajnar]

I just did it by hand; where I calculate start and end of week i just switched the day and month. But it's really wierd becouse it does it for only one date. If I switch them around, the one that was working before now doesnt and vice versa.

 

[arnelgp]

this part:
[Forms]![Seznam opravil]![Naloge subform].Form.RecordSource = sSql & "WHERE Naloge.Opraviti >= " & StartOfWeek & " AND Naloge.Opraviti <= " & EndOfWeek

should be:
[Forms]![Seznam opravil]![Naloge subform].Form.RecordSource = sSql & "WHERE Naloge.Opraviti >= #" & Format(StartOfWeek,"mm\/dd\/yyyy") & "# AND Naloge.Opraviti <= #" & Format(EndOfWeek,"mm\/dd\/yyyy") & "#"

 

[mlahajnar]

Okay, I'll try that, Thanks

 

[mlahajnar]

The problem is, that I have start and end of week declared as strings, if you can see in the code how i get them. Unless if VBA automaticly changes from string to date when you have format "dd/mm/yyyy", then this wont work :/

 

[gemma-the-husky]

I presume you live somewhere where you have UK dates

US dates are mm/dd/yyyy
UK dates are dd/mm/yyyy

what happens is that SQL always tries to treat a date as a US date,

so 16/7/2015 is 16th July but
12/7/15 becomes December 7th.

to force a date like 12/7/15 to be treated as a UK date, you need to force SQL to regard it as a UK date - hence the advice.

I find this format(mydate,"long date") is a simple fix, although it is not as rigorous as the other example you have benn given.

 

[mlahajnar]

One question, kinda off-topic, kinda not

If I have a string "11/8/2015" and I put it in the format(here,"long date"), will it work?
And the other thing, I have dates in my table saved by the UK standard, will searching using US standard actually work?

Anyway, for now I'll just google the first question and then try what you suggested.

 

[mlahajnar]

Never mind, the solution below worked fine, without anything else needed.

this part:
[Forms]![Seznam opravil]![Naloge subform].Form.RecordSource = sSql & "WHERE Naloge.Opraviti >= " & StartOfWeek & " AND Naloge.Opraviti <= " & EndOfWeek

should be:
[Forms]![Seznam opravil]![Naloge subform].Form.RecordSource = sSql & "WHERE Naloge.Opraviti >= #" & Format(StartOfWeek,"mm\/dd\/yyyy") & "# AND Naloge.Opraviti <= #" & Format(EndOfWeek,"mm\/dd\/yyyy") & "#"

 

[RainLover]

I believe the Allen Browne site has some good examples of formatting Dates.

Just for future reference.

 

[gemma-the-husky]

One question, kinda off-topic, kinda not

If I have a string "11/8/2015" and I put it in the format(here,"long date"), will it work?
And the other thing, I have dates in my table saved by the UK standard, will searching using US standard actually work?

Anyway, for now I'll just google the first question and then try what you suggested.

the date is actually stored in your tables as an integer (well, decimal number, but for the sake of argument, an integer), which represents the number of days since a reference date that is (I think 30/11/1899) - so that is an absolute, its not US or UK.

you can format the value for presentation as a date, and the format "short date" depending on where you are will either show dd/mm/yyyy or mm/dd/yyyy. The alternative formats "medium date" shows the month as a 3 letter abbreviation, and "long date" in full. So once you get away from short date, there is no ambiguity.

eg. http://dailydoseofexcel.com/archive...short-date-medium-date-and-long-date-formats/

it's when you go the other way that you have a problem. If you use a date in a SQL statement, then it will get treated as a US date, if possible - unless you force it to be treated in a different way.

and by the way, it's not a string "11/08/2015" - it has to be surrounded in characters called octothorpes # to be treated as a date - hence #11/08/2015#.