Query criteria help?

[gmatriix]

Hello All,

Trying to use some criteria in a query that is giving me a error.

When I put this in the query

>DateAdd("yyyy",-1,Date())

I get an error.....what I am trying to get is: "One year ago today" for the criteria.....

So whenever the query runs, the criteria will be that day a year ago.

Is there a better way to get he same result?

Any Ideas?

 

[Minty]

You appear to have two greater than signs ?

> DateAdd("yyyy", -1, Date())

 

[gmatriix]

Sorry, that was a typo.....The extra> was not in the query....I did that when I have typing the question....

Any Ideas?

 

[pbaldy]

What error do you get? There's nothing wrong with the syntax, other than the typo.

 

[gmatriix]

Here is the error I am getting..

 

[pbaldy]

Are you sure the data type of the field is date/time?

 

[gmatriix]

Good point!

the field type is originally number...and it is formatted like "20070325" as a date...I don't know why they did it that way...

So that is most like what is causing the trouble...so I am looking possibly doing a date format for "yyyy","mm","dd"

DateAdd("yyyy",-1,Format(Date,"yyyy/mm/dd"))

I haven't tried it yet...

think this might work?

 

[pbaldy]

Format returns a string, but you can try it:

> Format(DateAdd("yyyy", -1, Date()), "yyyymmdd")

if that doesn't work convert it to a number:

> CLng(Format(DateAdd("yyyy", -1, Date()), "yyyymmdd"))

 

[gmatriix]

Bro, you are awesome!!

that worked great!

 

[pbaldy]

Happy to help!

 

[gmatriix]

Yep, I agree....however I am not allowed to changed the data type coming from the companies mainframe.....

I don't understand why they did that?? But your talking about a mainframe system that was created........yeeeeeearrss ago...lol

gotta work with what I got...lol...