4
Contributors
10
Replies
15
Views
4 Years
Discussion Span
Last Post by AndreRet
0

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.

Edited by Ancient Dragon

0

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
0

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
0

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.

0

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,

0

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

0

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 & "#"
0

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.

0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.