help needed with query

[associates]

Hi,

I was wondering if i could get some help here. I got problem while trying to perform sql query. I got an error message saying

"No value given for one or more required parameters".

I think i know what caused this but i just don't know how to get around this.

The query I'm trying to perform requires 3 tables and 2 queries.
Tables: Projects, Plan_Names, Reason
Queries: PSQuery, ProjectQuery

I think this is due to the fact that those two queries are empty for a start. Hence, it needs more value.

My question is how do i in the code, tell it to execute the two queries before attempting the query i want to perform.

Thank you in advance

 

[neileg]

If you run a query that is based on other queries, these other queries will be run, too. So that's not your problem. Unless, of course, these queries need parameters that you haven't supplied. Even then, I would expect you to be prompted for them.

 

[associates]

Thank you for your reply.

I still can't get the query to work in the code. I have tested by putting the sql query statement in the queries and it produced result.

Here is my code

    StrSQL = "SELECT [Plan Summaries].PN_ID, System.System_Name, [Plan Names].PS_Name, Projects.J_ID, Projects.Reason_Code, Projects.Reason_Code2, Projects.Reason_Code3, Projects.[2003/4], Projects.[2004/5], Projects.[2005/6], Projects.[2006/7], Projects.[2007/8], Projects.[2008/9], Projects.[2009/10], Projects.[2010/11], Projects.[2011/12], Projects.[2012/13], Projects.[2013/14], Projects.[2014/15], Projects.[2015/16], Projects.[2016/17], Projects.[2017/18], Projects.[Project Code], Projects.Project_ID, Projects.Project_Name"
    StrSQL = StrSQL & " FROM (System INNER JOIN ([Plan Summaries] INNER JOIN Projects ON [Plan Summaries].PS_ID = Projects.PS_ID) ON System.System_ID = [Plan Summaries].System_ID) INNER JOIN [Plan Names] ON [Plan Summaries].PN_ID = [Plan Names].PN_ID"
    StrSQL = StrSQL & " WHERE ((([Plan Summaries].PN_ID) Like '*') And ((System.System_Name) Like 'Ballarat') And ((Projects.J_ID) Like '*') And ((Projects.Reason_Code) Like '*') And ((Projects.[Project Code]) Like 'C') And ((Projects.Project_ID) Like '*') And ((Projects.Project_Name) Like '*'))"
    StrSQL = StrSQL & " GROUP BY [Plan Summaries].PN_ID, System.System_Name, [Plan Names].PS_Name, Projects.J_ID, Projects.Reason_Code, Projects.Reason_Code2, Projects.Reason_Code3, Projects.[2003/4], Projects.[2004/5], Projects.[2005/6], Projects.[2006/7], Projects.[2007/8], Projects.[2008/9], Projects.[2009/10], Projects.[2010/11], Projects.[2011/12], Projects.[2012/13], Projects.[2013/14], Projects.[2014/15], Projects.[2015/16], Projects.[2016/17], Projects.[2017/18], Projects.[Project Code], Projects.Project_ID, Projects.Project_Name"
    StrSQL = StrSQL & " ORDER BY [Plan Summaries].PN_ID,System.System_Name

As you may have noticed that i actually hard coded the values for those variables, it still produced nothing when it should.

then, i have

        Do Until rsNew1.EOF
            linecount = linecount + 1
            strList = rsNew1!PS_Name & "," & rsNew1!System_Name & "," & rsNew1!PN_ID & "," & rsNew1!J_ID & "," & rsNew1!Reason_Code & "," & rsNew1!Reason_Code2 & "," & rsNew1!Reason_Code3 & "," & rsNew1(year1) * 1000 & "," & rsNew1(year2) * 1000 & "," & rsNew1(year3) * 1000 & "," & rsNew1(year4) * 1000 & "," & rsNew1(year5) * 1000 & "," & rsNew1(year6) * 1000 & "," & rsNew1(year7) * 1000 & "," & rsNew1(year8) * 1000 & "," & rsNew1(year9) * 1000 & "," & rsNew1(year10) * 1000 & "," & rsNew1(year11) * 1000 & "," & rsNew1(year12) * 1000
            a.writeline strList

            rsNew1.MoveNext
        loop
        rsNew1.close

What happens is that it did not go into the loop but skip the whole loop as if rsNew1 returns nothing (Nil). But i have tested the query statement in the queries and run it from there, i got some result.

Is there anything i did wrong here? it really drives me nut. (sorry, it really frustrated me)

Thank you in advance

 

[KeithG]

IF you can save the SQL statement as a query I suggest you do so. Then you can use the query name instead of the SQL statement with the db.openrecordset( QueryName).

 

[neileg]

Ah, you didn't say it was string SQL. I agree with KeithG.

 

[associates]

Thank you to you both, KeithG and Neileg.

I tried to use openrecordset as you said but got compilation error. It says user-defined type not defined and it's pointing to "set rsNew1 as DAO.recordset"

Here is my code

   Dim rsNew1 As DAO.Recordset
    Set rsNew1 = CurrentDb.openrecordset("qProjectsSummary")
        
    Do Until rsNew1.EOF
        linecount = linecount + 1
        strList = rsNew1!PS_Name & "," & rsNew1!System_Name & "," & rsNew1!PN_ID & "," & rsNew1!J_ID & "," & rsNew1!Reason_Code & "," & rsNew1!Reason_Code2 & "," & rsNew1!Reason_Code3 & "," & rsNew1(year1) * 1000 & "," & rsNew1(year2) * 1000 & "," & rsNew1(year3) * 1000 & "," & rsNew1(year4) * 1000 & "," & rsNew1(year5) * 1000 & "," & rsNew1(year6) * 1000 & "," & rsNew1(year7) * 1000 & "," & rsNew1(year8) * 1000 & "," & rsNew1(year9) * 1000 & "," & rsNew1(year10) * 1000 & "," & rsNew1(year11) * 1000 & "," & rsNew1(year12) * 1000
        a.writeline strList

        rsNew1.MoveNext
            
    Loop
        
    rsNew1.Close

I don't think it recognised DAO. And would you mind checking if that's the way to retrieve value out of rsNew1 in the loop?

Thank you in advance

 

[associates]

Hi again,

I don't know if i could get response ASAP as i have to get this to work. I know you guys are trying to do your best.

It's just me that unable to get this query statement to produce some recordset. (frustrated) don't mind me

anyway, i got error from this line of code as follows

Set rsNew1 = CurrentDb.openrecordset("qProjectsSummary")

it says that it needs more argument. I thought they are optional. Hence, it won't run.

Now, i think i found out why the recordset is none when executing the sql statement.

The following code works fine as it returns some recordsets

...
StrSQL = StrSQL & " WHERE (Projects.J_ID) Like '31'
...

but this doesn't return anything

StrSQL = StrSQL & " WHERE (Projects.J_ID) Like '*'

rsNew.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until rsNew.EOF
        MsgBox "test"
        rsNew3.MoveNext
Loop

it just doesn't even get into the loop because rsNew has reached EOF.

Is it because it doesn't like the wildcard "*".

Thank you in advance

 

[associates]

Hi

I think i have got it working now. For some reason, it just doesn't like the star "*". But as soon as i replaced it with "%", it then works fine.

Thank you very much for all your time.