By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,852 Members | 2,116 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,852 IT Pros & Developers. It's quick & easy.

Problem in VB SQL Query

P: 35
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:

Expand|Select|Wrap|Line Numbers
  1. FromDate = CDate(fromm & "/" & fromDayCmb.Text & "/" & fromYearCmb.Text)
  2. ToDate = CDate(tom & "/" & toDayCmb.Text & "/" & toYearCmb.Text)
  3.  
  4. w = "Select * from TRequests where ReqDate >= #" & FromDate & "# "
  5. w = w & "And Reqdate <= #" & ToDate & "# "
  6.  
  7. Set rs = cn.Execute(w)
Please Help,

Thanks in Advance ..
Jan 7 '08 #1
Share this Question
Share on Google+
32 Replies


QVeen72
Expert 100+
P: 1,445
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
Jan 7 '08 #2

P: 35
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 ...
Jan 7 '08 #3

QVeen72
Expert 100+
P: 1,445
Hi,

Try to use "Between" like this :

Expand|Select|Wrap|Line Numbers
  1. w = "Select * from TRequests where ReqDate Between  #" & FromDate & "# "
  2. w = w & "And  #" & ToDate & "# " 
  3.  
  4. OR
  5.  
  6. w = "Select * from TRequests where ReqDate Between CDate('" & FromDate & "') "
  7. w = w & "And CDate('" & ToDate & "') " 
  8.  
  9.  
Also check you have some data for that period..

Regards
Veena
Jan 7 '08 #4

P: 35
Hi,

Try to use "Between" like this :

Expand|Select|Wrap|Line Numbers
  1. w = "Select * from TRequests where ReqDate Between  #" & FromDate & "# "
  2. w = w & "And  #" & ToDate & "# " 
  3.  
  4. OR
  5.  
  6. w = "Select * from TRequests where ReqDate Between CDate('" & FromDate & "') "
  7. w = w & "And CDate('" & ToDate & "') " 
  8.  
  9.  
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
Expand|Select|Wrap|Line Numbers
  1. Set HFR_empDR1.DataSource = rs
changed to
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. Set rs = cn.Execute(w)
Appreciate your time and help ..
Jan 7 '08 #5

QVeen72
Expert 100+
P: 1,445
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
Jan 7 '08 #6

P: 35
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
Expand|Select|Wrap|Line Numbers
  1. HFR_empDR1.DataSource = Adodc1.Recordset
but when I use it this way
Expand|Select|Wrap|Line Numbers
  1. 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)
Jan 8 '08 #7

QVeen72
Expert 100+
P: 1,445
Hi,

Change your Query :

Expand|Select|Wrap|Line Numbers
  1. w = "Select * from TRequests where ReqDate >= #" _
  2. & Format(FromDate,"m/d/yyyy" ) _
  3. & "#  And Reqdate <= #" & Format(ToDate,"m/d/yyyy") & "# " 
  4.  
OR
avoid Confusion and use "Julian" date format..

Expand|Select|Wrap|Line Numbers
  1. w = "Select * from TRequests where CLng(ReqDate ) >= " _
  2. & CLng(FromDate) _
  3. & "  And CLng(Reqdate) <= " & CLng(ToDate) 
  4.  

Regards
Veena
Jan 8 '08 #8

P: 35
Hi,

Change your Query :

Expand|Select|Wrap|Line Numbers
  1. w = "Select * from TRequests where ReqDate >= #" _
  2. & Format(FromDate,"m/d/yyyy" ) _
  3. & "#  And Reqdate <= #" & Format(ToDate,"m/d/yyyy") & "# " 
  4.  
OR
avoid Confusion and use "Julian" date format..

Expand|Select|Wrap|Line Numbers
  1. w = "Select * from TRequests where CLng(ReqDate ) >= " _
  2. & CLng(FromDate) _
  3. & "  And CLng(Reqdate) <= " & CLng(ToDate) 
  4.  

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 :

Expand|Select|Wrap|Line Numbers
  1. Dim cn As ADODB.Connection
  2. Dim rs As ADODB.Recordset
  3.  
  4. fromm = (fromMonthCmb.ListIndex) + 1
  5. tom = (toMonthCmb.ListIndex) + 1
  6.  
  7.  
  8. FromDate = CDate(fromm & "/" & fromDayCmb.Text & "/" & fromYearCmb.Text)
  9. ToDate = CDate(tom & "/" & toDayCmb.Text & "/" & toYearCmb.Text)
  10.  
  11. w = "Select * from TRequests where CLng(ReqDate ) >= " _
  12.         & CLng(FromDate) _
  13.         & "  And CLng(Reqdate) <= " & CLng(ToDate)
  14.  
  15. answer = MsgBox("Do you want to view the report", vbYesNo + vbQuestion, "Confirm")
  16.  
  17. If answer = vbYes Then
  18.      Set rs = cn.Execute(w)
  19.      ss = "Requests Report"
  20.      s2 = FromDate
  21.      s3 = ToDate
  22.      s4 = "To"
  23.  
  24. Set HFR_empDR1.DataSource = rs ' (This shows Nothing)
  25. ' Set HFR_empDR1.DataSource = Adodc1.Recordset (This shows all records)
  26.  
  27. HFR_empDR1.Sections("Section4").Controls("rptHeader").Caption = ss
  28. HFR_empDR1.Sections("Section4").Controls("from").Caption = s2
  29. HFR_empDR1.Sections("Section4").Controls("min").Caption = s4
  30. HFR_empDR1.Sections("Section4").Controls("min").Caption = s4
  31. HFR_empDR1.Show
  32. 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
