Help with Syntax Error and Sum

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Hello,
I get a syntax error when my function gets to this line: .Open "SELECT *
FROM [tblWages] WHERE [dtmDate] = Year[dtmDate]= Year(Now())",
CurrentProject.Connection

What I'm trying to do is calculate year to date gross wages. Once it gets
past the above line, I also need to know how to sum the field [dblGross] in
my table [tblWages] so I can put the results into a text box on my report.
I'm using vba in Access 2003.
Thanks for any help

Jim
 
Your WHERE clause is incorrect. You can only Field = Value or Field = Field,
not Field = Value = Value

I suspect what you want is

Open "SELECT * FROM [tblWages] WHERE Year([dtmDate])= Year(Date())",
CurrentProject.Connection

or

Open "SELECT * FROM [tblWages] WHERE dtmDate BETWEEN
YearSerial(Year(Date()), 1, 1) AND YearSerial(Year(Date()), 12, 31)",
CurrentProject.Connection

The advantage of the second one is that it will use an index is dtmDate is
in one. It also requires far more function calls.
 
Thanks

Douglas J. Steele said:
Your WHERE clause is incorrect. You can only Field = Value or Field =
Field, not Field = Value = Value

I suspect what you want is

Open "SELECT * FROM [tblWages] WHERE Year([dtmDate])= Year(Date())",
CurrentProject.Connection

or

Open "SELECT * FROM [tblWages] WHERE dtmDate BETWEEN
YearSerial(Year(Date()), 1, 1) AND YearSerial(Year(Date()), 12, 31)",
CurrentProject.Connection

The advantage of the second one is that it will use an index is dtmDate is
in one. It also requires far more function calls.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Jim said:
Hello,
I get a syntax error when my function gets to this line: .Open "SELECT *
FROM [tblWages] WHERE [dtmDate] = Year[dtmDate]= Year(Now())",
CurrentProject.Connection

What I'm trying to do is calculate year to date gross wages. Once it gets
past the above line, I also need to know how to sum the field [dblGross]
in my table [tblWages] so I can put the results into a text box on my
report. I'm using vba in Access 2003.
Thanks for any help

Jim
Click to expand...
Click to expand...
 
Back
Top