Calculate Date in Query

[raytackettsells]

Hi there,



My programming knowledge is noob at best. I already have an expression in the Date Due field to format it correctly. Is there a way to have the date that is already in the table (SHEET1) to be calculated in the Query1 to add ONE day less than the date which is displayed. Sorry for my lack of knowledge but thanks a lot to anyone who helps me. Example

Date Due = 03/26/2012
Calculated Date = 03/25/2012

Thanks again!

Ray

[pbaldy]

You can use the DateAdd() function to subtract a day.

[raytackettsells]

I tried to add it into the syntax and forgive me if it looks wrong now but you get the idea. in the field column there is a format expression MMDDYYYY, and id add that into CRITERIA in the query right like, DateAdd("d",-1,[Date Due]) or is it different because now the field shows as well is exported as Expr1...so should I change it to that? Thanks for the help>

[raytackettsells]

I tried that and it works if I add a separate Due Date to the query so there are two...but I need it not only to calculate the field its in but also change it from that calculated field to MMDDYYYY no dashes. So basically I can get it to remove the dashes and now with your help know how to remove a day but how to get them to calc in the same field. Thanks

[pbaldy]

I'm not clear on the overall goal. If you just want that value for display, you'd have that in the Field pot, not the Criteria. Access will alias it to Expr1, which you cn change to something more descriptive.

[raytackettsells]

the goal is its imported from excel and is like 03/22/2012 I need to export to text and have it only be 8 bytes so i can do it inside the query using that format expression but dont know how to also have it calc and leave in that format...

[pbaldy]

You should be able to combine them:

Format(DateAdd("d",-1,[Date Due]), "MMDDYYYY")

[raytackettsells]

Worked like a charm! Thanks a lot! Its awesome I always think that there is a platform like this where people like yourself are willing to be so helpful.

[pbaldy]

Happy to help!