A query with multiple discrete dates.

[angelstrumpet99]

Hello,
I am rather new to Access and I have a question. I would like to run a query that gives me the needed information only on specific dates. The database is composed of oil well data on a daily basis. What I need to do is to run a report for multiple dates. I need to cherry pick those dates. I don't need a range of dates. I need specific discrete dates. e.g. 5/29/2012 6/14/2012 7/2/2012 8/14/2012. A Range of dates looks easy to write but I need to tell access that I only want information for specific days. What would be an easy fix for this? I am a little familiar with VBA so you can make it technical as you want. Can anyone help me?

[NeoPa]

Love the ID :-)

I suspect you're looking for the In() clause in SQL. Be careful always to formulate the dates correctly in your list (Literal DateTimes and Their Delimiters (#)).

[angelstrumpet99]

Thank you for the hint of the In() clause. Since dates are pretty squirrely on Access I changed the field to "short text" This solved my problem in another query I was doing a while ago. Anyway here is my script

(SELECT [Production] 
FROM [All Wells History2]
WHERE [Production] IN('5/29/2012','6/14/2012'))

When I ran the query I got the message "At most one record can be returned with this subquery" Can you give me anymore hints? Production is what I called the date column when I imported the data.

Thank you for you hint

[GKJR]

You can enter multiple different criteria for any field in a query. That is why there are so many rows going down the list below the 'Show' check box. I definitely wouldn't change the date to text because that eliminates the value aspect of the date. Try something like this:

SELECT [Production]
FROM [All Wells History2]
WHERE ((Production)=#9/10/2014#) OR ((Production)=#10/14/2014#);

After you enter that, switch back to the query design grid to see how simple it is to enter multiple criteria for any given field.
This is an interesting topic because you can combine multiple different criteria across any combination of fields. As an example for this application, you could pull up the production from one well in January and a different well in June.

[Rabbit]

A subquery in the select clause or used in an equivalency comparison can only return one value, otherwise it won't have any idea what value to use. To give a more specific answer we would need to see the whole sql.

I don't know what you mean by dates being "squirrelly ". I would caution you against using text to represent dates because that can cause it's own set of issues which tend to outweigh any I can think of if you used a date type instead.

[NeoPa]

@AngelStrumpet.

Your current situation is causing you to include multiple problems into the same question. That isn't allowed (assuming you even knew you were doing it of course), but more importantly it won't help clarify matters for you to throw everything in together.

I suggest you take the SQL aside and test it separately, and not as part of a more complex structure whose details we're not privvy to and which we don't want to care about while dealing with the current issue. How it may fit into your more-complex query is a question for a separate thread when, and only when, this primary issue is resolved and clearly understood. So, let's work with the subquery as a stand-alone item in its own right for now. I suspect when brought into line with my earlier suggestions you'll find it works perfectly.

AngelStrumpet:
Since dates are pretty squirrely on Access I changed the field to "short text".

That would be highly non-recommended. Dates are not remotely squirrely. They are perfectly logical and can work well for you if you use them correctly. That was the purpose of the link I included in my post #2. If you read and fully understand the points contained within that article you will find Dates both reliable and powerful tools to work with.

Having said that, converting dates to strings, or even testing them against strings without even first converting them, can be highly unreliable and should be avoided where at all possible.

[angelstrumpet99]

Thank you everyone for your replies to this thread. I finally got it to work. I really really appreciate the time that everyone put in to answer this question. I didn't get a chance to work on my access again till today and I re-read all of the responses and got it work.

[NeoPa]

AngelStrumpet:
I re-read all of the responses and got it work.

Good for you. Progress is always good. Especially when it's progress in understanding.