Type mismatch error, SQL and Recordset

[aland]

Hi, I'm hoping someone can help me with this code. I'm getting a 'Type
mismatch' error, and I'm not sure why. The SQL works fine in SQL view,
so I'm not sure if that's the problem or not.
This is a dumbed down version of what I'm trying to do so it looks like
more like an academic problem.
All I want at this stage is to get the highest value of table.id into a
variable.

Dim strSQL As String
Dim RS As Recordset
Dim strFileNum As String

strSQL = "SELECT table.id FROM [table] WHERE (((table.id) Like
""9001*""));"
Set RS = CurrentDb().OpenRecordset(strSQL)

With RS
.MoveFirst
strFileNum = !id
End With

lblFilenum.Caption = strFileNum

'Close the recordset
RS.Close
The full SQL statement looks like
"
SELECT table.id, table.a, Format(S([id]),""000"") AS Sort
FROM [table]
WHERE (((table.id) Like [Forms]![frm]![txtFileNum]+""*""))
ORDER BY Format(S([id]),""000"");
"
But I've left it out because it's long and confusing (and dosn't seem
to affect the problem at this stage.

TIA

[Jean]

Hi aland

Try substituting the wildcard character "*" in your SQL statement with
a "%"
I had this problem, it seems that when writing a SQL statement in a
Access recordset (ADODB or DAO), you have to use the % instead of the
normal *, which explains why it works in your query's SQL view but not
in your code.

Hope that helps!

J

[BillC]

You didn't say where your code is failing, but I suspect that in the
following line,

strFileNum = !id
the field "id" is numeric.

Bill

aland wrote: Hi, I'm hoping someone can help me with this code. I'm getting a 'Type mismatch' error, and I'm not sure why. The SQL works fine in SQL view, so I'm not sure if that's the problem or not.
This is a dumbed down version of what I'm trying to do so it looks like more like an academic problem.
All I want at this stage is to get the highest value of table.id into a variable.

Dim strSQL As String
Dim RS As Recordset
Dim strFileNum As String

strSQL = "SELECT table.id FROM [table] WHERE (((table.id) Like
""9001*""));"
Set RS = CurrentDb().OpenRecordset(strSQL)

With RS
.MoveFirst
strFileNum = !id
End With

lblFilenum.Caption = strFileNum

'Close the recordset
RS.Close
The full SQL statement looks like
"
SELECT table.id, table.a, Format(S([id]),""000"") AS Sort
FROM [table]
WHERE (((table.id) Like [Forms]![frm]![txtFileNum]+""*""))
ORDER BY Format(S([id]),""000"");
"
But I've left it out because it's long and confusing (and dosn't seem
to affect the problem at this stage.

TIA

[aland]

It's actually text. I've just made a copy of the original database and
made all the field names short so that my code is less messy. So it's a
misleading field name in that it isn't a primary key or anything.
Replacing * with % didn't seem to do much either. I'm sure I'm doing
some stupid and haven't realised it yet. I've used other queries in VBA
using * and it worked ok, so I'm not sure if that is actually causing a
problem.

[Darryl Kerkeslager]

On 8 Feb 2005 07:17:25 -0800, aland <al*****@gmail.com> wrote:

It's actually text. I've just made a copy of the original database and
made all the field names short so that my code is less messy. So it's a
misleading field name in that it isn't a primary key or anything.
Replacing * with % didn't seem to do much either. I'm sure I'm doing
some stupid and haven't realised it yet. I've used other queries in VBA
using * and it worked ok, so I'm not sure if that is actually causing a
problem.

Well, then, it's either that your using currentDB with what has defaulted
to an ADODB recordset because of the version of Access you are using (2000
- XP, I presume), or it is because you have used "table" as the name of a
table, in the line

"SELECT table.id FROM [table] WHERE (((table.id) Like
""9001*""));"

You cannot use the name table. More (?) importantly, you should always
fully qualify the name of data access objects, ADODB or DAO, since the
type of objects cannot be determined in your code without the fully
qualified reference.

So, use either:

Dim RS As DAO.Recordset

in which case your code should work fine ( as you are using a DAO method
later), unless you do not have a reference to DAO, or use

Dim RS As ADODB.Recordset

in which case your code will fail, because