Jan 8 '08 #9

QVeen72
Expert 100+
P: 1,445
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
Jan 8 '08 #10

P: 35
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
Jan 8 '08 #11

QVeen72
Expert 100+
P: 1,445
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
Jan 8 '08 #12

P: 35
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 ..
Jan 8 '08 #13

QVeen72
Expert 100+
P: 1,445
Hi,

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

REgards
Veena
Jan 8 '08 #14

P: 35
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
Expand|Select|Wrap|Line Numbers
  1. 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
Jan 9 '08 #15

QVeen72
Expert 100+
P: 1,445
Hi,

How many fields you are trying to Display in the datareport...?

Regards
Veena
Jan 9 '08 #16

P: 35
Hi,

How many fields you are trying to Display in the datareport...?

Regards
Veena
four fields ... It works when I move the fields and make them so close to each other and when I make the font small so that the width of the report is reduced.
Jan 9 '08 #17

QVeen72
Expert 100+
P: 1,445
Hi,

Before showing the report, give this :

DataReport1.Orientation = rptOrientLandscape
DataReport1.Show


Regards
Veena
Jan 9 '08 #18

P: 35
Hi,

Before showing the report, give this :

DataReport1.Orientation = rptOrientLandscape
DataReport1.Show


Regards
Veena
Ya, I already tried this and it worked ... but some reports don't need to be in landscape so I'll try to reduce the width until it works ...

thanks alot Veena :)
Jan 9 '08 #19

P: 35
Hi again,

I have a problem in the data reports, sometimes it shows all required records except one .. however, when I run the code in debug mode and go through each line until the report is generated, I can see all records in the report including the missing one ..
Jan 13 '08 #20

QVeen72
Expert 100+
P: 1,445
Hi again,

I have a problem in the data reports, sometimes it shows all required records except one .. however, when I run the code in debug mode and go through each line until the report is generated, I can see all records in the report including the missing one ..
Hi,

May be Some of the Fields of that Record are null,
Or there is no Proper Linking.. Try to run the query in Query Designer and find how many records are returned..

Regards
Veena
Jan 13 '08 #21

P: 35
Hi,

May be Some of the Fields of that Record are null,
Or there is no Proper Linking.. Try to run the query in Query Designer and find how many records are returned..

Regards
Veena
I checked the records returned by the query , and it is correct, only one record isn't showing in the report , also all the fields have values..

However, when I go step by step in debug mode until I am done wit hthe code and the report is generated, all records are shown in the report .. Why is this happening only in debug mode ??
Jan 13 '08 #22

QVeen72
Expert 100+
P: 1,445
Hi,

May be some time is needed, in between..
After Passing parameters to command object, and before showing the report, use "DoEvents" or use "Sleep" for few seconds.. give a Mesage box in between like "Generating Report"..

Regards
Veena
Jan 13 '08 #23

P: 35
Hi,

May be some time is needed, in between..
After Passing parameters to command object, and before showing the report, use "DoEvents" or use "Sleep" for few seconds.. give a Mesage box in between like "Generating Report"..

Regards
Veena

It worked , thaaanks alooot :D
Jan 13 '08 #24

P: 35
It worked , thaaanks alooot :D
After testing it a few times, I found out that using Sleep solves the problem except when the query returns 1 record, It is not shown ?? I tried increasing the Sleep time but it didn't work ??
Jan 14 '08 #25

QVeen72
Expert 100+
P: 1,445
Hi,

Can you post the code here...?

Regards
Veena
Jan 14 '08 #26

P: 35
Hi,

Can you post the code here...?

Regards
Veena
OK..

I am using the designer to add data environment, data report and command .. I set the command text with query that worked ..

The main code calls a function "Record5" which searches table "Requests" and find records which has a specific type of request and directorate name entered by the user from the form. If the records are found another function "AddRecord" is called to add records to a temporary table "TRequests".



//Main Code

