Question formatting a date

[xirokx]

here is the expression I am using as per your recommendation:-

DateDiff("d", [insuranceexpirydate], Date()) <= 7

So each time I run the query a box appears asking me to enter the date....

I really want the system to be able to use todays date whatever the date is today / tomorrow / next month etc upon opening the system each time so when I run I the report I can easily view which documents are due to expire in the next 7 days....

Also I noticed an error with this expression, in so much as documents that are due to expire in november are now being highlighted as per my set auto format, and nov is more then 7 days away so they shouldnt be showing up - please clarify??

Here is the database:-
http://www.mediafire.com/?sharekey=c7d7086d742cdaf5c2b435915e8821d7e04e75f6e8ebb871

Please pay attention to the "Driver Documents Section" this is where I want the conditioning and query to work when I run a report or query...It would be handy to have it working even when I am viewing each indiviudal record...

Please advise what expression I should be using...

Finally, as I am very close to completion I am wondering how I can bring the database together so an end user will be able to use the system without amending any of the tables / forms / querys / reports etc?? This is my very first database so I apologise for my newbish ways.... Do people use the switchboard manager as a menu?? if yes, then how can you assign more then 8 buttons to it??

Also do I need to click on ACCDE to lock the database and restrict access? please clarify

Thank you for your continued support and assistance

 

[xirokx]

am so close to the end of completing my very first database, please continue to help and support...

thanks in advance..

 

[pbaldy]

For starters, you need to keep in mind that we're not all in the same time zone. Your initial post 21 had no helpful information in it. You edited it and posted your follow up before I got up this morning.

As I've said, it will not prompt you for today's date. If you would look at the prompt you're getting, it's asking for "insuranceexpirydate", because that's NOT the name of your field.

The switchboard is a matter of personal preference. Some people use it, some don't. I personally don't, so I'm not sure how to get more items, if it's possible at all. I always distribute MDE/ACCDE's to users, but again it's a matter of preference. If you look in help you should see the benefits gained, and you can decide if you want to do it.

 

[xirokx]

i am sorry for being so silly but there is obvisiously something really minor I am doing wrong which is stopping me from acheiving my end result.

Is there any way you could kindly make the required changes on my files and upload it for me? to save us going back and forth.

Having read your reply I changed the 2 field names to the correct field names but a prompt box still appears...

I will use the help section to read more about MDE/ACCDE's...As long as I have a menu system in which the end user can navigate that will be fine. Also I do not want them fiddling with the data and I believe MDE/ACCDE's lock the data....

thanks for your support and assistance

 

[pbaldy]

In the sample you posted, I had to change it in 3 places, not 2.

MDE/ACCDE's do NOT lock the data.

 

[xirokx]

would it be possible for you to upload that database with the changes you made?

I believe I also made the same three changes and it still does not work...

please help

thanks

 

[pbaldy]

I don't have 2007 on this PC so I worked on a different machine with your sample, and I don't have access to that one right now. If you're still getting that prompt, there's a conditional format looking for the bad field name. I'm pretty sure I just changed 3 conditional formats and the report then ran without prompting, and one of the records was red.

 

[xirokx]

hey its working!!! wooohooo

thank you very much....

the dates its showing me are wrong?? they are more then 7 days from 7th July 2009, when I need the system to alert of documents due to expire within 7 days...

the expression I am using is:-

DateDiff("d", [insuranceexpirydate], Date()) <= 7

which I thought would show me any dates that are 7 days after 7th July 09 and that are due to expire within 7 days from 7th July 09

please clarify

thanks

 

[pbaldy]

That's still the bad field name, is it not? I suspect you want those that expire before the 14th, as that's 7 days from today? Try this as the condition:

[Insurance Expiry]<DateAdd("d",7,Date())

 

[xirokx]

that works a treat...

tell me, why when I setup a query and use the same expression, I not only see the documents expired or due to to expire but also see a different set of documents that are still in date?

please clarify..

thank you so much for your help...

 

[xirokx]

ignore me I just answered my own question.....

tell me how can I learn these expressions? they are so handy and not so difficult but I really need a starting point - do you have any tips please?

thanks in advance...

cheers