Query criteria: 3 days ago and older

Universal

New Member
Joined
Feb 10, 2004
Messages
8
Hello

I am querying off of multiple tables and I would like to create/save a query in which one of the criterion is that a date value in a column is from 3 days ago or earlier. Here is the fun part: The date values in that column are actually not formatted as a date. An example for today's date would be 20040210. Does this have to be converted or something? If so, after it's converted.....can the filter be executed with a function (similar to Excel) like "TODAY()-3", or does this have to be done with VB code? Either way I am lost with this one. Can somebody help me out with this?

Thanks,
Nick
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

bat17

Well-known Member
Joined
Aug 15, 2003
Messages
1,470
I think that your best bet is to add a new column to the query and convert the date field there with something like:-
RealDate:=CDate(Right([MyDate],2) & "/" & Mid([MyDate],5,2) & "/" & Left([MyDate],4))

Then you can use your criteria =Date()-3 in the criteria field of your new column

Peter

Ps Welcome to the Board :p
 

Universal

New Member
Joined
Feb 10, 2004
Messages
8
I did as you suggested. When I try to tun the query, a dialogue box appears that reads, "Enter Parameter Value" at the top and has an input field that reads "My Date:". Any other suggestions???

Thanks,
Nick
 

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
Try agian:

use:

Right([Date],2)<Day(Date())-2

Change [Date] to the name of the Field you put the Criteria in.

hope this helps
 

bat17

Well-known Member
Joined
Aug 15, 2003
Messages
1,470
The [MyDate] in my reply was meant to indicate the name of your field that has the date data as a text string. you need to substitue with the correct field name :)

Peter
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,157
Messages
5,768,518
Members
425,479
Latest member
Neerajcool

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top