Access Query Problem

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

Good Morning,

Got some good advice yesterday, but still running into issues. I am trying to query an Access db once a user selects a month/yr from a dropdown list.

Below is summary of my code:

Dim CurrentStrMth as String
Dim CurrentEndMth as String

if drpdate.selecteditem.text = "JANUARY 04" then
CurrentStrMth = "#1/1/2004#"
CurrentEndMth = "#1/31/2004#"

When I run the query below without passing any variables it works fine:
cmdSelect1 = New OleDbCommand("SELECT Count(*) from db WHERE [Submit Date/Time]>=#1/1/2004# and [Submit Date/Time]<=#1/31/2004#",dbconnection)

However, when I try to incorporate the string variables, as noted below, into the query I get "Character constant must contain exactly one character". I can't find anything on this on the web!

cmdSelect1 = New OleDbCommand("Select Count(*) from db where [submit date/time] >= '"CurrentStrMth"' and [submit date/time] <= '"CurrentEndMth"'", dbconnection)

If someone could assist me further with this issue, it would be greatly appreciated?

Thanks in advance!
MKC
 
I think your commandtext is using the literal CurrentStrMth and CurrentEndMth isn't it? If I'm reading this right there's no concatenation so those literals are in there. I'd recommend using Parameters anyway:

("Select Count(*) from db where [submit date/time] >= ? and [submit date/time] <=?", dbconnection)
cmdSelect1.Parameters.Add(CurrentStrMth);
cmdSelect1.Parameters.Add(CurrentEndMth);

HTH,

Bill

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
Good Morning,

Got some good advice yesterday, but still running into issues. I am trying to query an Access db once a user selects a month/yr from a dropdown list.

Below is summary of my code:

Dim CurrentStrMth as String
Dim CurrentEndMth as String

if drpdate.selecteditem.text = "JANUARY 04" then
CurrentStrMth = "#1/1/2004#"
CurrentEndMth = "#1/31/2004#"

When I run the query below without passing any variables it works fine:
cmdSelect1 = New OleDbCommand("SELECT Count(*) from db WHERE [Submit Date/Time]>=#1/1/2004# and [Submit Date/Time]<=#1/31/2004#",dbconnection)

However, when I try to incorporate the string variables, as noted below, into the query I get "Character constant must contain exactly one character". I can't find anything on this on the web!

cmdSelect1 = New OleDbCommand("Select Count(*) from db where [submit date/time] >= '"CurrentStrMth"' and [submit date/time] <= '"CurrentEndMth"'", dbconnection)

If someone could assist me further with this issue, it would be greatly appreciated?

Thanks in advance!
MKC
 
Back
Top