Data Type Mismatch in DAO

[Shlomo]

The SQL in the following code works on Query Analyzer but in VB it gives
over a data mismatch type. Actually if I use aliases i get a syntax error on
the FROM clause. ( this did not happen in Query Analyzer. If I use a simple
SQL Statement it works, however.

It breaks up at the end when I try to open up the Recordset.

Dim dbs As Database, qdf As QueryDef
Dim strConnect As String,
Dim strSQL As String

strConnect =
"ODBC;DSN=*****;UID=*******;PWD=********!;DATABASE=*********;Address=***.***
*.****;1433"

Set dbs = DBEngine.Workspaces(0).OpenDatabase(*******, _
False, False, strConnect)

Set qdf = dbs.CreateQueryDef("")
qdf.Connect = strConnect

strSQL = "select artrans.CustomerID, name, Transdate, ArAmount "
strSQL = strSQL + "from artrans inner join customer "
strSQL = strSQL + "on customer.customerID = artrans.customerID "
strSQL = strSQL + "where customer.customerID = 12196 "
strSQL = strSQL + "AND Transdate between '08/01/00' and '09/01/01' "
strSQL = strSQL + "AND ArAmount >= 0"
qdf.SQL = strSQL
qdf.ReturnsRecords = True



Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)

_____________

It seems that using the Where clause kills it cause even the following does
not work:

strSQL = "select top 100 name from customer where customerID = 12235"

whereas the same Query minus the where part of it goes well.

Thanks For your help!!

[huibert]

>strSQL = strSQL + "AND Transdate between '08/01/00' and '09/01/01' "

1) Single Quotes are used for strings, not for dates! your string should
look like this:
strSQL = strSQL & "AND Transdate between #08/01/00# and #09/01/01# "
DAO (Jet Engine) uses pound signs around dates. Note that the date is always
mm/dd/yyyy (i think) regardless of your regional settings!

2) Don't use '+' for string concatenation. It is bad coding practice and
will not compile in future versions of VB. It can lead to subtle bugs if
you mix variants and numeric data in your concatenation

HTH
huiberrt

[Shlomo]

Scratch that, you a genius! I tried putting single quotes around
CustomerID - (I should really check to see what it is for sure! we know what
what assuming makes! -I didn't design it) and it worked. well, well, well,
Thanks - now i just have tomake sure it works with the joins.

You can't do aloasing with VB DAO?
"huibert" <hvandeursen@wanadoo.nl> wrote in message
news:3b98814c$1@news.devx.com...
>
> >strSQL = strSQL + "AND Transdate between '08/01/00' and '09/01/01' "
>
> 1) Single Quotes are used for strings, not for dates! your string should
> look like this:
> strSQL = strSQL & "AND Transdate between #08/01/00# and #09/01/01# "
> DAO (Jet Engine) uses pound signs around dates. Note that the date is
always
> mm/dd/yyyy (i think) regardless of your regional settings!
>
> 2) Don't use '+' for string concatenation. It is bad coding practice and
> will not compile in future versions of VB. It can lead to subtle bugs if
> you mix variants and numeric data in your concatenation
>
> HTH
> huiberrt