[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 363
  • Last Modified:

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

0
rjbuckley
Asked:
rjbuckley
  • 8
  • 6
  • 2
  • +3
2 Solutions
 
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
 
rjbuckleyAuthor Commented:
I get that, but why does it work in the query and not in VBA?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
flavoCommented:
If cnn is an ADO connection, then you need to use % as the wild card character, and not *
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
 
flavoCommented:
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 8
  • 6
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now