Set RS = CurrentDb().OpenRecordset(strSQL)

is valid DAO, but not valid ADO.
Darryl Kerkeslager

[Bob Quintal]

"aland" <al*****@gmail.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

Hi, I'm hoping someone can help me with this code. I'm getting
a 'Type mismatch' error, and I'm not sure why. The SQL works
fine in SQL view, so I'm not sure if that's the problem or
not. This is a dumbed down version of what I'm trying to do so
it looks like more like an academic problem.
All I want at this stage is to get the highest value of
table.id into a variable.

Dim strSQL As String
Dim RS As Recordset
Dim strFileNum As String

strSQL = "SELECT table.id FROM [table] WHERE (((table.id)
Like
""9001*""));"
Set RS = CurrentDb().OpenRecordset(strSQL)

With RS
.MoveFirst
strFileNum = !id
End With

lblFilenum.Caption = strFileNum

'Close the recordset
RS.Close
The full SQL statement looks like
"
SELECT table.id, table.a, Format(S([id]),""000"") AS Sort
FROM [table]
WHERE (((table.id) Like [Forms]![frm]![txtFileNum]+""*""))
ORDER BY Format(S([id]),""000"");
"
But I've left it out because it's long and confusing (and
dosn't seem to affect the problem at this stage.

TIA

Format(S([id]),""000"") seems suspicious.

--
Bob Quintal

PA is y I've altered my email address.

[Darryl Kerkeslager]

On 8 Feb 2005 07:17:25 -0800, aland <al*****@gmail.com> wrote:

It's actually text. I've just made a copy of the original database and
made all the field names short so that my code is less messy. So it's a
misleading field name in that it isn't a primary key or anything.
Replacing * with % didn't seem to do much either. I'm sure I'm doing
some stupid and haven't realised it yet. I've used other queries in VBA
using * and it worked ok, so I'm not sure if that is actually causing a
problem.

Well, then, it's either that your using currentDB with what has defaulted
to an ADODB recordset because of the version of Access you are using
(2000 - XP, I presume), or it is because you have used "table" as the name
of a table, in the line

"SELECT table.id FROM [table] WHERE (((table.id) Like
""9001*""));"

You cannot use the name table. More (?) importantly, you should always
fully qualify the name of data access objects, ADODB or DAO, since the
type of objects cannot be determined in your code without the fully
qualified reference.

So, use either:

Dim RS As DAO.Recordset

in which case your code should work fine ( as you are using a DAO method
later), unless you do not have a reference to DAO, or use

Dim RS As ADODB.Recordset

in which case your code will fail, because

Set RS = CurrentDb().OpenRecordset(strSQL)

is valid DAO, but not valid ADO.
Darryl Kerkeslager

[aland]

Thanks everyone for your replies, they have been very helpful and
educational (I'm still pretty new to this Access game).
Daryl is on the money with the DAO/ADODB, I started looking into
changing to ADODB but gave up very quickly. I found the library so I
can use DAO (Microsoft DAO 3.6) so things seem to be going well. Here
is the code atm which works well (although there is still a while to go
before I'm finished what I'm doing)
Dim strSQL As String
Dim RS As DAO.Recordset
Dim strFileNum As String

strSQL = "SELECT tbl_files.id FROM [tbl_files] WHERE
(((tbl_files.id) Like ""9001*""));"

Set RS = CurrentDb().OpenRecordset(strSQL)
With RS
.MoveFirst
strFileNum = !id
End With

lblFilenum.Caption = strFileNum

Bob, I'm not sure why "Format(S([id]),""000"") seems suspicious". S is
a function I have written to strip certain characters out of a string.
To elaborate:
id contains a string representing file numbers, in the format of
9999/[X]999[A-Z], S(string) will strip off everything leaving the
second numeric portion. This is so I can sort them properly (eg, the
numbers are stored as 9001/1, 9001/1A, 9001/10, 9001/253, etc) then
format just puts in leading zero's if necessary.

Thanks!

[aland]

Well maybe I jumped the gun on the "Format(S([id]),""000"")" piece as
it doesn't want to work at all (Item not found in this collection). Is
it possible to use functions in SQL queries when used in VBA and DAO?
Perhaps I should be doing this formating in VBA rather than the SQL
query?