[RESOLVED] MSAccess Dateadd function

[holly]

Hi

I am using MSaccess 2003 and I have a simple table that holds a schedule year...the table has three fields
date crew1 crew2


In my qry I want to display a weeks worth of the 3 columns
for example

date crew1 crew2
01/08/2006 A B
02/08/2006 B A
03/08/2006 C D

THANKS


i'M USING THE DATEADDFUNCTION WITHIN THE QRY'S CRITERIA BUT I'CANT GET IT TO WORK..

DateAdd("dd",7,[date])

pLEASE HELP

[si_the_geek]

Try showing us the full query, so we can understand better.

[holly]

ok..


ITS JUST A SIMPLE QUERY...

TA

[si_the_geek]

Erm.. could you show us the SQL instead please! (very few of us use that kind of interface)

[holly]

this is the sql view

SELECT Date.crew_schedule_date, Date.crew1, Date.crew2
FROM [Date]
WHERE (((Date.crew_schedule_date)=DateAdd("d",7,[crew_schedule_date])));

Thanks

[si_the_geek]

Oooh.. I just noticed that your table is called Date - that is a very bad idea, as it is a reserved word (which I'll actually use below!). It is likely to cause issues for you, and should really be changed to something different (possibly CrewDates).

You haven't specified what week of data you want (such as from/to certain dates, etc), but this will give the week starting today (untested):

SELECT Date.crew_schedule_date, Date.crew1, Date.crew2
FROM [Date]
WHERE (((Date.crew_schedule_date) Between Date And DateAdd("d",7,Date)));

[holly]

I changed the table name as you stated and tried your sql as below but the returned results is only todays date and not a weeks worth.. this is the sql view..

SELECT CrewDate.crew_schedule_date, CrewDate.crew1, CrewDate.crew2
FROM CrewDate
WHERE (((CrewDate.crew_schedule_date) Between [crew_schedule_date] And DateAdd("d",7,[crew_schedule_date])));

Thanks

[LinXG]

Hi Holly;

Based on your original post I assume that you want to be able to look at the crew schedule for a given week. If that is the case then the function you should consider using is DatePart.

I've created a sample query that you can cut and paste into your database. I've changed your table name to CrewSchedule. The query asks you to enter any date in the week and then returns the schedule for that week.

SELECT CrewSchedule.Crew_Schedule_date, CrewSchedule.Crew1, CrewSchedule.Crew2
FROM CrewSchedule
WHERE (((DatePart("ww",[Crew_Schedule_date]))=DatePart("ww",[Enter any date in week])));

I hope this is helpful to you.

[si_the_geek]

I changed the table name as you stated and tried your sql as below

You changed the Where clause, it was actually meant to be Date (which returns the current date) rather than your field (which you were comparing to itself!), ie:

WHERE (((CrewDate.crew_schedule_date) Between Date And DateAdd("d",7,Date)));

However, LinXG's method may well be better for you - it depends how you define a week tho (7 days from today, or from a certain day of the week).

[salvelinus]

Basically, never use reserved words in for table, field, query, etc., names. I deal with a lot of legacy dbs which have a lot of this going on, and it's a pain in the ass.
In Access, you can do this if you enclose the name in brackets, same as you would if you did the also bad practice of putting spaces in names, but it's really a heck of a lot easier to just name things appropriately to start with.
The convention I use is to start all tablenames with tbl (tblFoo), fields with fld (fldFoo), queries with qry (qryFoo), etc. Then you can name a table tblDate, for example, and when you refer to it in code, there'll be no question as to what you're referring to, and no conflict with reserved names.
If you have a query and you don't want the column heading of "fldFoo", in your SELECT statement use "SELECT fldFoo AS IDontWantNamefldFoo FROM tblFoo", or whatever you want the result to be called.
And as si wrote, not many programmers use the Access drag & drop query interface. It's okay if you don't do many queries, and they're simple ones, but SQL view is really the best way to go. Clearer, more control. Also necessary in almost every db outside Access.

[Ecniv]

try the following:

SELECT [Date].[crew_schedule_date], [Date].[crew1], [Date].[crew2]
FROM [Date]
WHERE (((Date.crew_schedule_date) Between now And now+7

I noticed you changed the table name - so reflect that in the above as well. Hope you can get it working.

Note: function now includes time... You may need to use : cdate(format(now,'dd mmm yyyy')) to get just the date.



*edit* NOTE: square brackets put around fields and table names. So if you want the function do not use square brackets.

[holly]

Si,

I changed the SQL back...and now it asks me to input a date...how can I get it to automatically read todays date...I no I can use the Now() or date function..

SQL:
SELECT DMCF_CREW_SCHEDULE_Adm.crew_schedule_date, DMCF_CREW_SCHEDULE_Adm.crew1, DMCF_CREW_SCHEDULE_Adm.crew2
FROM DMCF_CREW_SCHEDULE_Adm
WHERE (((DMCF_CREW_SCHEDULE_Adm.crew_schedule_date) Between [Date] And DateAdd("d",7,[Date])));


Thanks very much!!

[si_the_geek]

Did I have square brackets around Date in my where clause?

The reason I didn't is because I was using the function Date (to return todays date), rather than a badly named field/table.

[holly]

Si,

Thanks very much for all your help...it works a treat...I'll remember in the future NOT to name a table with a keyword...

Thanks again

[si_the_geek]

Good stuff, I'm glad to help.

As you have the answer, could you please do us a little favour, and mark this thread as Resolved?
(this saves time reading for those of us who like to answer questions, and also helps those who search to find answers)

You can do this by clicking on "Thread tools" just above the first post in this thread, then "Mark thread resolved".