473,326 Members | 2,147 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

A query with multiple discrete dates.

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?
Sep 2 '14 #1

✓ answered by 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 (#)).

7 1323
NeoPa
32,556 Expert Mod 16PB
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 (#)).
Sep 3 '14 #2
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
Expand|Select|Wrap|Line Numbers
  1. (SELECT [Production] 
  2. FROM [All Wells History2]
  3. 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
Sep 3 '14 #3
GKJR
108 64KB
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Production]
  2. FROM [All Wells History2]
  3. 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.
Sep 3 '14 #4
Rabbit
12,516 Expert Mod 8TB
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.
Sep 3 '14 #5
NeoPa
32,556 Expert Mod 16PB
@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.
Sep 3 '14 #6
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.
Sep 26 '14 #7
NeoPa
32,556 Expert Mod 16PB
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.
Oct 2 '14 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Phantom_guitarist | last post by:
I am trying to write a query (in PHP) which selects from a database all of the items which are in the future. My query is as follows SELECT * FROM news WHERE ((news.date)>$today ORDER BY date ...
3
by: Lapchien | last post by:
done homework on Usenet and still stuck... I have a parameter query that asks the user to input the 'slip reference' based on the accounting period. This has now changed slightly so that the...
3
by: Deano | last post by:
Ta for looking. I am setting this SQL to the recordsource of a report but it's complaining about a syntax error. "SELECT tblemp.Surname, tblemp.Forename, tblAbsence.AbsenceDate,...
5
by: Irfan | last post by:
Hi All, I am trying to create a report but having problem with the critiera selection logic, please help. I have the following fields date1 date2 date3
0
by: Greg Strong | last post by:
Hello All, In the past I've used a combo box with the 'row source' being an Access SQL union query to select "All" or 1 for only 1 criteria in a query. An example is as follows: SELECT 0 As...
1
by: dan_williams | last post by:
Is it possible to pass multiple discrete values to a report document so that I can export it to PDF? I've managed to perform the following code to display a Crystal Report Viewer ok, but i want...
0
by: djflow | last post by:
Hi! II was wondering if you can help me with SQL query.. Below 7 separated select query works fine(only when they are retrieved separately) But I want to combined them together and so that i...
5
by: JD | last post by:
Example Give me all the dates between 02/02/07 and 05/02/07 ? In Europe the answer must be 4 dates : 02/02/07 ; 03/02/07 ; 04/02/07 ; 05/02/07. JD.
1
by: DAHMB | last post by:
I have a report based on a query that has two dates that I need information filtered on, and then return the information in the same report. I have a date a case was opened and a date a case was...
1
by: help4me | last post by:
I need to have 3 different dates (with time) saved in a MySql database. I'm using the following: $entered= Date("n-j-Y, g:i a"); $began= Date("n-j-Y, g:i a"); $complete= Date("n-j-Y, g:i a");...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.