By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,829 Members | 1,798 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,829 IT Pros & Developers. It's quick & easy.

Updating data using VBA and SQL

P: n/a
Hi All

My many thanks to DFS for helping me with my problem.

I had posted my problem a few weeks ago and DFS helped me out with the
code.

After having completed my other project, I got time to work on this
problem.

However, a part of this code is giving me a problem and I have a
feeling that either and a parenthesis is missing or there is an extra
parenthesis but I dont know which.
This is the code:

'LOOK FOR PRIOR MONTH PAYMENT
set rs1 = db.openRecordset("SELECT DISTINCT * FROM
tblTenantTransaction
WHERE TenantID = " & rs("TenantID") & " AND (datePart("m",[TransDate])
= " &
priorMonth & " and datePart("yyyy",[TransDate] = " & transYear & ");")
..

When I compile this code I get the following error msg
Compile Error
Syntax Error

My many thanks in advance to all of you for your help.
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On 17 Jan 2004 01:57:49 -0800, ha*******@yahoo.com (Hasanain F. Esmail) wrote:
priorMonth & " and datePart("yyyy",[TransDate] = " & transYear & ");")


Try
priorMonth & " and datePart("yyyy",[TransDate] = " & transYear & "));"
Wayne Gillespie
Gosford NSW Australia
Nov 12 '05 #2

P: n/a
There's no closing parenthesis to match the one that's opened after AND.

HTH
- Turtle

"Hasanain F. Esmail" <ha*******@yahoo.com> wrote in message
news:27**************************@posting.google.c om...
Hi All

My many thanks to DFS for helping me with my problem.

I had posted my problem a few weeks ago and DFS helped me out with the
code.

After having completed my other project, I got time to work on this
problem.

However, a part of this code is giving me a problem and I have a
feeling that either and a parenthesis is missing or there is an extra
parenthesis but I dont know which.
This is the code:

'LOOK FOR PRIOR MONTH PAYMENT
set rs1 = db.openRecordset("SELECT DISTINCT * FROM
tblTenantTransaction
WHERE TenantID = " & rs("TenantID") & " AND (datePart("m",[TransDate])
= " &
priorMonth & " and datePart("yyyy",[TransDate] = " & transYear & ");")
.

When I compile this code I get the following error msg
Compile Error
Syntax Error

My many thanks in advance to all of you for your help.

Nov 12 '05 #3

P: n/a
Thanks for your help.

Pardon my poor understandign of VBA and SQL

Can you please show me the systax
"MacDermott" <ma********@nospam.com> wrote in message news:<AM*****************@newsread1.news.atl.earth link.net>...
There's no closing parenthesis to match the one that's opened after AND.

HTH
- Turtle

"Hasanain F. Esmail" <ha*******@yahoo.com> wrote in message
news:27**************************@posting.google.c om...
Hi All

My many thanks to DFS for helping me with my problem.

I had posted my problem a few weeks ago and DFS helped me out with the
code.

After having completed my other project, I got time to work on this
problem.

However, a part of this code is giving me a problem and I have a
feeling that either and a parenthesis is missing or there is an extra
parenthesis but I dont know which.
This is the code:

'LOOK FOR PRIOR MONTH PAYMENT
set rs1 = db.openRecordset("SELECT DISTINCT * FROM
tblTenantTransaction
WHERE TenantID = " & rs("TenantID") & " AND (datePart("m",[TransDate])
= " &
priorMonth & " and datePart("yyyy",[TransDate] = " & transYear & ");")
.

When I compile this code I get the following error msg
Compile Error
Syntax Error

My many thanks in advance to all of you for your help.

Nov 12 '05 #4

P: n/a
Below is probably the syntax you want.

But if you're going to work with this kind of thing, I'd really recommend
that you try to understand it.
It's not hard to count parentheses and see which kind you have more of.

HTH
- Turtle

set rs1 = db.openRecordset("SELECT DISTINCT * FROM
tblTenantTransaction
WHERE TenantID = " & rs("TenantID") & " AND (datePart("m",[TransDate])
= " &
priorMonth & " and datePart("yyyy",[TransDate] = " & transYear & "));")

"Hasanain F. Esmail" <ha*******@yahoo.com> wrote in message
news:27*************************@posting.google.co m...
Thanks for your help.

Pardon my poor understandign of VBA and SQL

Can you please show me the systax
"MacDermott" <ma********@nospam.com> wrote in message

news:<AM*****************@newsread1.news.atl.earth link.net>...
There's no closing parenthesis to match the one that's opened after AND.
HTH
- Turtle

"Hasanain F. Esmail" <ha*******@yahoo.com> wrote in message
news:27**************************@posting.google.c om...
Hi All

My many thanks to DFS for helping me with my problem.

I had posted my problem a few weeks ago and DFS helped me out with the
code.

After having completed my other project, I got time to work on this
problem.

However, a part of this code is giving me a problem and I have a
feeling that either and a parenthesis is missing or there is an extra
parenthesis but I dont know which.
This is the code:

'LOOK FOR PRIOR MONTH PAYMENT
set rs1 = db.openRecordset("SELECT DISTINCT * FROM
tblTenantTransaction
WHERE TenantID = " & rs("TenantID") & " AND (datePart("m",[TransDate])
= " &
priorMonth & " and datePart("yyyy",[TransDate] = " & transYear & ");")
.

When I compile this code I get the following error msg
Compile Error
Syntax Error

My many thanks in advance to all of you for your help.

Nov 12 '05 #5

P: n/a
Thank you very much
"MacDermott" <ma********@nospam.com> wrote in message news:<FP*****************@newsread1.news.atl.earth link.net>...
Below is probably the syntax you want.

But if you're going to work with this kind of thing, I'd really recommend
that you try to understand it.
It's not hard to count parentheses and see which kind you have more of.

HTH
- Turtle

set rs1 = db.openRecordset("SELECT DISTINCT * FROM
tblTenantTransaction
WHERE TenantID = " & rs("TenantID") & " AND (datePart("m",[TransDate])
= " &
priorMonth & " and datePart("yyyy",[TransDate] = " & transYear & "));")

"Hasanain F. Esmail" <ha*******@yahoo.com> wrote in message
news:27*************************@posting.google.co m...
Thanks for your help.

Pardon my poor understandign of VBA and SQL

Can you please show me the systax
"MacDermott" <ma********@nospam.com> wrote in message

news:<AM*****************@newsread1.news.atl.earth link.net>...
There's no closing parenthesis to match the one that's opened after AND.
HTH
- Turtle

"Hasanain F. Esmail" <ha*******@yahoo.com> wrote in message
news:27**************************@posting.google.c om...
> Hi All
>
> My many thanks to DFS for helping me with my problem.
>
> I had posted my problem a few weeks ago and DFS helped me out with the
> code.
>
> After having completed my other project, I got time to work on this
> problem.
>
> However, a part of this code is giving me a problem and I have a
> feeling that either and a parenthesis is missing or there is an extra
> parenthesis but I dont know which.
>
>
> This is the code:
>
> 'LOOK FOR PRIOR MONTH PAYMENT
> set rs1 = db.openRecordset("SELECT DISTINCT * FROM
> tblTenantTransaction
> WHERE TenantID = " & rs("TenantID") & " AND (datePart("m",[TransDate])
> = " &
> priorMonth & " and datePart("yyyy",[TransDate] = " & transYear & ");")
> .
>
> When I compile this code I get the following error msg
> Compile Error
> Syntax Error
>
> My many thanks in advance to all of you for your help.

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.