Expand|Select|Wrap|Line Numbers
  1. fromm = (fromMonthCmb.ListIndex) + 1
  2. tom = (toMonthCmb.ListIndex) + 1
  3. FromDate = CDate(fromm & "/" & fromDayCmb.Text & "/" & fromYearCmb.Text)
  4. ToDate = CDate(tom & "/" & toDayCmb.Text & "/" & toYearCmb.Text)
  5.  
  6. If IsDate(FromDate) = True And IsDate(ToDate) = True Then
  7.    Call Records5
  8.  
  9.    Sleep 600 //works fine with records > 1
  10.  
  11.   Load DataEnvironment4
  12.   With DataEnvironment4
  13.       If .rsCommand1.State <> 0 Then .rsCommand1.Close
  14.       .Command1 CDate(FromDate), CDate(ToDate)
  15.   End With
  16.  
  17.   answer = MsgBox("Show Report ??", vbYesNo + vbQuestion, "Confirm")
  18.   If answer = vbYes Then
  19.    s2 = FromDate
  20.    s3 = ToDate
  21.    s4 = "To"
  22.  
  23. DataReport4.Sections("Section4").Controls("rptHeader").Caption = ss
  24. DataReport4.Sections("Section4").Controls("direclbl").Caption = direcCmb.Text
  25. DataReport4.Sections("Section4").Controls("from").Caption = s2
  26. DataReport4.Sections("Section4").Controls("tolbl").Caption = s3
  27. DataReport4.Sections("Section4").Controls("min").Caption = s4
  28. DataReport4.Refresh
  29.  
  30.  
  31. If DataReport4.Visible = False Then DataReport4.Show
  32.  
  33. End If


Expand|Select|Wrap|Line Numbers
  1. Private Sub Records5()
  2. Requests.Recordset.MoveFirst
  3. Do While (Requests.Recordset.EOF = False)
  4. If Requests.Recordset!ReqSubType = ReqTypeCmb.Text And       Requests.Recordset!ReqType = rtype And Requests.Recordset!Directorate =  direcCmb.Text Then
  5.             Call AddRecord
  6. End If
  7. Requests.Recordset.MoveNext
  8. Loop
Expand|Select|Wrap|Line Numbers
  1. Private Sub AddRecord()
  2. If Adodc1.Recordset.RecordCount > 1 Then
  3. Adodc1.Recordset.MoveFirst
  4. End If
  5. Adodc1.Recordset.AddNew
  6. Adodc1.Recordset!cpr = Requests.Recordset!cpr
  7. If Requests.Recordset!Name <> "" Or Requests.Recordset!Name <> Null Then
  8.     Adodc1.Recordset!Name = Requests.Recordset!Name
  9.     reqName = Requests.Recordset!Name
  10. End If
  11. If Requests.Recordset!jobtitle <> "" Or Requests.Recordset!jobtitle <> Null Then
  12.     Adodc1.Recordset!jobtitle = Requests.Recordset!jobtitle
  13. End If
  14. If Requests.Recordset!grade <> "" Or Requests.Recordset!grade <> Null Then
  15.     Adodc1.Recordset!grade = Requests.Recordset!grade
  16. End If
  17. If Requests.Recordset!ByEmployee <> "" Or Requests.Recordset!ByEmployee <> Null Then
  18.     Adodc1.Recordset!ByEmployee = Requests.Recordset!ByEmployee
  19. End If
  20. If Requests.Recordset!status <> "" Or Requests.Recordset!status <> Null Then
  21.     Adodc1.Recordset!status = Requests.Recordset!status
  22. End If
  23. If Requests.Recordset!ReqType <> "" Or Requests.Recordset!ReqType <> Null Then
  24.     Adodc1.Recordset!ReqType = Requests.Recordset!ReqType
  25. End If
  26. If Requests.Recordset!ReqSubType <> "" Or Requests.Recordset!ReqSubType <> "-" Then
  27.     Adodc1.Recordset!ReqSubType = Requests.Recordset!ReqSubType
  28. End If
  29. If Requests.Recordset!Directorate <> "" Then
  30.     Adodc1.Recordset!Directorate = Requests.Recordset!Directorate
  31. End If
  32. If Requests.Recordset!section <> "" Or Requests.Recordset!section <> Null Then
  33.     Adodc1.Recordset!section = Requests.Recordset!section
  34. End If
  35. If Requests.Recordset!Employmentdate <> "" Or Requests.Recordset!Employmentdate <> Null Then
  36.     Adodc1.Recordset!Employmentdate = Requests.Recordset!Employmentdate
  37. End If
  38. If Requests.Recordset!reqdate <> "" Or Requests.Recordset!reqdate <> Null Then
  39.     Adodc1.Recordset!reqdate = Requests.Recordset!reqdate
  40. End If
  41. If Requests.Recordset!Comment <> "" Or Requests.Recordset!Comment <> Null Then
  42.     Adodc1.Recordset!Comment = Requests.Recordset!Comment
  43. End If
  44.  
  45. If Adodc1.Recordset.RecordCount > 1 Then
  46. Adodc1.Recordset.MoveNext
  47. End If
  48. End Sub
