Query criteria help? (1 Viewer)

[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!

 

[Pat Hartman]

If you want to work with this field as a date, it would be best to store it as a DateTime data type. Otherwise, you will be constantly converting it.

 

[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...

 

[Pat Hartman]

You don't have to change the source data type, you have to change the data type in your own table. If you have to export the data back to the mainframe, simply reformat it in the export query.

There are many legacy mainframe systems where data types are essentially strings. Storing a date in yyyymmdd order allows for comparing and sorting date fields into what humans would consuder date order. Relational databases use a numeric data type to hold dates. They all use an origin date of 0 but different systems assume different dates to be the 0 date. For example, Access assumes 12/30/1899 for the 0 date. dates less than that are negative numbers so 12/29/1899 is -1 and 12/31/1899 is 1. The decimal part of the date is elapsed time since midnight so .25 is 1/4 of a day or 6 AM Using the RDBMS system allows for easy date arithmetic. The date is converted to a human readable format for display based either on how you specifically format the date or based on the default Windows date setting for your computer.

Today is:
print now()
6/4/2018 4:34:47 PM <<<<< this is how the date is printed based on my default Windows settings (standard US format with AM/PM rather than 24 hour clock)
print cdbl(#6/4/2018 4:34:47 PM#)
43255.6908217593 <<<< this is how the date is stored internally. Notice that we are almost 3/4 of the way through the day.

print cdbl(#6/4/2018 6:00:00 PM#)
43255.75 <<<< 3/4 of the way through the day