datetime field in access 2003

I forgot to mention that I am not interested in any data describing month-day-year, in fact, it would be rather counterproductive for this table. I am only interested in the weekday.

This table is going to have about 200 rows when completed so that users can query against using SQL statements

(e.g. select meeting_name, city_location, keywords, weekday from the table which meet on tuesday between 6:00 pm and 10:00 or on Friday beween 7:00pm and 10:00pm having keywords ='women only' and bookstudy
group by city_location , time
order by weekday)

or something like that. so the fact that it is may 14th 2007 or sept 23 or october 28 is irrelevant because all of these meetings occur in a regular pattern, ie, daily, several times a week, or weekly - every week, every month, all year round.

Sorry I left that information out - I was tired. thanks for any further help

just define a dropdown box for a text field with values "Sunday", "Monday", etc (7 in total).

Let me mention that this should be considered a 'repeating group', so you would need two tables to get a database in BCNF that can be searched easily. Same as with keywords (assuming such meetings can have several keywords. But that's not the problem you've got here.

Hope this helps.


Kees

Thank you. That sounds like one way to plan this out.

I was also just now thinking what I can do is to t create a new table with (startdate datetime) column. create another table with numbers 1 to 7 included as n.

Then run a query:
selecting format(startdate + n, 'dddd') as weekday
from the table with startdate
and the table with n
order by startdate+n

and save that as a new table which will then give me the weekday without any other datetime format.

then when I build my meeting table I can use this new table to define the dayoftheweek of the meeting.

Do you this will work?

well I made one table with a startdate, another with a number from 0 - 6
and I ran this sql query

select format(startdate+n, 'dddd') as weekday
into weekday
from startweek,
weekcount;

and now weekday shows sunday through saturday

but the datatype is text.

oh well...

If startdate + 6 was a saturday, than one day later (startdate + 7) is a sunday. So simply add 1-7 (or 8-13, if you like) in stead of 0-6.

Dates and weekdays are like social security numbers and first names.
There are 365 dates in 2007, and 53 of those are called 'monday'. But 'monday' isn't a date, so you can't enter it into a date-field.
There are - say - 200 SSN's in your company's personnel database. And some - say 7 - are called John. But 'John' isn't a SSN, so you can't enter it a SSN-filed. And that's why you need to work with text in stead of a date. And it doesn't matter at all if you start with text or find some way (like a date format, or a complicated nested if, or your own VBA-function) to start with a number and transform it into text.

Good luck with your endeavors.

Kees