Another Runtime 3075 =(

  • Thread starter Thread starter DeDBlanK
  • Start date Start date
D

DeDBlanK

Green VBA user here. I know there is a lot of the 'Operation not
found' issues out there and I have tried several solutions to get this
simple key check to work to no avail.
Here's the code:

txtVendNumb = "US" & strVendNumb
Set Db = CurrentDb()
strSQL = "SELECT count(*) FROM tblVendor WHERE " &
strVendorQAD & " = " & txtVendNumb
'MsgBox to see the SQL statement
MsgBox strSQL
Set Rs = CurrentDb.OpenRecordset(strSQL)

If Rs.RecordCount > 0 Then ...

txtVendNumb is text and so is strVendorQAD
when I run the code the strSQL is giving me this in the MsgBox
SELECT count(*) FROM tblVendor WHERE = US1234 (the 1234 is what was
enter from the prompt) and it throws the Run-Time Error of 3075
(Missing operator).
I also tried the ... WHERE '" & strVendorQAD & '" = " &
txtVendNumb for the strSQL and the same issue is showing in the
MsgBox of no strVendorQAD in the SQL, however, it does complete the
COUNT but with incorrect results. It comes back stating (no matter
what's entered it has a result of 1 in the Rs.RecordCount because of
matching nothing I presume).
Any help would be greatly appreciated.
 
Is strVendorQAD the name of the field in the table? If so, then use

strSQL = "SELECT count(*) FROM tblVendor " & _
"WHERE strVendorQAD = '" & txtVendNumb & "'"

Exagerated for clarity, that's

strSQL = "SELECT count(*) FROM tblVendor " & _
"WHERE strVendorQAD = ' " & txtVendNumb & " ' "

If strVendorQAD is a variable that holds the name of the field in the table,
then use

strSQL = "SELECT count(*) FROM tblVendor " & _
"WHERE [" & strVendorQAD & "] = '" & txtVendNumb & "'"

(The reason for the square brackets is in case the field name being passed
includes special characters, such as spaces)
 
Is strVendorQAD the name of the field in the table? If so, then use

    strSQL = "SELECT count(*) FROM tblVendor " & _
      "WHERE strVendorQAD  = '" & txtVendNumb & "'"

Exagerated for clarity, that's

    strSQL = "SELECT count(*) FROM tblVendor " & _
      "WHERE strVendorQAD  = ' " & txtVendNumb & " ' "

If strVendorQAD is a variable that holds the name of the field in the table,
then use

    strSQL = "SELECT count(*) FROM tblVendor " & _
      "WHERE [" & strVendorQAD & "]  = '" & txtVendNumb & "'"

(The reason for the square brackets is in case the field name being passed
includes special characters, such as spaces)

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)




Green VBA user here.  I know there is a lot of the 'Operation not
found' issues out there and I have tried several solutions to get this
simple key check to work to no avail.
Here's the code:
Click to expand...
txtVendNumb = "US" & strVendNumb
               Set Db = CurrentDb()
                   strSQL = "SELECT count(*) FROM tblVendor WHERE " &
strVendorQAD & " = " & txtVendNumb
                   'MsgBox to see the SQL statement
                   MsgBox strSQL
                   Set Rs = CurrentDb.OpenRecordset(strSQL)
Click to expand...
                   If Rs.RecordCount > 0 Then ...
Click to expand...
txtVendNumb is text and so is strVendorQAD
when I run the code the strSQL is giving me this in the MsgBox
SELECT count(*) FROM tblVendor WHERE  = US1234 (the 1234 is what was
enter from the prompt) and it throws the Run-Time Error of 3075
(Missing operator).
I also tried the   ... WHERE '" & strVendorQAD & '" = " &
txtVendNumb   for the strSQL and the same issue is showing in the
MsgBox of no strVendorQAD in the SQL, however, it does complete the
COUNT but with incorrect results.  It comes back stating (no matter
what's entered it has a result of 1 in the Rs.RecordCount because of
matching nothing I presume).
Any help would be greatly appreciated.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Mr. Steele, thanks for your quick response. I have read many of your
postings.

strVendorQAD is a field in tblVendor

When I used:
strSQL = "SELECT count(*) FROM tblVendor WHERE strVendorQAD = '" &
txtVendNumb & "'"
But now the Rs.Recordcount = 1 even if there isn't or is a record that
matches.
So I am curious if it is actually doing what it's suppose to. It
looks correct, but isn't working.

The bracket solution throws a:
3061 Run Time error: too few parameters. Expected 1.
 
Your SQL returns a count of the number of rows. Whether or not there are any
rows in the table that match the criteria, the recordset will always have
one row with one field (eqaul to 0 if there are no matching rows, or a count
of the number of rows if there are matching rows.)

If what you're interested in is the number of rows, rather than If
Rs.RecordCount > 0 Then, use If Rs.Fields(0) > 0 Then

I assume that when you talk of "the bracket solution", you're referring to
the second SQL statement I posted:

strSQL = "SELECT count(*) FROM tblVendor " & _
"WHERE [" & strVendorQAD & "] = '" & txtVendNumb & "'"

As I said, that assumed that strVendorQAD was a variable containing the name
of the field to which the value in txtVendNumb was to be compared. If you
didn't have the name of the field in variable strVendorQAD, the resultant
SQL would have contained WHERE [] = 'US1234'. Since Access had no idea what
[] is, you got that error message.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Is strVendorQAD the name of the field in the table? If so, then use

strSQL = "SELECT count(*) FROM tblVendor " & _
"WHERE strVendorQAD = '" & txtVendNumb & "'"

Exagerated for clarity, that's

