Problem with dates in parameter query . . . (1 Viewer)

[AccessUser29]

hello,


I have a simple database that has a date field to track the time in which a lab test is performed. The date format is as follows:

------------------------------
8/20/2002 12:31:28 PM
-----------------------------

Now to retrive queries by a date range, I am using this query:

---------------------------------------
SELECT CoA.Test_Number, CoA.CoA_Date, CoA.[Product#], CoA.DateCode, CoA.TName, CoA.PH, CoA.Water_Activity, CoA.Moisture, CoA.Peanut_Protein, CoA.Egg_Protein, CoA.Milk_Protein, CoA.Vomitoxin, CoA.Aflatoxin

FROM CoA

WHERE CoA.CoA_Date Between [Start Date] And [End Date];
---------------------------------------

For the most part the query works fine, but it cannot pick up records that are at the final date of the range.


For example,

A range of between 8/15/2002 and 8/20/2002 will miss records that have an 8/20/2002 date & time stamp.


A range of between 8/20/2002 and 8/20/2002 will bring up an empty set even if there are records for that day.


I have a feeling that the problem might lie in the fact that Access is interpreting an inputed parameter of "8/20/2002" as "8/20/2002 12:00:00 AM". Is there a way for my query to ignore the time portion of the field?


thanks


AccessUser29

 

[thouston]

You're right - it is the time stamp that's causing the problem. Easy to solve though using the function Dateval (which only looks at the date part of the date/time), as in

SELECT blah blah

FROM CoA

WHERE Datevalue([CoA.CoA_Date]) Between [Start Date] And [End Date];

You may need more brackets but that's the general idea.

 

[AccessUser29]

Thanks! That function took care of my problem.



AccessUser29

 

[Groundrush]

Same problem, but can't resolve

Hi

Finally found someone who had the same problem

I tried thouston's solution, but it says that the expression is typed incorrectley, or it is too complex to be evaluated.


the qry did work when I used
Between [Start Date] And [End Date]
but it also left out the entries on the final date range
(same problem as AccessUser29 had)

using your example the query now looks like this:

SELECT dbo_F_TASK_TIME.TT_ID, dbo_F_TASK_TIME.TT_NAME, dbo_F_TASK_TIME.TT_LAB_DESC, Sum(dbo_F_TASK_TIME.TT_NORM_TIME) AS SumOfTT_NORM_TIME, Sum(dbo_F_TASK_TIME.TT_OVR_TIME1) AS SumOfTT_OVR_TIME1, Sum(dbo_F_TASK_TIME.TT_OVR_TIME2) AS SumOfTT_OVR_TIME2, Sum(dbo_F_TASK_TIME.TT_OVR_TIME3) AS SumOfTT_OVR_TIME3, Sum(dbo_F_TASK_TIME.TT_TRAV_TIME) AS SumOfTT_TRAV_TIME, Sum(dbo_F_TASK_TIME.TT_PARKING_TIME) AS SumOfTT_PARKING_TIME
FROM dbo_F_TASK_TIME INNER JOIN dbo_F_TASKS ON dbo_F_TASK_TIME.TT_FKEY_TA_SEQ = dbo_F_TASKS.TA_SEQ
WHERE (((DateValue([TT_STARTED])) Between ([Start Date]) And ([End Date])))
GROUP BY dbo_F_TASK_TIME.TT_ID, dbo_F_TASK_TIME.TT_NAME, dbo_F_TASK_TIME.TT_LAB_DESC
ORDER BY dbo_F_TASK_TIME.TT_NAME;


can anyone please tell me where I am going wrong

thanks in advance

 

[thouston]

That's funny. I tried your syntax on my test DB and it seems to work fine, even with all the brackets reproduced as you wrote the query. The only thing I didn't test was your join (obviously I don't know what's in your other table).

All I can suggest if you can't make it work is that you do a simple query using only your dbo_F_TASK_TIME table which selects everything straight (no groups or sorts) but uses the DATEVALUE function on the date.

Check that it works and then join that into the other table and use a standard parameter query (without the DATEVALUE part) on the new date field that you created in the query, instead of the original time stamp. Bit clunky - turns a one stage query into two - but it might work (or at least give you an insight into where the underlying problem is).

Good luck!

 

[Groundrush]

I have tried as you suggested, but still no joy

I wonder if part of the problem is that some of my qry's are linked to tables on our odbc server, and your test is based on a table that you created in access with my field names.




thanks for your help

 

[thouston]

The odbc could be part of the problem. What is it linking to (eg. Oracle, what version etc?). At what stage is your query failing, if you split it into 2 parts - the datevalue function or the grouping?

 

[Groundrush]

Right. lets see


This particular server is not local, it's based in another location, but is available on our network

all I know about this sytem that it's not dependant on a dedicated database application
but suppports all major relational database management systems (RDBMS) including Oracle and Mocrosoft SQL Server

(that info was taken from our manual)

The qry fails at the first stage when I use:
WHERE (((DateValue([TT_STARTED])) Between ([Start Date]) And ([End Date])))

it does not fail if I use:
HAVING (((dbo_F_TASK_TIME.TT_STARTED) Between [Start Date] And [End Date]))

but as mentioned before it leaves out data on the final date range.

not sure if that makes it any clearer to understand

thanks for your help

 

[thouston]

Hmm, could be that your remote server doesn't support exactly the same SQL syntax, or has a few cunning differences. It seems that it doesn't recognise the "datevalue" command. Check to see whether there are any synonyms that might work. Otherwise you might have to do something clever like add a day on to your end date so that it gets everything up to midnight.

If I can think of anything elegant I'll post it. Sorry not to be more useful...

 

[Groundrush]

Otherwise you might have to do something clever like add a day on to your end date so that it gets everything up to midnight.

Now that rings a bell

I contacted the company that created this system and they told me that what they suggested was to
add a day to the (to date)

then within the sql WHERE clause
select greater than or equal to (>=) the (from date) and less than (<) THE (To Date)

Any Ideas how I go about attempting that?

 

[thouston]

You'd want syntax that went somthing like this:

SELECT etc etc
FROM dbo_F_TASK_TIME
WHERE (((dbo_F_TASK_TIME.TT_STARTED)>=[start date]) AND ((dbo_F_TASK_TIME.TT_STARTED+1)<=[end date]));

Check out the brackets etc, I haven't tested this!

I reckon that's probably the simplest way around this. Fingers crossed...

 

[Groundrush]

Thanks for that

tried it but it didn't return all 39 hrs

don't know why but the results had different totals, could not find a common reason

I then experimented and changed the STARTED+1 to STARTED-2

and that gave me the results that I was looking for, although it also picked out some records into the next week in a couple of the other fields and gave me some strange amounts in those fields


NORM_TIME- returns all records (39 Hrs)
OVR_TIME1- feeds info from somewhere else
OVR_TIME2- " " " " "
OVR_TIME3- " " " " "
TRAV_TIME- " " " " "
PARKING_TIME " " " " "

This is really confusing now.


Thanks for all your help, I think I'm getting closer.
will keep trying

 

[thouston]

I'm guessing that all those dates might be slightly differently specified in the underlying database (eg some date/time, some just dates perhaps). If you can easily lay hands on the info, it might be helpful to get the data type specs. Otherwise I suspect you'll get there pretty quickly by trial and error now you've at least got a method that returns some data! (remember you can also play around with < vs <= in the criteria).

 

[Groundrush]

Thouston we have a problem

Like my Subjet heading or is that an old one?.

Thank you very much for your time, I will keep trying

Regards

 

[Groundrush]

Found a solution

Thought you might like to know I have a solution
it may not be the best method, but it works

qry now looks like this:

SELECT dbo_F_TASK_TIME.TT_ID, dbo_F_TASK_TIME.TT_NAME, dbo_F_TASK_TIME.TT_LAB_DESC, Sum(dbo_F_TASK_TIME.TT_NORM_TIME) AS SumOfTT_NORM_TIME, Sum(dbo_F_TASK_TIME.TT_OVR_TIME1) AS SumOfTT_OVR_TIME1, Sum(dbo_F_TASK_TIME.TT_OVR_TIME2) AS SumOfTT_OVR_TIME2, Sum(dbo_F_TASK_TIME.TT_OVR_TIME3) AS SumOfTT_OVR_TIME3, Sum(dbo_F_TASK_TIME.TT_TRAV_TIME) AS SumOfTT_TRAV_TIME, Sum(dbo_F_TASK_TIME.TT_PARKING_TIME) AS SumOfTT_PARKING_TIME
FROM dbo_F_TASK_TIME INNER JOIN dbo_F_TASKS ON dbo_F_TASK_TIME.TT_FKEY_TA_SEQ = dbo_F_TASKS.TA_SEQ
WHERE (((dbo_F_TASK_TIME.TT_STARTED)>=[Start Date] And (dbo_F_TASK_TIME.TT_STARTED)<[end date plus 1 day]))
GROUP BY dbo_F_TASK_TIME.TT_ID, dbo_F_TASK_TIME.TT_NAME, dbo_F_TASK_TIME.TT_LAB_DESC
ORDER BY dbo_F_TASK_TIME.TT_NAME;

the only drawback is that, I am prompted for 3 dates now instead of 2

1-start date
2-End date + 1
3-End Date

Regards