Problem in VB SQL Query

[MeeMee]

Hii

I am working on a project right now, and I have this problem that I spent lots of time trying to solve but couldn't ....

I am using VB6 and MS Access for the database .. I have a table in my database called TRequests and it has a date field of the format Short Date (mm/dd/yyyy), the field's name is ReqDate.

On the form the user should enter FromDate and ToDate , then the code should retrieve all records where ReqDate is between FromDate and ToDate ..

My code doesn't produce any errors but the report generated doesn't show and record and the record source seems to be empty !!!

Here is my code:

FromDate = CDate(fromm & "/" & fromDayCmb.Text & "/" & fromYearCmb.Text)
ToDate = CDate(tom & "/" & toDayCmb.Text & "/" & toYearCmb.Text)

w = "Select * from TRequests where ReqDate >= #" & FromDate & "# "
w = w & "And Reqdate <= #" & ToDate & "# "

Set rs = cn.Execute(w)

Please Help,

Thanks in Advance ..

[QVeen72]

Hi,

Keep a BreakPoint and Check if FromDate and ToDate are bieng Populated properly.
When you are building a Date from a Day+Month+Year from different Controls, its always preferable to use "DateSerial " Function.. This way there is no confusion between Day and Month with Regional settings..


Regards
Veena

[MeeMee]

Hi,

Keep a BreakPoint and Check if FromDate and ToDate are bieng Populated properly.
When you are building a Date from a Day+Month+Year from different Controls, its always preferable to use "DateSerial " Function.. This way there is no confusion between Day and Month with Regional settings..


Regards
Veena

I checked FromDate and ToDate in debug mode, they are being populated properly with dates in this format mm/dd/yyyy ...

[QVeen72]

Hi,

Try to use "Between" like this :

[code=oracle]
w = "Select * from TRequests where ReqDate Between #" & FromDate & "# "
w = w & "And #" & ToDate & "# "

OR

w = "Select * from TRequests where ReqDate Between CDate('" & FromDate & "') "
w = w & "And CDate('" & ToDate & "') "

[/code]

Also check you have some data for that period..

Regards
Veena

[MeeMee]

Hi,

Try to use "Between" like this :

[code=oracle]
w = "Select * from TRequests where ReqDate Between #" & FromDate & "# "
w = w & "And #" & ToDate & "# "

OR

w = "Select * from TRequests where ReqDate Between CDate('" & FromDate & "') "
w = w & "And CDate('" & ToDate & "') "

[/code]

Also check you have some data for that period..

Regards
Veena

I changed a line in the code and it now retrieve data but the problem is that it doesn't retrieve data within the specifed period ...
line

Set HFR_empDR1.DataSource = rs

changed to

Set HFR_empDR1.DataSource = Adodc1.Recordset

I also tried the between queries you gave me, the first one have the same problem, it shows more records, for example if I specify records with a date between two dates from Novemeber, it also retrived a record from December ..

The second query generated a run-time error
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

and it points to this line

Set rs = cn.Execute(w)

Appreciate your time and help ..

[QVeen72]

Hi,

Check the database, if it has stored in "dd-mm-yyyy" instead of "mm-dd-yyyy"

keep a copy of database, remove all the existing data and add new data and check.. and what format is your Regional Date settings..?

Regards
Veena

[MeeMee]

Hi,

Check the database, if it has stored in "dd/mm/yyyy" instead of "mm/dd/yyyy"

keep a copy of database, remove all the existing data and add new data and check.. and what format is your Regional Date settings..?

Regards
Veena

Hi,

I checked the database and the format of the ReqDate field, it is short date m/d/yyyy. Then I deleted the data, added new , checked again, still all records are shown in the report (This happens when I use the Set

HFR_empDR1.DataSource = Adodc1.Recordset

but when I use it this way

Set HFR_empDR1.DataSource = rs

the report shows no records)

The regional setting for the date is short date (M/d/yyyy) and long date (dddd, MMMM dd, yyyy)

[QVeen72]

Hi,

Change your Query :

[code=oracle]
w = "Select * from TRequests where ReqDate >= #" _
& Format(FromDate ,"m/d/yyyy" ) _
& "# And Reqdate <= #" & Format(ToDate," m/d/yyyy") & "# "
[/code]

OR
avoid Confusion and use "Julian" date format..

[code=oracle]
w = "Select * from TRequests where CLng(ReqDate ) >= " _
& CLng(FromDate) _
& " And CLng(Reqdate) <= " & CLng(ToDate)
[/code]


Regards
Veena

[MeeMee]

Hi,

Change your Query :

[code=oracle]
w = "Select * from TRequests where ReqDate >= #" _
& Format(FromDate ,"m/d/yyyy" ) _
& "# And Reqdate <= #" & Format(ToDate," m/d/yyyy") & "# "
[/code]

