473,322 Members | 1,610 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,322 software developers and data experts.

Filter last 6 months records on a month field ie 12/2013 format

547 512MB
How do one filter on a "month field" ie 12/2013 format when mmddyyyy is not available in a query.
I need to view only the last 6 month records.
Usually i would use this based on dates:
Expand|Select|Wrap|Line Numbers
  1. >=DateAdd("m",-6,Date())
The Date() part is messing me around
Please advise
Feb 4 '14 #1
8 4257
MikeTheBike
639 Expert 512MB
Hi
You could try something like this
Create a field like this
Expand|Select|Wrap|Line Numbers
  1. DateSerial(Right([month field],4),Left([month field],2),1) as MonthDate 
And add a where clause as you indicated ie.
Expand|Select|Wrap|Line Numbers
  1. [MonthDate] >= DateAdd(“m”.-6,Date())
If the [month field] hold lower month like this 2/2014 and not 02/2014 then the Month past of DateSerial should something like this
Expand|Select|Wrap|Line Numbers
  1. Left([month field],Instr([month field],”/”)-1)
Also you may need -7 month to return the requied data?

HTH

MTB
Feb 4 '14 #2
jimatqsi
1,271 Expert 1GB
Your question is not very clear. What is available in the query? Are you saying the query does not have a date field but only a MMYYYY field, (probably in an integer)? If that is the case you could rearrange it to be YYYYMM and compare it to (Year(Date()))*100)+Month(Date().
It would be something like this to get the number of months difference:
Expand|Select|Wrap|Line Numbers
  1. (Year(Date()))*100)+Month(Date() - ((Right(yourdatefield,4)+Cint(yourdatefield/10000))
Jim
Feb 4 '14 #3
neelsfer
547 512MB
Jim, i filtered the query to not display dd/mm/yyyy but ONLY MM/YYYY . I need to display the last 6 months data in this query now. I cannot filter on dd/mm/yyyy as i merged two different dates into one mm/yyyy field, based on criteria that must now be filtered.
Feb 4 '14 #4
NeoPa
32,556 Expert Mod 16PB
Neelsfer:
"How do one filter on a "month field" ie 12/2013 format when mmddyyyy is not available in a query."

I can't work out what you're trying to say. It doesn't make sense as it stands and I have no idea what you mean.
Feb 5 '14 #5
neelsfer
547 512MB
Hi Neopa
perhaps these pics will clarify the issue on how to filter the last 6 months records only
Attached Images
File Type: jpg monthly filter.jpg (51.4 KB, 567 views)
File Type: jpg filter month field.jpg (69.4 KB, 509 views)
File Type: jpg monthly filtering.jpg (22.9 KB, 406 views)
Feb 5 '14 #6
NeoPa
32,556 Expert Mod 16PB
I'm afraid they don't Neels. Is there any reason you cannot express your question in words that make sense together?
Feb 6 '14 #7
mcupito
294 256MB
In your initial post, you might need to make sure the formats that are comparing the 2 dates are the same.

This might be a helpful link --> http://office.microsoft.com/en-us/ac...010078108.aspx

Edit: NeoPa is correct. Date() does return a Date/Time value. You might want to try a Format() on the Date() to match your criterion. You are trying to compare that to a custom mask MM/YYYY. Hope this helps.

NeoPa : I was not trying to mislead the OP, however convey to him that the format is different. You are indeed correct. I should have told him what you mentioned. Thanks for the correction.
Feb 6 '14 #8
NeoPa
32,556 Expert Mod 16PB
MCupito:
"Date() returns a short date."

I'm sorry - that's just wrong. Date() returns a Date/Time value - not any type of string. How it's formatted when displayed is not what it is, but simply how it's formatted.

It may well be that trying to do a comparison using SQL and getting the formats wrong (so they don't match) is what's getting Neels confused. I'll have to wait until his question makes sense before I can help further.
Feb 7 '14 #9

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

Similar topics

3
by: Ricardo | last post by:
Hi all, I am trying to come up with a sql query (just one query) that returns all the columns for the last N records that were inserted into a table. I have a column in the table that has the...
20
by: Guru | last post by:
Hi I have a table which contains number of rows. I want to fetch the last 5 records from the table. I know for the first 'n' records we can use FETCH FIRST n ROWS ONLY command. But i want to...
4
by: S. van Beek | last post by:
Dear reader, By a Date field with Now() as default value the content of the field is date plus time. As I need a filter in a query on date only (excluding time) I invented the following...
5
by: hunt4grouse | last post by:
I'm looking for C code that will give me the last day of the month. Input - any day that I pass in as a variable.... input = 20050201 Output - calculate and format with slashes.... output =...
1
by: Ronniesss1 | last post by:
I have a table called RescheduleIns and another called Shop Complete Table that are opened together on another form called fAll Shop Orders. On this form, all the Shop Complete Table data is...
6
by: Christo | last post by:
I have this script for showing news on a page, but i want it to only show the last 10 records, as in the 10 records that were added to the database last. the script shows the entries in descending...
7
by: djpaul | last post by:
Hello! I want to check if a record exists in the last 30 records that were added. For example, i have a table wich contains about 34000 records by now. But i want to check if the songID is in the...
7
by: munkee | last post by:
Hi all, I am using Allen's excellent form filter script to filter the results from a query. I would now like to add some further functionality. How do I go about displaying say the top N costs...
4
by: ndeeley | last post by:
Hello, This is going to sound strange but I have a webpage that displays a form showing the last record added. This is because further records share a lot of the same data, so only a few fields...
69
by: moishy101 | last post by:
How can I retrieve the last 3 records from the following table. The table has the following fields and sample data: ClientID TransactionID TransactionDate 1 6511216 ...
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
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...
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...
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
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.