Solved

query works but cnn.execute does not?

Posted on 2006-07-18
20
338 Views
Last Modified: 2008-03-03
sqlstring = "SELECT DateDiff(" & Chr(34) & "n" & Chr(34) & ",[timeout],[time63]) AS ElapsedTime, VehicleClosed.Town, VehicleClosed.Vehicle, VehicleClosed.DateOut INTO tblTemp " & _
"FROM VehicleClosed GROUP BY DateDiff(" & Chr(34) & "n" & Chr(34) & ",[timeout],[time63]), VehicleClosed.Town, VehicleClosed.Vehicle, VehicleClosed.DateOut, VehicleClosed.IncidentID, VehicleClosed.Time63 " & _
"HAVING VehicleClosed.Town=" & Chr(34) & Me.Town.Column(1) & Chr(34) & " AND VehicleClosed.Vehicle Like " & Chr(34) & Me.Vehicle & Chr(34) & " AND (VehicleClosed.DateOut Between " & Chr(34) & DateRange & Chr(34) & ") AND VehicleClosed.Time63 <>''"

This is the debug.print of my sqlstring query:

'SELECT DateDiff("n",[timeout],[time63]) AS ElapsedTime, VehicleClosed.Town, VehicleClosed.Vehicle, VehicleClosed.DateOut INTO tblTemp FROM VehicleClosed GROUP BY DateDiff("n",[timeout],[time63]), VehicleClosed.Town, VehicleClosed.Vehicle, VehicleClosed.DateOut, VehicleClosed.IncidentID, VehicleClosed.Time63 HAVING VehicleClosed.Town="SEYMOUR" AND VehicleClosed.Vehicle Like "?-*" AND (VehicleClosed.DateOut Between "04/01/2004" AND "04/28/2004") AND VehicleClosed.Time63 <>''

If I paste the debug.print into a Query (SQL View) it works fine.  In VBA it does not return records.  Any ideas?

Thanks,

Rich

0
Comment
Question by:rjbuckley
  • 8
  • 6
  • 2
  • +3
20 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17129037
dates in access should be enclosed by # and not by "
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17129048
Try encapsulating the times with # rather than "
0
 

Author Comment

by:rjbuckley
ID: 17129050
I get that, but why does it work in the query and not in VBA?
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 35

Expert Comment

by:Raynard7
ID: 17129056
sorry angel - you are too fast for me
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17129069
The query translates the query before submitting it to jet - it assumes that a date field is a date and changes it to the default date format - submitting to a conn it is not translated
0
 

Author Comment

by:rjbuckley
ID: 17129076
Here's the run.  The guy that created the database made all the fields text.  It is not a date field. I could not get the # to work.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17129086
You cant use text with between - translate the string date function to a date and it should work - ie cdate(VehicleClosed.DateOut) Between #04/01/2004# AND #04/28/2004#

Obviously access is assuming because you are using the between keyword you need to change the figures to numbers
0
 

Author Comment

by:rjbuckley
ID: 17129095
I will try it.  I thought that I had, but I may be confused.....
0
 

Author Comment

by:rjbuckley
ID: 17129111
This is the debug.  It still does not put records in the table.....

SELECT DateDiff("n",[timeout],[time63]) AS ElapsedTime, VehicleClosed.Town, VehicleClosed.Vehicle, VehicleClosed.DateOut INTO tblTemp FROM VehicleClosed GROUP BY DateDiff("n",[timeout],[time63]), VehicleClosed.Town, VehicleClosed.Vehicle, VehicleClosed.DateOut, VehicleClosed.IncidentID, VehicleClosed.Time63 HAVING VehicleClosed.Town="SEYMOUR" AND VehicleClosed.Vehicle Like "?-*" AND (cdate(VehicleClosed.DateOut) Between #04/01/2004# AND #04/28/2004#) AND VehicleClosed.Time63 <>''
0
 
LVL 34

Accepted Solution

by:
flavo earned 500 total points
ID: 17129114
If cnn is an ADO connection, then you need to use % as the wild card character, and not *
0
 

Author Comment

by:rjbuckley
ID: 17129124
SELECT DateDiff("n",[timeout],[time63]) AS ElapsedTime, VehicleClosed.Town, VehicleClosed.Vehicle, VehicleClosed.DateOut INTO tblTemp FROM VehicleClosed GROUP BY DateDiff("n",[timeout],[time63]), VehicleClosed.Town, VehicleClosed.Vehicle, VehicleClosed.DateOut, VehicleClosed.IncidentID, VehicleClosed.Time63 HAVING VehicleClosed.Town="SEYMOUR" AND VehicleClosed.Vehicle Like "?-%" AND (cdate(VehicleClosed.DateOut) Between #04/01/2004# AND #04/28/2004#) AND VehicleClosed.Time63 <>''
 

Does not work either......
0
 

Author Comment

by:rjbuckley
ID: 17129128
But  a Query with the try before the last one still does.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17129149
You are talking about putting records in the table - is this part of an insert statement? or is this teh full sql?
0
 

Author Comment

by:rjbuckley
ID: 17129157
It  is a Select ...Into.  If this won't work in VBA can I use the Query and pass parameters to it from VBA.  How would you do that.  I need to pass Town, DateRange, and Vehicle.
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 17129161
Is the attempt to execute this query via the ADO connection object causing an error?, you could examine the Errors collection of the connection object to see if anything has gone awry! This may give some clue as to why it does'nt work with ADO.
0
 

Author Comment

by:rjbuckley
ID: 17129168
No errors.  The Query returns 3 rows, the ADO returns 0 rows.  The ADO does create the table, but there are no records.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17129245
I'm thinking that you do not need the ado connection.

Can you put the two below lines after the select part of the statement - where strBackupLocation is the location of the db

"INTO VehicleClosed" & vbCrLf & _
"IN" & chr(34) & strBackupLocation & vbCrLf & chr(34) & _

And then do

DoCmd.RunSQL sqlString
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 17129257
'SELECT DateDiff("n",[timeout],[time63]) AS ElapsedTime, VehicleClosed.Town, VehicleClosed.Vehicle, VehicleClosed.DateOut INTO tblTemp FROM VehicleClosed GROUP BY DateDiff("n",[timeout],[time63]), VehicleClosed.Town, VehicleClosed.Vehicle, VehicleClosed.DateOut, VehicleClosed.IncidentID, VehicleClosed.Time63 HAVING VehicleClosed.Town="SEYMOUR" AND VehicleClosed.Vehicle Like "?-*" AND (VehicleClosed.DateOut Between "04/01/2004" AND "04/28/2004") AND VehicleClosed.Time63 <>''


What's the deal with the last set (or single) quotes??????  This is your debug printout.  A single apostrophe appears at the beginning of the code ('Select)- and assuming the last one is it's partner - you are missing a quote in the VehicleClosed.Time63 <> '     ????

Scott C
0
 
LVL 34

Assisted Solution

by:flavo
flavo earned 500 total points
ID: 17129258
Woops, ? should be _ {underscore} - as well as the * being %
0
 
LVL 13

Expert Comment

by:John Mc Hale
ID: 17129269
Try breaking the Query up into smaller chunks testing each with ADO to see if there's a difference. I don't see any ADO code in any of your previous  postings, so i'm not sure what you're trying to achieve!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question