month and day keep switching in my date

[Toadums]

Hi everyone, I am having this problem when trying to call a report from within vba...

pretty much what is happening, I am getting my data from sql via an ODBC connection, and I am querying that table to get the necessary parameters for my report...

When the data comes in, the dates are formatted like dd/mm/yyyy, which I think access doesnt like very much..the dates are then put into a listbox, where people can pick a date to query by. here is a screen of my form:

ok so that is how the dates are formatted. So I am trying to change the dates in my vba code so that it queries them correctly...

here is the code I have to deal with the dates:

If (emptyListBox = False) Then

    lbdate = Me.DateListBox.Value
    
    lbdate = Format(Month(CDate(lbdate)), "00") & "/" & Format(day(CDate(lbdate)), "00") & "/" & CStr(Year(CDate(lbdate)))



DateStr = DateStr & " [Date] >= #" & DateAdd("d", -5, lbdate) & "# AND [Date] <= #" & lbdate & "#"

End If

I did originally have a BETWEEN in my sql clause instead of <=/>=, but i thought maybe that was the issue (it wasnt)...

I am running the report with:

DoCmd.OpenReport "rei_report", acViewReport, , criteria

and then this is what my criteria for my query actually ends up being

the #09/05/2010# comes from the DateAdd("d", -5, lbdate), where the second date is just lbDate. so I dont know what is happening...but when I do DateAdd, it seems to just forget about how I just formatted lbDate, and switches the format back to dd/mm/yyyy....which is lame.

anyways...hopefully someone understands what is going on..cause I dont, which makes it hard to explain..so feel free to ask me to clarify something if you dont get it

Thanks, Paul

 

[jdraw]

As I read the sample you have commented from Access, it is saying

Name = "Paul" AND
[Date] greater or equal to Sept 5,2010 and
[Date] less than or equal to May 14, 2010.


You should be aware that Date is a reserved word /BuiltIn function in Access.

What Dates are greater than Sept 2010 and less than May 2010???

This expression will return the Day value of today's Date + 9 days

Datepart("d",Date+9)

so if today is 13 May, 9 days hence is 22 May.

What exactly do you want your expression to do -- in plain english?

 

[boblarson]

Also, unless you apply specific date Constant formatting to your dates at the time you pass them to the query you must use the U.S. date method for criteria (thank Microsoft for that one).

 

[Toadums]

You should be aware that Date is a reserved word /BuiltIn function in Access.

err..it will be a pretty huge mess to change my Date field, since it is coming from SQL, I would have to change a LOOOOOOOOOOOOOOT of code in a lot of files to have the name changed...cause this code is being used by php/c# and now Access...is it that crutial that I change the name?

As I read the sample you have commented from Access, it is saying

Name = "Paul" AND
[Date] greater or equal to Sept 5,2010 and
[Date] less than or equal to May 14, 2010.
What exactly do you want your expression to do -- in plain english?

i know that there is no such date, that is the problem, lol, it just returns a blank report to me.

Ok pretty much what i want to do, is take the date that is selected in the listbox, and get all records which are of the week ENDING in the date in the combo box......that might not make sense..

ok. so lets say I select 14/05/2010 (may 14th), I would want to query on all the dates from

08/05/2010 to 14/05/2010, so from may 8 to may 14.

then of course there are other parameters...name, client, project ETC, but all those ones are easy...it is just the date i am having troubles with.

So yeah. like you see in the screen of my criteria string...it does the dateAdd("d",-6,lbDate), and then switches the month and day (which is why we ended up with some date in september.......lol)


and also, what exactly do you mean boblarson? what is the "date method criteria"

thanks guys!

 

[jdraw]

What Bob is saying is that MS Access by default uses the USA method of storing/handling dates.
That means by default you'll get mm/dd/yyyy


Using your example

ok. so lets say I select 14/05/2010 (may 14th), I would want to query on all the dates from

08/05/2010 to 14/05/2010, so from may 8 to may 14.

If you select 14/05/2010 from the list, and this is a text datatype called lbdate,
then I recommend you define a new variable dtReportDate of date datatype.

Then, dtReportDate = CDate(format(lbdate,"dd/mm/yyyy"))
and you can use it in building your query to select the proper data for your report.

Here's a small test, you can run to see that it builds a proper Between statement with Dates.

Note: I used - 6 because the Between in Access is inclusive
meaning that data for both the low and high values will be included.
So, as I understand you, the data for 08/05/2010 should not be included in a report for week ending 14/05/2010.

Sub awtest()
Dim lbdate As String
Dim dtReportDate As Date
Dim sWhere As String
lbdate = "14/05/2010"
dtReportDate = CDate(Format(lbdate, "dd/mm/yyyy"))
sWhere = " WHERE myDate Between #" & dtReportDate & "# AND #" & dtReportDate - 6 & "#"
Debug.Print sWhere
End Sub

Note also: myDate is the field in your table that has the proper date associated with activity/work whatever.

 

