[RESOLVED] Problem in monthly records.

[dexjel140503]

good day! im trying to search records between 2 dates (monthly,weekly). what is wrong with my query/code?

<code>
paramDate1 = CDate(DateFrom)
paramDate2 = CDate(DateTo)

Set rs = New ADODB.Recordset
rs.Open "Select * from tblEvents where EventDate between #" & paramDate1 & "# AND #" & paramDate2 & "#", cn, 3, 2
</code>

it didn't display monthly record but can display individual date (e.g records dated 12/1/2012 but not 12/1/2012 to 12/31/2012). can u pls give me some advice? or am i doing something wrong?
thanks guys.

[brss]

Hello, Could you please do something like below:

rs.Open "Select * from tblEvents where EventDate between " & "#" & paramDate1 & "#" & " AND " & "#" & paramDate2 & "#", cn, 3, 2

[DataMiser]

The method shown in post #2 will not be any different.

What database are you using?

[brss]

@ Master Guru DataMiser.. you totally right. If he use DTpicker instead assuming using Access DB, like below because i'm using DTPIcker format mm/dd/yyyy and work fine when i show Data between two Date..

S_Date = DTPicker1.Value
E_Date = DTPicker2.Value

rs.Open "Select * from tblEvents where EventDate between " & "#" & S_Date & "#" & " AND " & "#" & E_Date & "#", cn, 3, 2

[jggtz]

The dates must be formated as follows:
Access: #yyyy-mm-dd#
SQL: 'yyyymmdd'

[gibra]

As has often been suggested on this and other forums,
you must use Command with parameters, it avoids all the problems of formatting,
which as you know can be different depending on the type of database.

Database - Why should I use Parameters instead of putting values into my SQL string?
http://www.vbforums.com/showthread.php?t=548787

See also sample projects on my sign (both ADODB and ADO.NET)

[dexjel140503]

Hi! Im using Access 2003 as a database. Mr. brss i tried ur query but i still didn't get the correct records. BTW, what f i change the field type in my table from text to date/time? will it help? .

[brss]

Well my friend, I'm sorry for not helping you out, I will try my best here. I think your problem related to your saved format in your DB. Have you checked your Date field format, How it's behave? This problem happend to me long time ago when i call Excel report between two date msgbox "No data found" while i have lot of data! try to use DTPicker with field Date/Time. Wish you the best. Thank you

Edited: DTPicker format will behave as your System date format. And you can customized it for sure.

[dexjel140503]

whew! i finally managed to do it. for those who got same problem revise ur queries like this
Code:

("Select * from tblEvents where Eventdate >= #" & firstMonth & "# AND EventDate <= #" & lastMonth & "#")

just make sure the field in ur DB is a Date/Time formatted as "mm/dd/yyyy". Thanks a lot guys! This forum is really helpful. Cheers!