why is this happen?


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 7 of 7

Thread: why is this happen?

Hybrid View

  1. #1
    Join Date
    Aug 2004
    Posts
    43,023

    why is this happen?

    [Originally posted by ifah]

    hi;

    i have 1 combo box here which list type of book : Education, Horror,
    Romance and Kids.

    i also have 2 DateTimePicker here.

    My question here is how to generate a report that will show data when
    i choose 'Horror' from Combo1 and the data is between the 2 DateTimePicker.

    example ( user put this in his combo box and DTPickers):

    Combo1.Text = 'Horror'
    DTPicker1.Value = 01/09/20001
    DTPicker2.Value = 05/09/2001


    i done this in:
    ----------------------
    Command1_Click

    Dim strSQL as string
    strSQL = "SELECT * FROM tblMyBooks WHERE BookType=''" & Combo1.Text & "'' AND (BookDate Between #" & DTPicker1.Value & "# AND #" & DTPicker2.Value & "#);"

    End Sub
    --------------------
    Command2_Click

    Me.Hide
    DataReport1.Show

    End Sub

    -------------


    the problem is, the report show all data from the table. the report did not show data
    that i want like in my strSQL.

    is there anything wrong here?

    could anybody please tell me why?

    thanks in advance!!


  2. #2
    Join Date
    Aug 2004
    Posts
    43,023

    Re:why is this happen?

    [Originally posted by Tom Lewandowski]

    If your string in the question is exactly the same as as the code in your app, it may have something to do with the two single quotes after the 'BookType=' and before the 'AND'.˙ Try putting only a single quote at these places.

    With two single quotes, I actually get an error. If I change it to one single quote at each place, the query works fine.

    Tom

  3. #3
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:why is this happen?

    [Originally posted by ifah]

    thanks Tom !

    i have changed my coding here and it did not show any error:

    ------
    dim sStartDate˙ as date
    dim sEndDate˙ ˙ as date

    sStartDate˙ =DTPicker1.Value
    sEndDate˙ =˙ DTPicker2.Value

    sSql = "SELECT * FROM tblMyBooks WHERE BookType= '" & Combo1.Text & "'" & "and "
    sSql = sSql & " bookDate BETWEEN '" & sStartDate & "' And '" & sEndDate & "'"
    sSql = sSql & " Order by booktype"

    -------

    but the report still did not show the data that i want . it still show all the data from the table.

    then i tried to call my DataReport like this. i put this at the end of my coding :

    DataReport1.DataSource = sSql
    DataReport1.Show

    i got an error here.

    how to call my DataReport so it will show data like in my SQL?

    thanks in advance!

  4. #4
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:why is this happen?

    [Originally posted by Tom Lewandowski]

    I don't know what the DataReport1 object is.˙ I am not familiar with that control.˙ I create recordsets using the DAO OpenRecordset method.

    I am wondering why you changed the '#' before and after your dates to a single quote.˙ If you use the '#' before and after a date string, the database driver should return the correct records.˙ Copy and paste the following code into you program and see if it works.

    sSql = "SELECT * FROM tblMyBooks WHERE BookType= '" & Combo1.Text & "' and "
    sSql = sSql & " bookDate BETWEEN #" & str(sStartDate.value) & "# And #" & str(sEndDate.value) & "#"
    sSql = sSql & " Order by booktype"

    Let me know if this works.˙ Also, if it doesn't work, it would help to know the text of the error message returned.

    Tom

  5. #5
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Re:Re:Re:why is this happen?

    [Originally posted by ifah]

    thanks for teying to help me Tom!

    sorry for the late reply. my Computer making trouble. i'll try it now!

  6. #6
    Join Date
    Aug 2004
    Posts
    43,023

    Tom , there is an error

    [Originally posted by ifah]

    Tom i have tried your suggestion and i got error "Invalid Qualifier"

    the yellow color highlight at the str(sStartDate)

    how to do then?
    i have tried it all day but i still get an error.

    thanks in advance!

  7. #7
    Join Date
    Aug 2004
    Posts
    43,023

    Re:Tom , there is an error

    [Originally posted by Tom Lewandowski]

    The problem is you were using a datepicker control in your first example then it looks like you changed it to a string.˙ I assumed the start and end dates were still date pickers.˙ The problem is the sStartDate is not an object so sStartDate.Value will produce and error.˙ Try the following instead.

    sSql = "SELECT * FROM tblMyBooks WHERE BookType= '" & Combo1.Text & "' and "
    sSql = sSql & " bookDate BETWEEN #" & sStartDate & "# And #" & sEndDate & "#"
    sSql = sSql & " Order by booktype"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center