date values in query criteria

[Jeff31592]

I have a field that is short date data type and it represents "date of birth". If I want to query all records in the data base that have persons born in October, what will my statement look like in the criteria line (in query design view) for the field 'DOB'?

 

[Beetle]

October of any year?

Where Month(DOB) = 10

 

[Jeff31592]

that does not seem to work.

this statement is going in criteria line of field "DOB" in query design view. "DOB" field is formated short date and has input mask ##/##/####. If I enter your solution Where Month(DOB) = 10 Access first converts to Where Month("DOB") = 10 and upon execution (!) give an error message: data type mismatch in criteria expression.

 

[boblarson]

that does not seem to work.

this statement is going in criteria line of field "DOB" in query design view. "DOB" field is formated short date and has input mask ##/##/####. If I enter your solution Where Month(DOB) = 10 Access first converts to Where Month("DOB") = 10 and upon execution (!) give an error message: data type mismatch in criteria expression.

You didn't understand. He gave you the actual SQL. In the query by example grid, create a new field by typing

BirthMonth: Month ([DOB])

And in the criteria put

10

 

[Jeff31592]

not sure what query by example grid is (not sure Microsoft does either as it does not appear in their help)

this has got to be the easiest thing in the world to do. I have a field with date of birth and I want to query all persons in the database who have a birthday in the month of October

 

[Beetle]

This is the QBE (Query By Example) grid;

Notice that the third field is a calculated field that uses the Month function to return only the Month from that DOB field and in the criteria row of that field is the value 10. This is the QBE display of your Where clause. If you looked at the the actual SQL of this query (right click in the top area of the QBE and click SQL View) it would look like;

SELECT YourTable.FirstName, YourTable.LastName, Month([DOB]) As BirthMonth
FROM YourTable
WHERE Month([DOB]) = 10;

 

[AlexHedley]

You might prefer the term 'Query Builder'

 

[Jeff31592]

that works. I was confused by the "query by example". I was able to do the same task in a more round about way by exporting to Excel, parcing using / as delimiter and bringing a new integer field back into Access called birthmonth. your solution is quicker and less prone to introduce errors into the database.

so that I can learn something additional here. What is the SQL line with all syntax that you would use - i.e. if I switched to SQL view what would my where clause look like?

thanks.

Jeff

 

[Jeff31592]

you are correct that is what I am familiar with. i.e. query builder or just build.

 

[AlexHedley]

Jeff, Beetle answered your WHERE clause question before.

Since you want the month of October you use the Month function which takes a date and returns the Month so your Date Field is DOB so 'Month([DOB])', you wish it to be October which is the 10th month so your WHERE clause is
WHERE Month([DOB])=10.

Both the Query Builder (QBE) was shown as was the above SQL.

 

[boblarson]

not sure what query by example grid is (not sure Microsoft does either as it does not appear in their help)

Just a suggestion - If a term confuses you, and it isn't found in the help file, there is a cool thing available to use - Google Search or Bing Search. It actually comes up with some good answers if you type that term in. I use those all of the time when I'm not sure what someone is saying before I ask further because many times it will give plenty of information which answers the questions.

 

[Jeff31592]

A great suggestion. I will definitely try that when I hit a wall again in the future.

Thanks to all that offered up help. One more word and then I will shut up:

It seems a bit inelegent to have to make a build (query by example) to extract the month component out of a field that is formatted as short date. I would think that there would be a way to query that field and filter out just the records for a particular month. Wildcards? Something like "DOB = 10/##/####" in the criteria line for field DOB. I tried a million or more and could not get anything to work. Food for thought.

Jeff

 

[Beetle]

It seems a bit inelegent to have to make a build (query by example) to extract the month component out of a field that is formatted as short date. I would think that there would be a way to query that field and filter out just the records for a particular month.

I'm not sure I understand your objection here. The QBE is just a visual click and drag (so to speak) way of building a query. Behind the scenes you have a SQL statement being created for you when you use the QBE.

When you want to extract a set of records from a table, then you're going to use a query and you're going to need to apply criteria to that query if you want to limit the results to a particular range of records. That's where the WHERE caluse comes in. As far as the WHERE clause is concerned, I don't see how;

Month([DOB])=10

is any more difficult than;

[DOB]=10/##/#### (assuming it was even possible to do it this way, which it's not).

Here's what the SQL would look like in both cases.

Case 1

SELECT YourTable.FirstName, YourTable.LastName, YourTable.DOB
FROM YourTable
WHERE Month([DOB])=10;


Case 2

SELECT YourTable.FirstName, YourTable.LastName, YourTable.DOB
FROM YourTable
WHERE [DOB]=10/##/####;


In both cases the resulting SQL is the same, except that the former criteria method works and the latter doesn't.

 

[boblarson]

A great suggestion. I will definitely try that when I hit a wall again in the future.

Thanks to all that offered up help. One more word and then I will shut up:

It seems a bit inelegent to have to make a build (query by example) to extract the month component out of a field that is formatted as short date. I would think that there would be a way to query that field and filter out just the records for a particular month. Wildcards? Something like "DOB = 10/##/####" in the criteria line for field DOB. I tried a million or more and could not get anything to work. Food for thought.

Jeff

I agree with Sean (Beetle). What is inelegent about using

Month([DOB]) to get the month and then the 10 for the criteria? Access is not Excel and it uses queries for what it does. Doing what we showed is normal for ANY relational database, so what you're saying is that Oracle, SQL Server, MySQL, etc. are all flawed because you can't do what you want like you can in Excel?

 

[Jeff31592]

Thanks both. I am not explaining myself well. Everything you have said works and completes the task - I was just questioning the efficiency of the process. Your explanations are telling me that it is the way Access works and the way Access was designed. I can certainly accommodate. I will try and look for an example that I can use to illustrate my point, but not until the weekend, this being a work week.

Jeff

 

[Galaxiom]

It can also be done with:

WHERE DOB Like "10*"

However this expression is regionally dependent since dates are actually stored as real numbers and the displayed format is dependent on the regional date settings.

The advantage of this query is that it is entirely SQL with no functions applied.

For finding a month in a dd/mm/yyyy format:

WHERE DOB Like "*/10/*"

Note that it doesn't really care what format is used to display the date because it apparently automatically convert it to the regional short format before applying the criteria.

 

[Jeff31592]

here is a trival database that illustrates my question:

create database dbCarLot
go
Use dbCarLot
go
create table CarInventory
(cinv_Id int primary key, cinv_Make varchar(15) not null, cinv_Model varchar(12) not null, cinv_AquiredDate date not null)
　
insert into CarInventory
values(1, 'Ford', 'F-150', '09/05/2009), (2, 'Ford', 'Mustang', '09/05/2009'), (3, 'Chevy', 'Camaro', '10/20/2002')

select *
from CarInventory
where cinv_AquiredDate Like '%-09-%'

Results yield the two records who have '09' in the first part of Date field, i.e. the two Fords but not the Chevy.

Went right to the field with date data to get the info we were looking for. That is what I wanted to do in Access.

Sorry that I cannot show result set, don't know how to paste screen shots into this forum. you can run the script above and get your own result set to verify.

Jeff