Update Query Involving Date

[minii]

Hey, So basically I have a update query whereby I have a stored date that I want to compare against today's date and if it's smaller, then update some fields. Now I want it to actually be a month later than the stored date so something like this: [StoredDate]+31 < Date() then... blabla. For some reason though, no matter how I try to interpet that in Access it won't work. Anyone want to shed some light on it for me? Many thanks.

 

[vbaInet]

Look at the DateAdd() function:

http://www.techonthenet.com/access/functions/date/dateadd.php

You will use the "m" value.

 

[minii]

Thanks for your reply. The thing with using that is I need to use a stored date and that's going to change once a month. So can I do the DateAdd and use a field? So DateAdd("m",1,[StoredDate]) with the criteria of

 

[vbaInet]

Have you tried it?

 

[minii]

Yes, sorry. For some reason that post above decided to cut half of what I said off. I tried it and it did not update the necessary fields implying it didn't work. To be clear about exactly what I have in the query, I have attached a picture. Thanks again.

 

[vbaInet]

When you open the query as Datasheet does it display records?

 

[minii]

Nopes, I guess that's not good.

 

[vbaInet]

Well, there's your problem. Manually go through your records and see if any of them match your criteria.

You can drop the DateAdd() function in a new column in a SELECT query for cross referencing.

 

[minii]

There is only one row for it to look at in that table though. There are three fields, two yes/no fields and the ReportDate field which currently contains the date of 1/10/2011, this means it falls into the correct criteria for the update query to work... well it should lol. I attached a picture as clarification again.

 

[vbaInet]

Does the Date field have a Time part to it? Did you Format the field?

 

[minii]

It's entered manually and set to use short date, so it shouldn't have a time part to it. Sorry for the late reply.

 

[vbaInet]

I don't see why it shouldn't work. Is the data type of the field Date/Time?

 

[minii]

Yeah it is set to date/time. Please find attached a screenshot showing how it's setup.

 

[vbaInet]

Alright. Upload your db so I can see what you've done.

 

[minii]

As requested, please find the link below. Many thanks http://dl.dropbox.com/u/9551747/StockTest.accdb

 

[vbaInet]

Two things:

1. ReportID in tblReports doesn't have a related value in tblStocks.
2. The criteria should be <Month(Date()) instead of just Date()

 

[minii]

Ahh of course! I will make the necessary changes. I actually noticed while having a play that it works fine if I take out the totalused + used part of the query. I'll give it a try and see what it does, thanks buddy. EDIT: Works fine now, thank you. I'm so silly for missing this lol.

 

[vbaInet]

No problemo!