Solved

query works but cnn.execute does not?

Posted on 2006-07-18
20
328 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 142

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
 
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now