OR
avoid Confusion and use "Julian" date format..

[code=oracle]
w = "Select * from TRequests where CLng(ReqDate ) >= " _
& CLng(FromDate) _
& " And CLng(Reqdate) <= " & CLng(ToDate)
[/code]


Regards
Veena

Hi,

I tried both queries but still the same ... could the problem be from the recordset or the execute command ...

This is the code till now :

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

fromm = (fromMonthCmb.ListIndex) + 1
tom = (toMonthCmb.ListIndex) + 1

            
FromDate = CDate(fromm & "/" & fromDayCmb.Text & "/" & fromYearCmb.Text)
ToDate = CDate(tom & "/" & toDayCmb.Text & "/" & toYearCmb.Text)

w = "Select * from TRequests where CLng(ReqDate ) >= " _
        & CLng(FromDate) _
        & "  And CLng(Reqdate) <= " & CLng(ToDate)

answer = MsgBox("Do you want to view the report", vbYesNo + vbQuestion, "Confirm")

If answer = vbYes Then
     Set rs = cn.Execute(w)
     ss = "Requests Report"
     s2 = FromDate
     s3 = ToDate
     s4 = "To"

Set HFR_empDR1.DataSource = rs ' (This shows Nothing)
' Set HFR_empDR1.DataSource = Adodc1.Recordset (This shows all records)

HFR_empDR1.Sections("Section4").Controls("rptHeader").Caption = ss
HFR_empDR1.Sections("Section4").Controls("from").Caption = s2
HFR_empDR1.Sections("Section4").Controls("min").Caption = s4
HFR_empDR1.Sections("Section4").Controls("min").Caption = s4
HFR_empDR1.Show
End If

I tried running the code in debug mode to check the values of the variables and objects , the FromDate and ToDate are correct , is there a way to check the records in the recordset ???

Another thing , when I used Julian format, in debug mode , I noticed that FromDate has a value of 39387 , and the date selected by the user is 11/1/2007 , Is this correct ??

I am trying everything but it is not working :(

Thanks again for you help

[QVeen72]

Hi,

Yes the value is Perfectly all right.
But I'am not sure, if the DataReport can take Ad-hoc Queries like that.
In debug mode, copy the Query and run in Access and check. If query is working alright, then you may have to Change the logic of DataReport.
Add Parameters to the Command Object on which the data report is based. and pass the From and To dates Parameters to datareport..

Regards
Veena

[MeeMee]

Hi,

Yes the value is Perfectly all right.
But I'am not sure, if the DataReport can take Ad-hoc Queries like that.
In debug mode, copy the Query and run in Access and check. If query is working alright, then you may have to Change the logic of DataReport.
Add Parameters to the Command Object on which the data report is based. and pass the From and To dates Parameters to datareport..

Regards
Veena

Hi Veena

I tried the query in Access, and it returned the correct records... I just created a query in Access and entered the dates as parameter ..

I don't know why it is not working in VB, I have already written a code for another report which shows all records (no condition ,Select *) and it worked perfectly .. Why this one is not working...

I know that it would be much easier to switch to Access for reports but I have already created other reports in VB, which are working ..

Thanks again for you help

[QVeen72]

Hi,

The query does work in VB.
But not for DataReports directly.
As I said in my Prev Post, Create 2 Parameters for command object and pass those parameters, before showing the DataReport. This is the only way to make DataReports Dynamic.. Just by Chaning the RS Object, DataReports are not Refreshed/Filtered..


Regards
Veena

[MeeMee]

Hi,

The query does work in VB.
But not for DataReports directly.
As I said in my Prev Post, Create 2 Parameters for command object and pass those parameters, before showing the DataReport. This is the only way to make DataReports Dynamic.. Just by Chaning the RS Object, DataReports are not Refreshed/Filtered..


Regards
Veena

OK , I created a new Vb Project just in case , created a new form and passed the parameters before showing the report, and it worked :D , The records retrieved were correct ..

Now , when I did it on my original full project , I get a run-time error saying that the Report width is larger than the paper width , I tried changing the width but it is not working ..

[QVeen72]

Hi,

To avoid that error download and install SP5 or higher..

REgards
Veena

[MeeMee]

Hi,

To avoid that error download and install SP5 or higher..

REgards
Veena

Hi Veena,

First I want to thank you for helping me with the data report :D

I installed SP5 but the error still happens, I also installed SP6 but still the same ...

However, when I added this line
D

ataReport2.Orientation = rptOrientLandscape

it worked but the report was displayed in landscape , isn't there a way to avoid the error to display the report in portrait ??

Thanks again