sql stmt issue
 

I'm trying to update a field (NOTES) in my Access database with 2 sql
statements. The statements are as follows"

Sql = "UPDATE [Data_Current] set [NOTES] = """ & tmpcom & """ where [GP] =
""" & vGroup & """ AND [BR] = """ & Cells(r, 2) & """ AND [CUST_NO] = """ &
Cells(r, 3) & """ AND [SOURCE] = """ & Cells(r, 6) & """ AND [DATE] = """ &
tmpAll & """ AND [BALANCE] = '" & vBal & "'"

Call Recordset.Open(Sql, ConnectionString, adOpenForwardOnly,
adLockReadOnly, CommandTypeEnum.adCmdText)

With these statements, I'm getting a "data type mismatch in criteria
expression" error. I believe the issue has to do with the balance field
because when I run it without the balance criteria, the sql statement works.

GP, BR, CUST_NO, SOURCE, and DATE are all text fields in the Access database.

BALANCE is defined as currency in the Access database. The variable vBal is
dimmed as a currency variable at the beginning of the module.

The only difference I see is in the Access database, BALANCE is displayed as
$29.04. When I move the cursor over vBal, the amount is displayed as 29.04.

I'd appreciate any help getting these statements to work. Thanks.....
--
JT

sql stmt issue
 

Try:

....AND [BALANCE] = " & vBal

--
Tim Zych
SF, CA
"JT" wrote in message
...
I'm trying to update a field (NOTES) in my Access database with 2 sql
statements. The statements are as follows"

Sql = "UPDATE [Data_Current] set [NOTES] = """ & tmpcom & """ where [GP] =
""" & vGroup & """ AND [BR] = """ & Cells(r, 2) & """ AND [CUST_NO] = """
&
Cells(r, 3) & """ AND [SOURCE] = """ & Cells(r, 6) & """ AND [DATE] = """
&
tmpAll & """ AND [BALANCE] = '" & vBal & "'"

Call Recordset.Open(Sql, ConnectionString, adOpenForwardOnly,
adLockReadOnly, CommandTypeEnum.adCmdText)

With these statements, I'm getting a "data type mismatch in criteria
expression" error. I believe the issue has to do with the balance field
because when I run it without the balance criteria, the sql statement
works.

GP, BR, CUST_NO, SOURCE, and DATE are all text fields in the Access
database.

BALANCE is defined as currency in the Access database. The variable vBal
is
dimmed as a currency variable at the beginning of the module.

The only difference I see is in the Access database, BALANCE is displayed
as
$29.04. When I move the cursor over vBal, the amount is displayed as
29.04.

I'd appreciate any help getting these statements to work. Thanks.....
--
JT