Pass through query date parameter not working

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pass through query date parameter not working

Author

Topic

actsql
Starting Member

34 Posts

Posted - 2011-01-20 : 16:40:57

The attached pass through query is returning records outside the date value specified. For instance, when I run it using 12/31/10 as the upper limit I am getting records for January 2011. I have included the VBA that is actually creating the PT query in my form.

Pass Through Query:
SELECT gl_history.job_id, gl_history.basic_account_id, gl_history.date_booked, gl_history.div_level_1, sum(gl_history.amount_db - gl_history.amount_cr)as TotDol, jobs.project_class_id

FROM gl_history LEFT JOIN jobs on gl_history.job_id = jobs.job_id

WHERE gl_history.basic_account_id>='4000' AND gl_history.basic_account_id<'6000' AND gl_history.basic_account_id<>'4022' AND gl_history.basic_account_id<>'5050' AND gl_history.date_booked <=12/31/2010 AND gl_history.job_id = 'IN-2204'

VBA from form to create query:
strSQL = "SELECT gl_history.job_id, gl_history.basic_account_id, gl_history.date_booked, gl_history.div_level_1, " _
& "sum(gl_history.amount_db - gl_history.amount_cr)as TotDol, jobs.project_class_id " _
& "FROM gl_history LEFT JOIN jobs on gl_history.job_id = jobs.job_id " _
& "WHERE gl_history.basic_account_id>='4000' AND gl_history.basic_account_id<'6000' AND gl_history.basic_account_id<>'4022' AND gl_history.basic_account_id<>'5050' " _
& "AND gl_history.date_booked <=" & CDate(Me.PrecMoEndDate) _
& " AND " & strTemp

Any help is much appreciated.

Thanks

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-20 : 22:31:30

[code]strSQL = "SELECT gl_history.job_id, gl_history.basic_account_id, gl_history.date_booked, gl_history.div_level_1, " _
& "sum(gl_history.amount_db - gl_history.amount_cr)as TotDol, jobs.project_class_id " _
& "FROM gl_history LEFT JOIN jobs on gl_history.job_id = jobs.job_id " _
& "WHERE gl_history.basic_account_id>='4000' AND gl_history.basic_account_id<'6000' AND gl_history.basic_account_id<>'4022' AND gl_history.basic_account_id<>'5050' " _
& "AND gl_history.date_booked <='" & CDate(Me.PrecMoEndDate) _
& "' AND " & strTemp

[/code]

Notice the single quotes around the date.