473,320 Members | 2,020 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,320 software developers and data experts.

Issues with the Between function in a query

2
I have an SMS database that keeps track of any Safety Issues within the company. The query that I have now allows me to select all records for a certain month and year. What I want to be able to do is select all records for a certain month and year and then compare them to all records for a certain month and year.
Eg. Bring up all records for March 2007 and then all records for March 2008.

The between function doesn't work, and I don't know what function I should be using instead. What I have so far is:

SELECT Table1.[Report Number], Table1.[Report Type], Table1.Date,
Table1.[Aircraft Type], Table1.[Aircraft Registration], Table1.[Specific
Issue], Table1.Consequence, Table1.Employee, Table1.Base, Table1.Category
FROM Table1
WHERE (((Month([Date]))=[Choose a month from 1-12]) AND
((Year([Date]))=[Choose a year]));

Any suggestions?
Oct 16 '08 #1
3 1716
GazMathias
228 Expert 128KB
Expand|Select|Wrap|Line Numbers
  1. WHERE (((Table1.Date) Like "*08/2008")) OR (((Table1.Date) Like "*08/2007"));
If you are leaving some [Choose Date] type message in the criteria intentionally so that boxes pop up, ensure the users enter the date as *mm/yyyy, like above.

Better way is to set up a form with some unbound text boxes on, so that you can validate the user input (and maybe even delete and replace the queryDefs at runtime). You can also put controls on the form to reference in code that it allows the user to output to Excel, the screen, HTML, etc.

Gaz.
Oct 17 '08 #2
JCM82
2
Thanks,

Still can't get the query to return the second set of results though.

Anyone else have any thoughts?
Oct 22 '08 #3
ADezii
8,834 Expert 8TB
I have an SMS database that keeps track of any Safety Issues within the company. The query that I have now allows me to select all records for a certain month and year. What I want to be able to do is select all records for a certain month and year and then compare them to all records for a certain month and year.
Eg. Bring up all records for March 2007 and then all records for March 2008.

The between function doesn't work, and I don't know what function I should be using instead. What I have so far is:

SELECT Table1.[Report Number], Table1.[Report Type], Table1.Date,
Table1.[Aircraft Type], Table1.[Aircraft Registration], Table1.[Specific
Issue], Table1.Consequence, Table1.Employee, Table1.Base, Table1.Category
FROM Table1
WHERE (((Month([Date]))=[Choose a month from 1-12]) AND
((Year([Date]))=[Choose a year]));

Any suggestions?
I did it successfully on the Employees Table of the Northwind Database, just make your appropriate substitutions, and you should be OK:
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM Employees
  3. WHERE (Month([Birthdate]) = [Enter 1st Month] And Year([BirthDate]) = [Enter 1st Year]) Or  
  4. (Month([Birthdate]) = [Enter 2nd Month] And Year([BirthDate]) = [Enter 2nd Year])
Oct 23 '08 #4

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

Similar topics

5
by: sandy | last post by:
Hi All, I am a newbie to MySQL and Python. At the first place, I would like to know what are the general performance issues (if any) of using MySQL with Python. By performance, I wanted to...
7
by: Julia Baresch | last post by:
Hi everyone, My company recently upgraded from Office 97 to Office XP. As those who've read my other posts know I have a database with 3 nested subforms Main form-->First Subform-->Second...
2
by: Lynn N. | last post by:
I have a report showing Rate, Hours and Total Pay (which is Rate*Hours) for several workers. I want to sum the Total Pay and get a CORRECT figure. This seems like it should be such a simple task....
4
by: Smriti Dev | last post by:
Hi There, I'm creating a MS ACCESS database using Ms Access 2000 to store Interpretation requests by different departments in a hospital and Interpreter availability. All internal departments...
5
by: elitecodex | last post by:
Hey everyone. I have this query select * from `TableName` where `SomeIDField` 0 I can open a mysql command prompt and execute this command with no issues. However, Im trying to issue the...
5
by: loveshack | last post by:
Can anyone help me please (i am quite a novice, but having fun learning). Im not sure if this is an ASP problem, a javascript problem or a browser problem. Firstly, everything i have written...
4
by: Adam1331 | last post by:
I've been trying to get the DSum function to work but I've been running into some roadblocks. What I'm trying to do is to total up times in a timesheet report into different departments. The report...
6
by: BOMEz | last post by:
So i've recently been starting to program PHP in an object oriented way, but I'm running into some difficulties in from a design stand point and from an object oriented stand point: Issue 1: In my...
7
by: SteveGod | last post by:
Hi, I'm trying to create a report that will produce automated sets of Committee Minutes for School Appeals, where there are a set range of different outcomes. The field involved are all from...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
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...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
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

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.