Help me with m$ access

[Fat Tony!]

I have to do a ICT Project on M$ access but i dont know how exactly, please help. I need to do a query or something like that for the field "Date Book due", I need to add a month onto the date which is in field "Date Book Borrowed" but if the date is on a saturday or a sunday i need to add 2/1 respectivly to make it a monday instead. understand? What forumula would i need?

 

[Gorgon]

LOL, well its easy Dude. I can help you but its going to take a lot of writing.

A SECOND WAY to do it is : visit www.google.com then type Access version (version such as xp, 2000...etc) and type tutorial

Moreover you can use the help options writtin inside the Access itself.

 

[Fat Tony!]

Unlikely, access help sucks. I somehow dont think any sites gonna have this specific problem on it

 

[ComradeBadger]

Moved.



Have you tried the help files actually yet, or searched? Hm Ben?

 

[Fat Tony!]

Course i have

 

[ComradeBadger]

Just checking mate, I know what you're like

 

[RandomPING]

Gorgon was talking about looking for a tutorial online. Those are usally made seperate from microsoft, just people trying to help other people with step by step instructions. The help does suck, but an online tutorial might be exactly what you need.

 

[jonbob]

It should be quite easy, but alas, I can't figure out how you might get the program to decide if the data borrowed was a saturday, sunday or just a normal day...

 

[lePobz]

You need help with access? you should try creating, maintaining and querying (over 20) databases in raw SQL... that's one of my modules' coursework. Not simple queries, either. 10+ line complex ones.

Lots and lots of headache. ;(

I wish I could use access instead, lol

 

[Fat Tony!]

It should be quite easy, but alas, I can't figure out how you might get the program to decide if the data borrowed was a saturday, sunday or just a normal day...

yes thats the hard bit. I tried using the "weekday" function and the "IF" and "dateadd" in conjunction but it keeps getting syntax errors ;(. We never learnt how to do this how am i meant to do it, plus the tutorials on the web suck and dont help me at all. Just tell me damn it ;(.

 

[jonbob]

I think I have the solution for you; paste this into the Query field:

IIf((WEEKDAY([DateBorrowed]+30)=7),[DateBorrowed]+32,(IIf((WEEKDAY([DateBorrowed]+30)=1),[DateBorrowed]+31,[DateBorrowed]+30)))

I've tested it somewhat and it appears to work. Bear in mind though, that for me a month is classified as 30 calender days. It should be possible to set it up so that the rental period is different depending on which month DateBorrowed falls in, using a triple nested IIf statement. Tell me if you want that doing too.

 

[Fat Tony!]

Explain exactly what you have done please i think you've hit it on the nail . P.s thank you!
/me hugs jonbob

If i displace "[datebook due] +30" with dateadd("m",1,[datebook due]) i think it should work :thumbs:

 

[Fat Tony!]

Hows this:
IIf((WEEKDAY(DateAdd("m",1,[DateBorrowed)=7),DateAdd("m",1,[DateBorrowed)+2,(IIf((WEEKDAY(DateAdd("m",1,[DateBorrowed)=1),DateAdd("m",1,[DateBorrowed)+1,[DateBorrowed]+30))))

 

[jonbob]

I might suggest an amendment to that:

IIf((WEEKDAY(DateAdd("m",1,[DateBorrowed]))=1),(DateAdd("m",1,[DateBorrowed])+1),(IIf((WEEKDAY(DateAdd("m",1,[DateBorrowed]))=7),(DateAdd("m",1,[DateBorrowed])+2),(IIf((WEEKDAY(DateAdd("m",1,[DateBorrowed]))=6),(DateAdd("m",1,[DateBorrowed])+3),(DateAdd("m",1,[DateBorrowed])))))))