Date field

[dgoold]

how can I Querie all the records from a table that fall on a day of the week, like Monday using the date field. or am I going to have to add a new days of the week field in my table.

 

[Fuga]

You use weekday([datefield]). Returns an integer.

Fuga.

 

[raskew]

Fuga's solution is absolutely sound and is the
one I'd probably use in real life. However, if
you can't remember from day to the next which
integer represents which day, you could try this,
(based on the Northwind database and prompts the
user to enter 'Jan', 'Feb', 'Mar',....). It
would be interesting if Fuga would tell us how
this works in Swedish (misplaced my
Swedish-English dictionary).

SELECT Orders.OrderID, Orders.CustomerID, Orders.EmployeeID, Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate, Orders.ShipVia, Orders.Freight, Orders.ShipName, Orders.ShipAddress, Orders.ShipCity, Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry
FROM Orders
WHERE ((Format([OrderDate],"mmm")=[Enter order month - 3 posn abbrev]));

 

[Fuga]

You´re absolutely right, raskew!

I always forget what number goes with what day, probably because of the "swedish week".

Now I´ve learned it once and for all. It starts with sunday being 1.

No wait a minute...

 

[Jon K]

Try this query (type/paste in the SQL View of a new query, replacing with the correct table name and field name):-

SELECT *
FROM [TableName]
WHERE format([DateField],"ddd")=[Enter a day of the week eg Sun];


When the query is run and Mon is entered, all the records that fall on a Monday will be returned.