Jan 14 '08 #27

QVeen72
Expert 100+
P: 1,445
Hi,

Why are you looping through all the Records and adding ..?
Use Connection Object and Execute Insert Query for the selected conditions..
and this approach will be much faster than looping through..

Some thing like this :

Expand|Select|Wrap|Line Numbers
  1. Dim AConn As New ADODB.Connection 
  2. Dim sSQL As String
  3. AConn.ConnectionString = "<Your Connection String>" 
  4. 'Conn string can be copied from the Properties of Connection obj in dataenv..
  5.  
  6. AConn.Open
  7. sSQL = "Delete From TempTable"
  8. AConn.Execute sSQL
  9. sSQL = "Insrt Into TempTable(Field1,Field2...) Select F1,F2.. From MainTable Where Category='XYZ' "
  10. AConn.Execute sSQL
  11. '
  12. 'After this show the datareport
  13. '
  14.  
Regards
Veena
Jan 14 '08 #28

P: 35
Hi,

Why are you looping through all the Records and adding ..?
Use Connection Object and Execute Insert Query for the selected conditions..
and this approach will be much faster than looping through..

Some thing like this :

Expand|Select|Wrap|Line Numbers
  1. Dim AConn As New ADODB.Connection 
  2. Dim sSQL As String
  3. AConn.ConnectionString = "<Your Connection String>" 
  4. 'Conn string can be copied from the Properties of Connection obj in dataenv..
  5.  
  6. AConn.Open
  7. sSQL = "Delete From TempTable"
  8. AConn.Execute sSQL
  9. sSQL = "Insrt Into TempTable(Field1,Field2...) Select F1,F2.. From MainTable Where Category='XYZ' "
  10. AConn.Execute sSQL
  11. '
  12. 'After this show the datareport
  13. '
  14.  
Regards
Veena
I keep getting syntax error

Expand|Select|Wrap|Line Numbers
  1. sSQL = " Insert Into TRequests(CPR,Name,JobTitle,Directorate,Section,Grade,EmploymentDate,ReqDate,ReqType,ReqSubType,ByEmployee,Status,Comment,Seen) Select CPR,Name,JobTitle,Directorate,Section,Grade,EmploymentDate,ReqDate,ReqType,ReqSubType,ByEmployee,Status,Comment,Seen From Requests Where ReqType = rtype And ReqSubType = ReqTypeCmb.Text And Directorate = 'Direc1'"
rtype is a variable
Jan 14 '08 #29

QVeen72
Expert 100+
P: 1,445
Hi,

Check this :

Expand|Select|Wrap|Line Numbers
  1. sSQL = " Insert Into TRequests " _  
  2. & " (CPR,Name,JobTitle,Directorate,Section,Grade, " _
  3. & " EmploymentDate,ReqDate,ReqType,ReqSubType, " _
  4. & " ByEmployee,Status,Comment,Seen) Select " _
  5. & " CPR,Name,JobTitle,Directorate,Section,Grade, " _
  6. & " EmploymentDate,ReqDate,ReqType,ReqSubType, " _
  7. & " ByEmployee,Status,Comment,Seen From Requests "  _
  8. & " Where ReqType = '" & rtype & "'" _
  9. & " And ReqSubType = '" & ReqTypeCmb.Text & "'" _ 
  10. & " And Directorate = 'Direc1'"
  11.  
Regards
Veena
Jan 14 '08 #30

P: 35
Still getting a run-time error '-2147217900 (80040e14): Syntax error in Insert Into Statement.
Jan 14 '08 #31

QVeen72
Expert 100+
P: 1,445
Hi,

If both tables have same Structure , try this :


sSQL = " Insert Into TRequests " _
& " Select * From Requests " _
& " Where ReqType = '" & rtype & "'" _
& " And ReqSubType = '" & ReqTypeCmb.Text & "'" _
& " And Directorate = 'Direc1' "

Check all the tablename and field names are spelled properly..

Regards
Veena
Jan 14 '08 #32

P: 35
Hi,

If both tables have same Structure , try this :


sSQL = " Insert Into TRequests " _
& " Select * From Requests " _
& " Where ReqType = '" & rtype & "'" _
& " And ReqSubType = '" & ReqTypeCmb.Text & "'" _
& " And Directorate = 'Direc1' "

Check all the tablename and field names are spelled properly..

Regards
Veena
Tried it and it worked , it now shows all retrieved records even if it was one.

Thanks again :)

Hopefully this will be the last problem
Jan 14 '08 #33

Post your reply

Sign in to post your reply or Sign up for a free account.