[AccessD] Query runs and doesn't run

[McGillivray, Don]

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