CDATE ISSUE

[jmc014]

Hi, we are working with a simple Db that queries data from a table via an ODBC connection.
The main query that we are working with should gather data related to a departement or "HU" together with a date criteia taken from a simple form.

Here is a copy of the SQL:

SELECT dbo_SERVICE_TAXI_VIEW.[ID Bitacora], dbo_SERVICE_TAXI_VIEW.[STI Number], dbo_SERVICE_TAXI_VIEW.Vessel, dbo_SERVICE_TAXI_VIEW.[Voyage Number], dbo_SERVICE_TAXI_VIEW.serviceDate, dbo_SERVICE_TAXI_VIEW.origin, dbo_SERVICE_TAXI_VIEW.destination, dbo_SERVICE_TAXI_VIEW.crews, dbo_SERVICE_TAXI_VIEW.rideComments, dbo_SERVICE_TAXI_VIEW.serviceComments, dbo_SERVICE_TAXI_VIEW.[Division code], dbo_SERVICE_TAXI_VIEW.rideDate
FROM dbo_SERVICE_TAXI_VIEW
GROUP BY dbo_SERVICE_TAXI_VIEW.[ID Bitacora], dbo_SERVICE_TAXI_VIEW.[STI Number], dbo_SERVICE_TAXI_VIEW.Vessel, dbo_SERVICE_TAXI_VIEW.[Voyage Number], dbo_SERVICE_TAXI_VIEW.serviceDate, dbo_SERVICE_TAXI_VIEW.origin, dbo_SERVICE_TAXI_VIEW.destination, dbo_SERVICE_TAXI_VIEW.crews, dbo_SERVICE_TAXI_VIEW.rideComments, dbo_SERVICE_TAXI_VIEW.serviceComments, dbo_SERVICE_TAXI_VIEW.[Division code], dbo_SERVICE_TAXI_VIEW.rideDate
HAVING ((Not (dbo_SERVICE_TAXI_VIEW.[ID Bitacora]) Is Null) AND ((dbo_SERVICE_TAXI_VIEW.[Division code])="HU") AND ((dbo_SERVICE_TAXI_VIEW.rideDate)=[Forms]![TaxiDate_Search]![Texto57]))
ORDER BY dbo_SERVICE_TAXI_VIEW.[ID Bitacora];

The problem being that the [rideDate] feild holds date and time information, ej; 20/12/2014 09:30:21. In this respect, I have to implement the Cdate function, or similar as the user will only be searching by "dd mm YYYY"

I have tried all ways by I always get a message saying that my code is too complex. Here is a cc of my last intent:

HAVING ((Not (dbo_SERVICE_TAXI_VIEW.[ID Bitacora]) Is Null) AND ((dbo_SERVICE_TAXI_VIEW.[Division code])="HU") AND cDate(Int((dbo_SERVICE_TAXI_VIEW.rideDate)=[Forms]![TaxiDate_Search]![Texto57])))

Can someone please help me out here, i'm kinda going mad with this issue.
Thanks in advance.

JMC

 

[dhookom]

I'm not sure why your query is a totals query when there are no aggregate functions like Sum(), Count(), etc.
I would try:

SELECT [ID Bitacora], [STI Number], Vessel, [Voyage Number], serviceDate, origin, destination,
 crews, rideComments, serviceComments, [Division code], rideDate
FROM dbo_SERVICE_TAXI_VIEW
WHERE [ID Bitacora] Is Not Null AND [Division code]="HU"
   AND DateValue(rideDate)=[Forms]![TaxiDate_Search]![Texto57]
GROUP BY [ID Bitacora], [STI Number], Vessel, [Voyage Number], serviceDate, origin, destination, crews, rideComments, serviceComments, [Division code], rideDate
ORDER BY [ID Bitacora];

You may need to enter the data type of [Forms]![TaxiDate_Search]![Texto57] into the Query Parameters.

I would actually create a pass-through query and then modify its SQL to implement the date filter:

Dim strSQL as String
strSQL = "SELECT [ID Bitacora], [STI Number], Vessel, [Voyage Number], serviceDate, origin, destination, " & _
    "crews, rideComments, serviceComments, [Division code], rideDate " & _
    "FROM SERVICE_TAXI_VIEW " & _
    "WHERE [ID Bitacora] Is Not Null AND [Division code]='HU' "
strSQL = strSQL & " AND Convert(datetime, Convert(varchar(10), RideDate ,120)) = '" & [Forms]![TaxiDate_Search]![Texto57] & "' "
CurrentDb.QueryDefs("NameOfYourPTQuery").SQL = strSQL

Duane
Hook'D on Access
MS Access MVP

 

[jmc014]

Thanks Duane,

With the first code that you quoted, I'm still getting the same message, that the code is incorrect or too complex.
Time to turn off.. I'll have another go tomorow, I think.

Your help is really appreciated.
JMC014

 

[dhookom]

Try set the data type of [Forms]![TaxiDate_Search]![Texto57] in the query parameters. Are you sure it's Texto57 and not Text057?

Do you know every time you use a default name like Text123 you can on Santa's naughty list?

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

dd-mm-yyyy is an ambiguous date structure.

I'd use yyyy-mm-dd which is unambiguous!

 

[dhookom]

Also, CDate() doesn't remove the time portion of a date. DateValue() will remove the time portion.

Duane
Hook'D on Access
MS Access MVP

 

[jmc014]

Thanks for all this info..Even when I have redueced the query to a maximun (trail and error thing), I'm still getting the same error.
What I don´t understand is why it works on my personal computer (Win8 - Access 2007) but not on the system that we have at work (Access 2010)

The code now looks like this:

SELECT dbo_SERVICE_TAXI_VIEW.rideDate
FROM dbo_SERVICE_TAXI_VIEW
WHERE DateValue(rideDate) = [Forms]![TaxiDate_Search]![Texto57];

Since I'm testing directly form the query, the datatype on the form does not play a role! ¿Correct?

Did´nt know I was on Santa's naughty list... Does that mean that all the hard work it's cost me being good has gone through the window?
Jajaja (Don´t answer that)

 

[dhookom]

Try simplify even more:

SELECT dbo_SERVICE_TAXI_VIEW.rideDate
 FROM dbo_SERVICE_TAXI_VIEW
 WHERE DateValue(rideDate) = #11/30/2014#;

If that works, try add the reference to the form control back in but also add it to the parameters property of the query.

There may be an issue with your date format. You might want to look at Allen Browne's International Date page.

Duane
Hook'D on Access
MS Access MVP