I want to create a temporary query at runtime

[debasis6677077]

I want to create a temporary query at runtime. I am using CreateQueryDef but compilation error comming. I have used the following coding

   Set database = CodeDb
 
   Dim dbsSunData As database
   Dim qdfTemp As QueryDef
   Dim qdfNew As QueryDef
 
   Set dbsSunData = OpenDatabase("sunlinkdata.mdb")
 
   With dbsSunData
      ' Create temporary QueryDef.
      Set qdfTemp = SunLinkData.CreateQueryDef("cldata", _
          "SELECT Location, accnt_code, exprperiod, trans_date, amount, d_c, due_date, expralloc_ref " & _
          "FROM salfldgvil " & _
          "WHERE (accnt_code >='" & Trim(Me.cboPartyCodeFr.Value) & "' AND " & _
                 "accnt_code <= '" & Trim(Me.cboPartycodeTo.Value) & "') AND " & _
                 "(trans_date >= #" & Me.dtpTranDtFrom.Value & "# AND " & _
                 "trans_date <= " & Me.dtpTranDateTo.Value & _
                 "#) ORDER BY alloc_ref, d_c")
 

Can you please help me.

[MMcCarthy]

What exactly does the error say?

[FishVal]

Hi, there.

Your variable SunLinkData supposed to be DAO.Database object was not declared nor initialized. Change it to dbsSunData or remove at all as soon as the instruction is within

With dbsSunData
End With
 

block.

[Jim Doherty]

I want to create a temporary query at runtime. I am using CreateQueryDef but compilation error comming. I have used the following coding

Set database = CodeDb
 
Dim dbsSunData As database
Dim qdfTemp As QueryDef
Dim qdfNew As QueryDef
 
Set dbsSunData = OpenDatabase("sunlinkdata.mdb")
 
With dbsSunData
' Create temporary QueryDef.
Set qdfTemp = SunLinkData.CreateQueryDef("cldata", _
"SELECT Location, accnt_code, exprperiod, trans_date, amount, d_c, due_date, expralloc_ref " & _
"FROM salfldgvil " & _
"WHERE (accnt_code >='" & Trim(Me.cboPartyCodeFr.Value) & "' AND " & _
"accnt_code <= '" & Trim(Me.cboPartycodeTo.Value) & "') AND " & _
"(trans_date >= #" & Me.dtpTranDtFrom.Value & "# AND " & _
"trans_date <= " & Me.dtpTranDateTo.Value & _
"#) ORDER BY alloc_ref, d_c")
 

Can you please help me.

In addition ........ amend this
Set qdfTemp = SunLinkData.CreateQueryDef

to read

Set qdfTemp = dbsSunData.CreateQueryDef


Jim :)