how view specific date of entry from database

[2mhzbrain]

WHERE Category = '" + CategorySorter.Text + "' AND DateAdded = '" + DateValue("DateEnd.Text") + "' "

thats my query and its not working, if the user input a date(like this 12/25/12) in DateEnd.Text, i want to confirm if it is existing on a database

[Ancient Dragon]

Depends on the database, you need to check with the database manager (maybe its you) to find out what format the database expects. MS-Addess wants dd-mm--yyyy format while others may want dd/mm/yyyy or yyyy/mm/dd or yyyy/dd/mm or something else. Before sending to the database I suppose you need to validate the text in DateEnd.Text is a valid date and in the correct format.

[2mhzbrain]

The database date format is 12/31/2012, i inputed almost all kinds of date inside DateEnd.Text, still "data type mismatch error"
sir here's the code:

Sub FilteredQuery()
cnDDR.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\parts.mdb;"

    With cmd
        .ActiveConnection = cnDDR
        .CommandType = adCmdText
        .CommandText = "Select Category, DateAdded FROM comparts WHERE Category = '" + Category.Text + "' AND DateAdded = '" + DateValue(DateEnd.Text) + "' "
        .Execute
    End With

    With rsDDR
        .ActiveConnection = cnDDR
        .CursorLocation = adUseClient
        .Open cmd
    End With

End Sub

[2mhzbrain]

I used this code from msdn to populate the report dynamically, so i can view specific date which will match from user input

    FilteredQuery

    Dim q As Integer
    Dim intCtrl As Integer
    Dim x As Integer
    Dim z As Integer

    x = 0
    q = 0
    z = 0

    With DynamicDR
        .Hide
        Set .DataSource = rsDDR
        .DataMember = ""

        With .Sections("Section1").Controls

            For intCtrl = 1 To .Count

                If TypeOf .Item(intCtrl) Is RptLabel Then
                    .Item(intCtrl).Caption = rsDDR.Fields(q).Name & " :"
                    q = q + 1
                End If

                If TypeOf .Item(intCtrl) Is RptTextBox Then
                    .Item(intCtrl).DataMember = ""
                    .Item(intCtrl).DataField = rsDDR(z).Name
                    z = z + 1
                End If

        Next intCtrl

        End With

    .Refresh
    .Show

    End With

[Ancient Dragon]

What database are you using? It should have some sort of administrator panel where you can manually enter and execute sql statements. If it has one then use it to execute the sql you are trying to create. Then when you find one that works change your vb program accordingly.

[2mhzbrain]

im using access (parts.mdb) its compatible with access 2003, i used myphpadmin before but for just a tutorial, and it has ssection which we can perform sql queries,

[2mhzbrain]

when i created the database (parts.mdb), the DateAdded created as a "Date" data type

[2mhzbrain]

its working now, i used this:

"Select ItemCode, ItemName, Category, Description, Supplier, ItemPrice, InStock, OnOrder, DateAdded FROM comparts WHERE Category = '" + CategorySorter.Text + "' AND DateAdded >= #" & DateEnd.Text & "# AND DateAdded <= #" & DateStart.Text & "#"

[tinstaafl]

If the date is 'Date' data type, then try converting the string to 'Date' type before you submit it. You can use CDate function to convert it.

[2mhzbrain]

Thanks, it is working now, i can now view the data according to category and date

[AndreRet]

Please mark as solved if it is working fine now, thank you.