Query works perfectly on Query screen but Error on VBA ??

[Acie]

Here is my problem:

I'm building a Select SQL statement and storing it into a
string variable. I'm including the table name as a
variable, and when I display the string via a msgbox and
when i store in into a textbox, it looks great. (I copied
into a text box, so that I can copy the evaluated select
stmt(CTL-V) and paste it on the Access screen to test it.

Here is the weird part. The SQL stmt works perfectly on
the SQL screen, however within the VBA code it gives me a
Type Mismatch Run-Time Error '13'. I am puzzled. I
checked the join fields and they all match(data type).

FYI The string has abut two parameters that are embedded
in several places.. I use Chr(34) as the quotes..,
etc..againwhen the variable is displayed it looks perfect,
and I test it via copying it and it works fine..

Oh before I had the table name hardcoded and it worked
fine in the VBA code. After I converted teh sql to utilize
variables..it stopped working.

Any ideas/thoughts?

thanks,
Acie

 

[Ken Snell]

Post the SQL statement, please.

 

[Guest]

OK.

Here is the actual SQL as it is evaluated by the VBA code.

SELECT b1mahoa.ID from b1mahoa where (((b1mahoa.ID)=
(Select MIN(b1mahoa.ID) From (b1mahoa inner join Resources
on b1mahoa.[Project Code] = Resources.[Project Code])
inner join Users on Resources.AnalystID = Users.AnalystID
where [First Name] like "*Hall*" or [Last Name]
like "*Hall*")));

The actual string is:
Dim str, quote as string
Dim vardata as variant
quote = Chr(34)

str = "SELECT " & userid & ".[ID] FROM " & userid & " "
str = str & "WHERE (((" & userid & ".[ID])=(SELECT MIN("
& userid & ".ID) "
str = str & "FROM (" & userid & " INNER JOIN Resources
ON " & userid & ".[Project Code] = Resources.[Project
Code]) INNER JOIN Users ON Resources.AnalystID =
Users.AnalystID where [First Name] like " & quote & "*" &
txtSearchString & "*" & quote & " or [Last name] like " &
quote & "*" & txtSearchString & "*" & quote & ")));"
str = "SELECT b1mahoa.ID from b1mahoa where (((b1mahoa.ID)=
(Select MIN(b1mahoa.ID) From (b1mahoa inner join Resources
on b1mahoa.[Project Code] = Resources.[Project Code])
inner join Users on Resources.AnalystID = Users.AnalystID
where [First Name] like " & quote & "*" & txtSearchString
& "*" & quote & " or [Last Name] like " & quote & "*" &
txtSearchString & "*" & quote & ")));"

Followed by:

Set dbs = CurrentDb()

'rst,db properly defined & declared..

Set rst = dbs.OpenRecordset(str,
dbOpenSnapshot, dbforward)
If Not rst.BOF And Not rst.EOF Then
vardata = rst.GetRows(1)
End If

Again, when I ran it in VBA, I get the "Type Mismatch
Error '13'

I take the evaluated str string and copy it to an textbox.
I copy & paste it onto the Query screen, and run it and it
gives me back one record of the ID.-Works great there.

Any help, advice would be greatly appreciated.

thanks,
Acie


-----

 

[John Spencer (MVP)]

Pardon me, but
Why are you sure that the Rst and dbs are properly defined? Are they defined as

DIM rst as DAO.RecordSet, dbs as DAO.Database

NOTE the inclusion of the library reference to DAO. In other words, your error
may not be caused by the SQL string itself. If you do dim your rst and db as
suggested and then get an error about user defined types, then you probably
don't have a reference to the DAO library.

OK.

Here is the actual SQL as it is evaluated by the VBA code.

SELECT b1mahoa.ID from b1mahoa where (((b1mahoa.ID)=
(Select MIN(b1mahoa.ID) From (b1mahoa inner join Resources
on b1mahoa.[Project Code] = Resources.[Project Code])
inner join Users on Resources.AnalystID = Users.AnalystID
where [First Name] like "*Hall*" or [Last Name]
like "*Hall*")));

The actual string is:
Dim str, quote as string
Dim vardata as variant
quote = Chr(34)

str = "SELECT " & userid & ".[ID] FROM " & userid & " "
str = str & "WHERE (((" & userid & ".[ID])=(SELECT MIN("
& userid & ".ID) "
str = str & "FROM (" & userid & " INNER JOIN Resources
ON " & userid & ".[Project Code] = Resources.[Project
Code]) INNER JOIN Users ON Resources.AnalystID =
Users.AnalystID where [First Name] like " & quote & "*" &
txtSearchString & "*" & quote & " or [Last name] like " &
quote & "*" & txtSearchString & "*" & quote & ")));"
str = "SELECT b1mahoa.ID from b1mahoa where (((b1mahoa.ID)=
(Select MIN(b1mahoa.ID) From (b1mahoa inner join Resources
on b1mahoa.[Project Code] = Resources.[Project Code])
inner join Users on Resources.AnalystID = Users.AnalystID
where [First Name] like " & quote & "*" & txtSearchString
& "*" & quote & " or [Last Name] like " & quote & "*" &
txtSearchString & "*" & quote & ")));"

Followed by:

Set dbs = CurrentDb()

'rst,db properly defined & declared..

Set rst = dbs.OpenRecordset(str,
dbOpenSnapshot, dbforward)
If Not rst.BOF And Not rst.EOF Then
vardata = rst.GetRows(1)
End If

Again, when I ran it in VBA, I get the "Type Mismatch
Error '13'

I take the evaluated str string and copy it to an textbox.
I copy & paste it onto the Query screen, and run it and it
gives me back one record of the ID.-Works great there.

Any help, advice would be greatly appreciated.

thanks,
Acie

-----

Post the SQL statement, please.

--
Ken Snell
<MS ACCESS MVP>




.