Limit Date to Month 2

[spongie1]

I have a field in a query that is filled with values formated like this:

format(mDate,"mmm")

where mDate is a date.


I want to Limit the values to only "Jan". If I put "Jan" in the criteria section I get a type mismatch error. I understand why, but what can I put in the criteria section of the query to get my desired result?

 

[mgolla]

What is the value of mDate?

Is it a text field, date/time field?

Need more info to help out.

HTH
Mike

 

[Vie]

Try the criteria argument in your query as:

Like "1/?/???"

Your table Date field can be date/time but doesn't need the mmm format explicitly set there. Set the format mmm in the Date column of your query.

 

[Vie]

Oops. Actually that's four ?'s for the year, not three.

 

[TonyJollans]

Hi spongie1,

You should be able to enter "Jan" in the criteria. Can you post a few more details, please?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[r937]

use

month(mDate) = 1

rudy

http://r937.com/

SQL Consulting

 

[spongie1]

I have been out of town. I will look into all suggestions and respond with a detailed email.

If I remember right, I build the date field from 3 separate columns in an excel spreadsheet linked to the database. (I do not have control over the excel spreadsheet.) I think I use dateserial(year, month, day)

I just tested TonyJollans advice using a query on a table created in access (not linked from excel) and "Jan" does work in the criteria section... unlike the version linked from the spreadsheet which do not work.

I also tested r937's advice in the same query and month(mDate) = 1 works.

For some reason these are not working with the linked spreadsheet data even though the output looks the same.

I will give a better response after work tomorrow.

 

[spongie1]

Ok, here is what is happening...

I link an excel spreadsheet that someone else generates into an access database using the import... link option.

The date exists in 3 columns in the spreadsheet (year, month, day). Each column is formated as a number in the excel spreadsheet.

I believe they are number/double in the access database.

I can use dateserial(year, month, day) to make something that looks like the correct date, but when I try to limit the data using the criteria field I get a type mismatch error.

However, the same suggestions provided by r937 and TonyJollans work when I am not using the excel spreadsheet as the data source.

Is there anyway that I can perform the necessary typescasting?

Also, have I correctly characterized the problem. I believe that this is what is happening, but until I fix the problem... I am not 100% sure that this is what is happening.

So to answer mgolla's question... I am not 100% sure what type mDate is... I believe that it is double.