strSQL = "SELECT count(*) FROM tblVendor " & _
"WHERE strVendorQAD = ' " & txtVendNumb & " ' "

If strVendorQAD is a variable that holds the name of the field in the
table,
then use

strSQL = "SELECT count(*) FROM tblVendor " & _
"WHERE [" & strVendorQAD & "] = '" & txtVendNumb & "'"

(The reason for the square brackets is in case the field name being passed
includes special characters, such as spaces)

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)




Green VBA user here. I know there is a lot of the 'Operation not
found' issues out there and I have tried several solutions to get this
simple key check to work to no avail.
Here's the code:
Click to expand...
txtVendNumb = "US" & strVendNumb
Set Db = CurrentDb()
strSQL = "SELECT count(*) FROM tblVendor WHERE " &
strVendorQAD & " = " & txtVendNumb
'MsgBox to see the SQL statement
MsgBox strSQL
Set Rs = CurrentDb.OpenRecordset(strSQL)
Click to expand...
If Rs.RecordCount > 0 Then ...
Click to expand...
txtVendNumb is text and so is strVendorQAD
when I run the code the strSQL is giving me this in the MsgBox
SELECT count(*) FROM tblVendor WHERE = US1234 (the 1234 is what was
enter from the prompt) and it throws the Run-Time Error of 3075
(Missing operator).
I also tried the ... WHERE '" & strVendorQAD & '" = " &
txtVendNumb for the strSQL and the same issue is showing in the
MsgBox of no strVendorQAD in the SQL, however, it does complete the
COUNT but with incorrect results. It comes back stating (no matter
what's entered it has a result of 1 in the Rs.RecordCount because of
matching nothing I presume).
Any help would be greatly appreciated.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Mr. Steele, thanks for your quick response. I have read many of your
postings.

strVendorQAD is a field in tblVendor

When I used:
strSQL = "SELECT count(*) FROM tblVendor WHERE strVendorQAD = '" &
txtVendNumb & "'"
But now the Rs.Recordcount = 1 even if there isn't or is a record that
matches.
So I am curious if it is actually doing what it's suppose to. It
looks correct, but isn't working.

The bracket solution throws a:
3061 Run Time error: too few parameters. Expected 1.
 
Your SQL returns a count of the number of rows. Whether or not there are any
rows in the table that match the criteria, the recordset will always have
one row with one field (eqaul to 0 if there are no matching rows, or a count
of the number of rows if there are matching rows.)

If what you're interested in is the number of rows, rather than  If
Rs.RecordCount > 0 Then, use If Rs.Fields(0) > 0 Then

I assume that when you talk of "the bracket solution", you're referring to
the second SQL statement I posted:

strSQL = "SELECT count(*) FROM tblVendor " & _
"WHERE [" & strVendorQAD & "] = '" & txtVendNumb & "'"

As I said, that assumed that strVendorQAD was a variable containing the name
of the field to which the value in txtVendNumb was to be compared. If you
didn't have the name of the field in variable strVendorQAD, the resultant
SQL would have contained WHERE [] = 'US1234'. Since Access had no idea what
[] is, you got that error message.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)


Is strVendorQAD the name of the field in the table? If so, then use
Click to expand...
strSQL = "SELECT count(*) FROM tblVendor " & _
"WHERE strVendorQAD = '" & txtVendNumb & "'"
Click to expand...
Exagerated for clarity, that's
Click to expand...
strSQL = "SELECT count(*) FROM tblVendor " & _
"WHERE strVendorQAD = ' " & txtVendNumb & " ' "
Click to expand...
If strVendorQAD is a variable that holds the name of the field in the
table,
then use
Click to expand...
strSQL = "SELECT count(*) FROM tblVendor " & _
"WHERE [" & strVendorQAD & "] = '" & txtVendNumb & "'"
Click to expand...
(The reason for the square brackets is in case the field name being passed
includes special characters, such as spaces)
Click to expand...
- Show quoted text -
Click to expand...

Mr. Steele, thanks for your quick response.  I have read many of your
postings.

strVendorQAD is a field in tblVendor

When I used:
strSQL = "SELECT count(*) FROM tblVendor WHERE strVendorQAD  = '" &
txtVendNumb & "'"
But now the Rs.Recordcount = 1 even if there isn't or is a record that
matches.
So I am curious if it is actually doing what it's suppose to.  It
looks correct, but isn't working.

The bracket solution throws a:
3061 Run Time error: too few parameters. Expected 1.- Hide quoted text -

- Show quoted text -
Click to expand...

Thank you very much for the solution. I wasn't aware of the
Rs.Field(0).
Just to make sure that I understand your explaination. Even if the
SQL doesn't find a matching record, the result of the RecordCount will
still be = 1?
 
As I said, your recordset will always have one record in it, whether there's
a thousand records in tblVendor with that vendor number, or zero. Your SQL
returns a count, not each of the matching rows. If there are no matching
records, that count will be 0.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


you very much for the solution. I wasn't
aware of the
Rs.Field(0).
Just to make sure that I understand your explaination. Even if the
SQL doesn't find a matching record, the result of the RecordCount will
still be = 1?
 
As I said, your recordset will always have one record in it, whether there's
a thousand records in tblVendor with that vendor number, or zero. Your SQL
returns a count, not each of the matching rows. If there are no matching
records, that count will be 0.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no e-mails, please!)


you very much for the solution.  I wasn't
aware of the
Rs.Field(0).
Just to make sure that I understand your explaination.  Even if the
SQL doesn't find a matching record, the result of the RecordCount will
still be = 1?
Click to expand...

Thanks for the further clarification, and Thanks again for all the
great help!
 
Back
Top