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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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
Back
Top