query works but cnn.execute does not?

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

rjbuckleyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
flavoConnect With a Mentor Commented:
If cnn is an ADO connection, then you need to use % as the wild card character, and not *
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
dates in access should be enclosed by # and not by "
0
 
Raynard7Commented:
Try encapsulating the times with # rather than "
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
rjbuckleyAuthor Commented:
I get that, but why does it work in the query and not in VBA?
0
 
Raynard7Commented:
sorry angel - you are too fast for me
0
 
Raynard7Commented:
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
 
rjbuckleyAuthor Commented:
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
 
Raynard7Commented:
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
 
rjbuckleyAuthor Commented:
I will try it.  I thought that I had, but I may be confused.....
0
 
rjbuckleyAuthor Commented:
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
 
rjbuckleyAuthor Commented:
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
 
rjbuckleyAuthor Commented:
But  a Query with the try before the last one still does.
0
 
Raynard7Commented:
You are talking about putting records in the table - is this part of an insert statement? or is this teh full sql?
0
 
rjbuckleyAuthor Commented:
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
 
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectCommented:
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
 
rjbuckleyAuthor Commented:
No errors.  The Query returns 3 rows, the ADO returns 0 rows.  The ADO does create the table, but there are no records.
0
 
Raynard7Commented:
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
 
clarkscottCommented:
'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
 
flavoConnect With a Mentor Commented:
Woops, ? should be _ {underscore} - as well as the * being %
0
 
John Mc HaleForensic Computer Examiner, Analyst/Programmer & Database ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.