[Toadums]

still not working...It might be because of the name of my date field, but like I said, it would be a HUGE mess to try to rename the field...is there a way that with an OCDB connection, I can change the name of a field without it changing in SQL?

Because right now it is querying between 14/05/2010 and 08/05/2010, but the only two dates that I get are:

14/05/2010 and 04/07/2010...which doesnt really make sense..there are about 15 entries in the data that have dates between the 8th and 15th of may.

I might just scrap this sub and start from scratch...the code is getting a little old now anyways...it could also be because the data is coming into access from SQL with the dates formatted as dd/mm/yyyy........?

 

[Brianwarnock]

2 points

1 Don't worry about the Date field, sure you shouldn't use it but as long as you always code [Date] Access will accept it as your field name.

2 Why did you reformat your date?
You do not hard code dates anywhere, if you did you would need to use US format, if creating SQL strings you would need them to be in US format, but otherwise just carry on with local format which ACCESS will revert to in cases like your dateadd.

It all terrible confusing, you could just wrap a format around all of you date work.

Brian

 

[Toadums]

2 points

1 Don't worry about the Date field, sure you shouldn't use it but as long as you always code [Date] Access will accept it as your field name.

2 Why did you reformat your date?
You do not hard code dates anywhere, if you did you would need to use US format, if creating SQL strings you would need them to be in US format, but otherwise just carry on with local format which ACCESS will revert to in cases like your dateadd.

It all terrible confusing, you could just wrap a format around all of you date work.

Brian

Thanks, I will leave my dates as they are then

And i format my dates because I the month and day seem to be switching...so I was trying to force them to all be in the same format (failed there).

here are 4 screens showing you the process I take:


First showing you my form...so I have 14/05/2010 selected and run the report:

next is the string of dates which it should be querying the report by:

as you can see, they are all the same format.

Next up we we have the result of the report:

So. I kind of finally figured out what is happening here I think. It is querying the days of the month that are not month numbers (so 13-31) all fine and dandy...but it gets confused when it get something with the day of 1-12...see in that example right there..it has october 5th coming up instead of may 10th (so instead of giving me 10/05/2010, it gives 05/10/2010...using the dd/mm/yyyy format that my data is in (that I cant seem to change no matter how hard i force VBA)...

And just for reference, here is part of the data i am querying...as you can see there are a LOT of dates in between the 8th and 14th of may! (i marked them with red ticks)

so yeah...hopefully these screens can help make sense of the issue to you guys, cause i am starting to bite my fingernails again right now because this is driving me nuttttts!

any ideas at all, just throw them out there...please! lol

Thanks again for everything,
Paul

 

[boblarson]

In the query, the criteria should be in US date format.

 

[Brianwarnock]

As I said in mypost hardcoding dates in vba or in Sql need to be in US format, the confusion comes when you create a SQL string in VBA without hardcoding the dates, VBA creates a string with hardcoded dates in your local format unless you tell it otherwise, which you must
In your string in the original post lbdate would be ok as you forced the US format, but the virtual/alias field generated by the dateadd would be in your local format. It is at the point of writing the sqlstring that you should do the formatting.

See http://allenbrowne.com/ser-36.html

Sorry I wasn't clear in my original post.

Brian

 

[Toadums]

thanks for the link!

ok, it works perfect now!!

i changed my code to:

Dim lbDate As Date
    Dim dtReportDate As Date
    Dim newDate As Date
       
    lbDate = Me.DateListBox.Value
    
    dtReportDate = CDate(Format(lbDate, "mm/dd/yyyy"))
    newDate = lbDate - 6
    newDate = CDate(Format(newDate, "mm/dd/yyyy"))
    
    DateStr = " [Date] Between #" & newDate & "# AND #" & dtReportDate & "#"

i think my problem was that i was trying to dateadd after i formatted to us format...and that just caused all sorts of trouble

thanks for all the help guys!

Paul

 

[Brianwarnock]

i think my problem was that i was trying to dateadd after i formatted to us format...and that just caused all sorts of trouble

thanks for all the help guys!

Paul

yep thats what I said in my first post.

Glad its sorted, I still prefer to format in the strsql, but each to his own.

Brian

 

[Toadums]

yep thats what I said in my first post.

Glad its sorted, I still prefer to format in the strsql, but each to his own.

Brian

ya, I know you did lol...I just couldnt figure out what you meant hehe.

So what do you mean by format in the strsql...you mean, in my case, DateStr?

 

[Brianwarnock]

ya, I know you did lol...I just couldnt figure out what you meant hehe.

So what do you mean by format in the strsql...you mean, in my case, DateStr?

Yes, it seems to me that if you do it when you need it then there can be no confusion, so calling that function by allen Browne as per his example keeps it simple, but I have no problem with what you did, infact i nearly suggested it, and I have seen allen's code used in line rather than being called as a function.

Provided one understands what one is doing then no problems should arise, gosh that sounds pompous

happy programming

Brian