?
Solved

query works but cnn.execute does not?

Posted on 2006-07-18
20
Medium Priority
?
353 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
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 2000 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

752 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