OpenQuery

[OldManRiver]

All,

The normal use for this command is for and existing query.

I have an Append (table building) query "qryBLDfnr" and need to mod it for filtering by date with:

Sub Sav_Tmp(MyTable)
    ' Save values to the Temp Table
    Dim DATbeg, DATend, SQLstr, WHRstr
    DATbeg = TargetForm![tboxSDT]
    DATend = TargetForm![tboxEDT]
    WHRstr = "WHERE (([tmp_wdt] <= DateSerial(Year(DATbeg), Month(DATbeg), Day(DATbeg))) AND " & _
             "([tmp_wdt] >= DateSerial(Year(DATend), Month(DATend), Day(DATend))));"
    SQLstr = "SELECT * FROM qryBLDfnr " & WHRstr
    DoCmd.SetWarnings False
    DoCmd.OpenQuery SQLstr
    DoCmd.SetWarnings True
End Sub

Since it is an Append query the DoCmd.RunSQL will not work and with the SQL string I get errors.

I know somehow I need to assign this to an object, based on the error message of:

Run-time error '7874':

Microsoft Office Access can't find the object 'SELECT * FROM qryBLDfnr WHERE (([tmp_wdt] <= DateSerial(Year(DATbeg), Month(DATbeg), Day(DATbeg))) AND ([tmp_wdt] >= DateSerial(Year(DATend), Month(DATend), Day(DATend))));.'

But do not know which object type, or syntax.

Have not found any help in the HOWTOs.

Thanks!

OMR

 

[MarkK]

The evaluation of the dates in your where clause needs to occur outside the quotes. For instance, your query 'qryBLDfnr' knows nothing about the variable named 'DATbeg' so the occurance of the string "Year(DATbeg)" in your where clause causes an error.
Probably you mean it to look something like...

Sub Sav_Tmp(MyTable)
  ' Save values to the Temp Table
  Dim DATbeg, DATend, SQLstr, WHRstr
  DATbeg = TargetForm![tboxSDT]
  DATend = TargetForm![tboxEDT]
  WHRstr = _
    "WHERE [tmp_wdt] <= #" & DateSerial(Year(DATbeg), Month(DATbeg), Day(DATbeg)) & "# " & _
      "AND [tmp_wdt] >= #" & DateSerial(Year(DATend), Month(DATend), Day(DATend)) & "#"
  SQLstr = "SELECT * FROM qryBLDfnr " & WHRstr
[COLOR="Green"]  'you can execute SQL against the CurrentDb which doesn't display warnings by default....
[/COLOR]  CurrentDb.Execute SQLstr
End Sub

 

[OldManRiver]

What you are saying is code should be:

Sub Sav_Tmp(MyTable)
    ' Save values to the Temp Table
    Dim DATbeg, DATend, SQLstr, WHRstr, TBLobj As New QueryDef, dbs As DAO.Database, FMTbeg, FMTend
    Set dbs = CurrentDb
    DATbeg = TargetForm![tboxSDT]
    DATend = TargetForm![tboxEDT]
    FMTbeg = DateSerial(Year(DATbeg), Month(DATbeg), Day(DATbeg))
    FMTend = DateSerial(Year(DATend), Month(DATend), Day(DATend))
    WHRstr = "WHERE (([tmp_wdt] <= " & FMTbeg & ") AND ([tmp_wdt] >= " & FMTend & "));"
    SQLstr = "SELECT * FROM qryBLDfnr " & WHRstr
    On Error Resume Next
    With dbs              '  it would be better to check to see if the
        .QueryDefs.Delete ("qryMODfnr")
        Set TBLobj = .CreateQueryDef("qryMODfnr", SQLstr)
        .Close
    End With
    DoCmd.SetWarnings False
    DoCmd.OpenQuery TBLobj
    DoCmd.SetWarnings True
End Sub

Am I right?

OMR

 

[MarkK]

You bet. That looks way promising. You might still have a problem here ...

"WHERE (([COLOR="DarkRed"][tmp_wdt] <= " & FMTbeg & "[/COLOR]) AND...

If [tmp_wdt] is a date your comparison value needs to be delimited with '#' as follows...

"WHERE (([COLOR="DarkRed"][tmp_wdt] <= #" & FMTbeg & "#[/COLOR]) AND...

But what happens when you run it?

 

[OldManRiver]

Yes,

Thanks! forgot the date value padding.

OMR

 

[OldManRiver]

Working but not saving

All,

This code is working making query and all but not saving:

Sub Sav_Tmp(MyTable)
    ' Save values to the Temp Table
    Dim dbs As DAO.Database, WSp As DAO.Workspace, RSc As DAO.Recordset, RSs As DAO.Recordset
    Dim DATbeg, DATend, FMTbeg, FMTend, SQLstr, WHRstr
    Set dbs = CurrentDb
    DATbeg = TargetForm![tboxSDT]
    DATend = TargetForm![tboxEDT]
    FMTbeg = DateSerial(Year(DATbeg), Month(DATbeg), Day(DATbeg))
    FMTend = DateSerial(Year(DATend), Month(DATend), Day(DATend))
    WHRstr = "WHERE (([tmp_wdt] >= #" & FMTbeg & "#) AND ([tmp_wdt] <= #" & FMTend & "#));"
    SQLstr = "SELECT * FROM tmpREPfnr " & WHRstr
'    On Error Resume Next
    With dbs              '  it would be better to check to see if the
        .QueryDefs.Delete ("qryMODfnr")
        Set TBLobj = .CreateQueryDef("qryMODfnr", SQLstr)
        DoCmd.SetWarnings False
        DoCmd.OpenQuery TBLobj.Name
        DoCmd.SetWarnings True
        .Close
    End With
End Sub

Needs to save as table in currentDB.

If you can help with that would appreciate.

Thanks!

OMR

 

[OldManRiver]

Resolved - New Thread Needed

All,

Will close this with:

Sub Sav_Tmp_Qry(MyTable)
    ' Save values to the Temp Table
    Dim DATbeg, DATend, SQLstr, WHRstr, TBLobj As New QueryDef, dbs As DAO.Database, FMTbeg, FMTend
    Set dbs = CurrentDb
    DATbeg = TargetForm![tboxSDT]
    DATend = TargetForm![tboxEDT]
    FMTbeg = DateSerial(Year(DATbeg), Month(DATbeg), Day(DATbeg))
    FMTend = DateSerial(Year(DATend), Month(DATend), Day(DATend))
    WHRstr = "WHERE (([tmp_wdt] >= " & FMTbeg & ") AND ([tmp_wdt] <= " & FMTend & "));"
    SQLstr = "SELECT * FROM qryBLDfnr " & WHRstr
'    On Error Resume Next
    With dbs              '  it would be better to check to see if the
        .QueryDefs.Delete ("qryMODfnr")
        Set TBLobj = .CreateQueryDef("qryMODfnr", SQLstr)
        DoCmd.SetWarnings False
        DoCmd.OpenQuery TBLobj.Name
        DoCmd.SetWarnings True
'        .saveas "tblMODfnr"
        .Close
    End With
End Sub

And will open another thread as I need to save my results in a table, but that is another issue.

OMR