Query not working and needs improvement

[Bong]

Hi, below is my query and it's giving me results that are not based on my criteria. I am trying to select all those records from dates and time specified by user. Would appreciate if you have any suggestions or comments with the query that I am using.

SELECT * FROM [Downtime Details]
WHERE

(([Downtime Details]![Downtime Date] >= FORMAT([Forms]![Selection Criteria4-DLG]![FromDate],"dd/mm/yyyy") AND

[Downtime Details]![Downtime Date] <= FORMAT([Forms]![Selection Criteria4-DLG]![ToDate],"dd/mm/yyyy") AND

FORMAT([Downtime Details]![Time Filler Start],"h:n AM/PM") >= FORMAT([Forms]![Selection Criteria4-DLG]![FromTime],"h:n AM/PM") AND

FORMAT([Downtime Details]![Time Filler Start],"h:n AM/PM") <= FORMAT([Forms]![Selection Criteria4-DLG]![ToTime],"h:n AM/PM")));

Many thanks.

 

[ajetrumpet]

SELECT * FROM [Downtime Details]
WHERE

(([Downtime Details]!<---"are you referring to the table here??"[Downtime Date]<---"and the field here??" >= FORMAT([Forms]![Selection Criteria4-DLG]![FromDate],"dd/mm/yyyy") AND

[Downtime Details]![Downtime Date] <= FORMAT([Forms]![Selection Criteria4-DLG]![ToDate],"dd/mm/yyyy") AND

FORMAT([Downtime Details]![Time Filler Start],"h:n AM/PM") >= FORMAT([Forms]![Selection Criteria4-DLG]![FromTime],"h:n AM/PM") AND

FORMAT([Downtime Details]![Time Filler Start],"h:n AM/PM") <= FORMAT([Forms]![Selection Criteria4-DLG]![ToTime],"h:n AM/PM")));

field reference is

.[field], not

![field]

Give more specifics on what you want from this...

 

[GrexP]

What is the data type of [Downtime Date] and why are you formating ToDate and FromDate. Is the user entering a string that looks like a date (e.g. 02/13/2007)? If so, there is no need to format it.

If [Downtime Date] has a date data type, and the user enters in something that looks like a date, your SQL should look something like

SELECT * FROM [Downtime Details]
WHERE (([Downtime Details]![Downtime Date] >= #10/01/2007#) AND
[Downtime Details]![Downtime Date] <= #12/31/2007#)));

View the SQL output. What does it look like.

 

[Bong]

Yes, my table name is Downtime Details and Downtime Date is one of my column on the table.

 

[ajetrumpet]

change the field reference SQL then, and follow GrexP's instructions. He's got it right on....

 

[Jon K]

below is my query and it's giving me results that are not based on my criteria.


SELECT * FROM [Downtime Details]
WHERE
(([Downtime Details]![Downtime Date] >= FORMAT([Forms]![Selection Criteria4-DLG]![FromDate],"dd/mm/yyyy") AND
[Downtime Details]![Downtime Date] <= FORMAT([Forms]![Selection Criteria4-DLG]![ToDate],"dd/mm/yyyy") AND
FORMAT([Downtime Details]![Time Filler Start],"h:n AM/PM") >= FORMAT([Forms]![Selection Criteria4-DLG]![FromTime],"h:n AM/PM") AND
FORMAT([Downtime Details]![Time Filler Start],"h:n AM/PM") <= FORMAT([Forms]![Selection Criteria4-DLG]![ToTime],"h:n AM/PM")));

The FORMAT() functions in the query will change the dates and times into text strings and hence return incorrect results.


Assuming you are searching for date and time separately (as suggested by your query), try this query:-

SELECT *
FROM [Downtime Details]
WHERE [Downtime Date] BETWEEN Forms![Selection Criteria4-DLG]!FromDate AND Forms![Selection Criteria4-DLG]!ToDate
And [Time Filler Start] BETWEEN Forms![Selection Criteria4-DLG]!FromTime AND Forms![Selection Criteria4-DLG]!ToTime;


If you are actually searching for a period of time i.e.

[Downtime Date]+[Time Filler Start]
BETWEEN [Forms]![Selection Criteria4-DLG]![FromDate]+[Forms]![Selection Criteria4-DLG]![FromTime]
AND [Forms]![Selection Criteria4-DLG]![ToDate]+[Forms]![Selection Criteria4-DLG]![ToTime]

you need to change the Where Clause in the query accordingly.
.

 

[ajetrumpet]

Bong, per your original code...

SELECT * FROM [Downtime Details]
WHERE

(([Downtime Details]![Downtime Date] >= FORMAT([Forms]![Selection Criteria4-DLG]![FromDate],"dd/mm/yyyy") AND

[Downtime Details]![Downtime Date] <= FORMAT([Forms]![Selection Criteria4-DLG]![ToDate],"dd/mm/yyyy") AND

FORMAT([Downtime Details]![Time Filler Start],"h:n AM/PM") >= FORMAT([Forms]![Selection Criteria4-DLG]![FromTime],"h:n AM/PM") AND

FORMAT([Downtime Details]![Time Filler Start],"h:n AM/PM") <= FORMAT([Forms]![Selection Criteria4-DLG]![ToTime],"h:n AM/PM")));

This is going to return every record that will have a downtime date >= (FROMDATE) and <= (TODATE), but it will also only return those "downtime" records that are ONLY between the (FROMTIME) and (TOTIME). Thus, if you're user's criteria was between "1/1/2007, 2:00PM" and "5/5/2007, 9:00PM", and you have got dates and times listed in two fields....you would get a record that looked like this...[field], [field], 3/2/2007, 5:30PM, but you would not get one that looks like this (even though it is between the dates the user specified)...[field], [field], 4/12/2007, 10:00PM (10:00PM is outside of the criteria of (2-9PM)

I am guessing your user will be entering the date and time into the controls and you want any record to pull up that is between those two "points" in time (like the example in the above paragraph). Correct?? If that is so, I am not sure about doing this with one field of date and one field of time. I would try to query using a long date or the general date (setting a field in the table for this format)....one in which you can enter the day and time in one long string. It would make it easier for you too....

 

[Jon K]

BTW, when we use the Format() function on a Date/Time data value, what is returned is a text string. To compare these text strings with >= or <= correctly, we need to Format() the dates with "yyyymmdd", the times with "hhnnss", and a date and a time together with "yyyymmddhhnnss" or "yyyymmdd hhnnss". An example:-

Format([Date A], "yyyymmdd") >= Format([Date B], "yyyymmdd") AND
Format([Date A], "yyyymmdd") <= Format([Date C], "yyyymmdd")

In fact we can do the same without involving the Format() function, just simply using
[Date A] >= [Date B] AND [Date A] <= [Date C]

but Between ... And ... is more efficient than >= and <=.


Using [Date]+[Time]
Date/Time data type is internally a double precision number with the integer portion representing the day and the decimal portion representing the time. So we can combine a date and a time with the + sign as if we are doing simple arithmetic.
.

 

[Bong]

Hi all,

Many thanks for your reply to my quote. I have attached picture of the form that I am using and I do require the user to specify the date & time for the report that they would like to run or generate. I am using activex controls for the date and time.

Using the query that I have posted, it selects from my table [Downtime Details] where the:

[From Date] & [To Date] looks at column [Downtime Date]
[From Time] looks at [Time Filler Stop] column
[To Time] looks at [Time Filler Start] column

 

[Bong]

Is there a way that I can display the query with the criteria that it is trying to select from my table. I am thinking of coding it thru VBA in one of the event when user clicks on the "OK" button.