[AccessD] Query runs and doesn't run

[Doug Steele]

How about (air code) instead of the dbs.execute strSQL:

dim rs as dao.recordset
set rs = currentdb.openrecordset strSQL

do while not rs.eof
  (display data or whatever)

rs.movenext
loop

On Tue, Jan 9, 2018 at 3:17 PM, McGillivray, Don <DMcGillivray at ctc.ca.gov>
wrote:

> I'm pretty sure that "dbs.execute" requires an action query (INSERT,
> UPDATE, DELETE).  Won't work with a SELECT.
>
> If you think about it, that makes sense; what do you expect to happen with
> the results of that SELECT in your code?
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Paul Hartland via AccessD
> Sent: Tuesday, January 09, 2018 2:34 PM
> To: Access List <accessd at databaseadvisors.com>
> Cc: Paul Hartland <paul.hartland at googlemail.com>
> Subject: Re: [AccessD] Query runs and doesn't run
>
> Have you tried running it without the where clause, then if it works try
> again with one of the where clauses, then the other....also when I used to
> query dates in access I would use something like the below, formatting the
> date
>
> Where MyDate =#" &  format("06/01/1935", "dd/mm/yyyy")& "#"
>
> On 9 Jan 2018 22:23, "Kaup, Chester" <Chester_Kaup at kindermorgan.com>
> wrote:
>
> > Well I did find one syntax error. Need two pairs of double quotes
> > around PATT. Should have been ""PATT"". Still getting no results though.
> >
> > From: Kaup, Chester
> > Sent: Tuesday, January 09, 2018 3:50 PM
> > To: 'Access Developers discussion and problem solving'
> > Subject: Query runs and doesn't run
> >
> > I have the following query that run just fine from the query grid
> >
> > SELECT Allocations.ChildPID, Allocations.ChildPTYPE, Allocations.PID,
> > Allocations.PTYPE, Allocations.Date, Allocations.Factor_1,
> > Allocations.Factor_2 INTO [tbl Initial Pattern Factors] FROM
> > Allocations WHERE (((Allocations.PTYPE)="PATT") AND
> > ((Allocations.Date)=#6/1/1935#));
> >
> > I have the same query in VBA but it generates no output. I am at a
> > loss as to what is going on. Here is the VBA part.
> >
> > Dim dbs As DAO.Database
> > Set dbs = CurrentDb
> >
> > strSQL = "SELECT Allocations.ChildPID, Allocations.ChildPTYPE,
> > Allocations.PID, Allocations.PTYPE, Allocations.Date,
> > Allocations.Factor_1,
> > Allocations.Factor_2 INTO [tbl Initial Pattern Factors] " & _
> >                   "FROM Allocations " & _
> >                   "WHERE Allocations.PTYPE='PATT' AND
> > Allocations.Date=#6/1/1935#;"
> >
> >         dbs.Execute strSQL
> >
> >
> > What is even stranger is the following statement does not fail
> > although I do not see the table in the list of tables
> >
> > DoCmd.DeleteObject acTable, "tbl Initial Pattern Factors"
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>