Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

Count records using a wildcard...

I am trying to setup a Count routine that will use a wildcard to find all records in a certain field within a certain table that starts with a specified number (comes from a text box) but I don't think the wildcard is working because no matter what I enter it doesn't find a Count (always 0). Any idea what I might be doing wrong now?

thanks in advance for any help that you can give me with this.

Also tried...
cmd.Parameters.AddWithValue("@CurrDepart", OleDbType.VarChar).Value = Me.txtCurGL.Text.ToString & "%"
'Check to make sure the Current Department already exisits
            Dim Count2 As Integer = 0
            Using Connection As New OleDbConnection _
             ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Comet 631 Databases\Converted to 2003\C022008.mdb")
                Dim cmd As OleDbCommand = New OleDbCommand _
                ("Select Count(ActAcct) as ExistingRecords From [SHACT] where (ActAcct)= @CurrDepart", Connection)
                cmd.Parameters.AddWithValue("@CurrDepart", Me.txtCurGL.Text.ToString & "%")
                 Connection.Open()
                Count2 = CInt(cmd.ExecuteScalar)
                ("@CurrDepart",me.txtCurGL.text.Tostring & "%")end withconnection.open()
            End Using
            'if the Current Department doesn't exisit then throw message and exit sub
            If Count2 <= 0 Then
                MessageBox.Show("The GL Department " & Me.txtCurGL.Text.ToString & " does not exisit.", _
                "Copy Department", MessageBoxButtons.OK)
                Me.txtCurGL.Focus()
                Me.txtCurGL.SelectAll()
                Return
            End If

Open in new window

0
Boon01
Asked:
Boon01
  • 2
1 Solution
 
nexusnationCommented:
You can do:

"Select Count(ActAcct) as ExistingRecords From [SHACT] where (ActAcct) Like @CurrDepart"

And the "%" might need to be "*". (Not sure; I don't recall the syntax).

You might also need single quotes. I'd eliminate the parameter and try this directly:

"Select Count(ActAcct) as ExistingRecords From [SHACT] where (ActAcct) Like '" & Me.txtCurGL.Text & "%'", Connection)
0
 
Boon01Author Commented:
Thank you very much nexusnation, "Select Count(ActAcct) as ExistingRecords From [SHACT] where (ActAcct) Like '" & Me.txtCurGL.Text & "%'", Connection) was the answer. I had tried this but had a typo in it so it gave an error and I tried the Paameter instead. But this worked like a charm, thanks again!!
0
 
nexusnationCommented:
Glad I could help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now