Run-time error '3065' Cannot execute a select query.

[johnve]

Hi,

I have written lots of the queries but I'm struggling with this one.

I get the run-time error 3065 when I run the following sql.

Dim db As DAO.Database
Dim sqlstring As String

Set db = DBEngine(0).Databases(0)


sqlstring = "SELECT ebk.hr_leav_amnt AS hr_clia_hour, ebk.hr_leav_type, ebk.hr_leav_code, ebk.hr_empl_code, ebk.hr_loadg_amt AS hr_loadg_amt, 'Leave Pay' AS hr_provision, mst.hr_paym_code, mst.hr_base_hour, '' AS hr_splt_accr, mst.hr_leav_abbr, ype.hr_norm_pcnt, ype.hr_allw_amnt"
sqlstring = sqlstring + " FROM hrtlvebk AS ebk, hrtlvmst AS mst, hrtptype AS ype"
sqlstring = sqlstring + " WHERE ebk.hr_leav_code Like 'a%' And ebk.hr_leav_code = [mst].[hr_leav_code] And ebk.hr_leav_type Like '1%' And bk.hr_leav_type = [mst].[hr_leav_type] And ebk.hr_recd_type = 'a' And ebk.hr_lbkg_refn = 'ACCRUAL' And ebk.hr_from_dati >= 20140701 And ebk.hr_from_dati <= 20140730 And mst.hr_load_rule <> 'y' And mst.hr_paym_code = [ype].[hr_paym_code]"
sqlstring = sqlstring + " GROUP BY ebk.hr_leav_amnt, ebk.hr_leav_type, ebk.hr_leav_code, ebk.hr_empl_code, ebk.hr_loadg_amt, mst.hr_paym_code, mst.hr_base_hour, mst.hr_leav_abbr, ype.hr_norm_pcnt, ype.hr_allw_amnt"
db.Execute sqlstring, dbFailOnError

When I run statement with Query (SQL) it works fine. The only thing I change is the text in the where clause.. ('a%' - Query it is "a%")

Thank you in advance.

John

 

[PHV]

The db.Execute method is for action queries, not SELECT query.
You may use the OpenRecordset method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[johnve]

Hi PHV,

Thank you but now I get Run-time error 3061 "Too few parameters. Expecting 1.

Set rs = db.OpenRecordset(sqlstring, dbOpenSnapshot)

Thanks

John

 

[maxhugen]

Looks like you have a typo: "And bk.hr_leav_type ="

You can put a code break after you have your sql string assembled, and get the output in the Immediate window (?sqlstring), then copy this to a new query in SQL View, and check the query to errors.

Max Hugen
Australia

 

[Darrylles]

Well spotted max.

Also, it seems you're mixing your SQL syntax (unless you're using a pass-through query to another database type, which means that you're in the wrong forum).

MS Access uses '*' as a wildcard. '%' will not act as a LIKE wildcard character (it will search for the exact string including the % symbol).

(Also, you have an alias that replicates the actual field name: (hr_loadg_amt)).

Point to note here is that this parameter error is telling you that it can't find a table or field that your SQL refers-to, in this case - an aliased table called 'BK'.

Darrylle ;-)

p.s. This is an old thread. It's courteous to respond (with an update), so that people can then rely on the 'solution'.





Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[johnve]

Hi All,

Thank you for your posts.

After reviewing the code again I noticed I missed the "e" against a table alias.

Thank you again