All Dates as Month/year [not just format]

[bfdeal31]

Hi,
I didn't see this question answered/asked anywhere else and I'm not sure if this will make sense or not, but how can I get all of the dates in my database to all be the first of whatever month and year the date is for. (ie. if the date is 7/18/02 how do I automatically have it exist as 7/1/02?) I'm working on an invoice database where all that matters is the month and year for an invoice. I have changed the format in various places to mmm/yyyy but the day is somehow still hidden away. I have an invoice entry form where today's date automatically pops in. So for today it appears as "Jul 2002" but it records the date as 07/18/02.
The reason I ask, is that when I go to run queries/reports, etc. and enter a certain date range using month and year only(ie June 2001 to Sept 2001), it will not include any invoices having dates other then the first of the month. I'd appreciate any help. Let me know if additional info is need, or if what I'm asking for makes sense. Thanks.

Steve

 

[RichMorrison]

Steve,

In a table, a "Date" field is really stored as a very large number. It means the number of seconds from some date in the past. Maybe from Bill Gate's date of birth.

When you use a Format, you are just changing the way the value is displayed. You are not changing the underlying value.

I think you should just use 2 fields in your tables; a 4-character Year and a 2-character Month. This will help you with queries.

You would need to take actual dates from forms and select the Month and Year portion to update your tables.

RichM

 

[Jon K]

The expression to use is:-
DateAdd('d',-day(DateField)+1,DateField)


Example of using it in a Select query:-

SELECT DateAdd('d',-day(DateField)+1,DateField) as NewDate, *
FROM yourTable
WHERE NOT isnull(DateFeild)


and in an Update query:-

UPDATE yourTable set DateFeild = DateAdd('d',-day(DateFeild)+1,DateFeild)
WHERE NOT isnull(DateFeild)

 

[raskew]

You can leave your dates as they are. All you'll need in the query criteria of your date field is something like this:

Between DateValue([enter mm/yyyy]) And DateSerial(Year(DateValue([enter mm/yyyy])),Month(DateValue([enter mm/yyyy]))+1,0)
[code]

The [enter mm/yyyy] criteria is a text entry.  When prompted, you'd put something like "10/1994" (without the quotes).

To test this approach, here's a sample query-SQL that you can copy directly into a new query in Northwind.

Test it with entries like "09/1994", "02/1995", etc. (without the quotes).
[code]
PARAMETERS [enter mm/yyyy] Text;
SELECT Orders.OrderID, Customers.CompanyName, Orders.OrderDate
FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE (((Orders.OrderDate) Between DateValue([enter mm/yyyy]) And DateSerial(Year(DateValue([enter mm/yyyy])),Month(DateValue([enter mm/yyyy]))+1,0)))
ORDER BY Orders.OrderDate;

 

[bfdeal31]

second date for range

Thanks everyone, for trying to help me. Raskew, what you suggested is closest to what I need to do, and would probably work the best for my particular situation.

However, how would I modify that code so that the user enters in two dates? I'm trying to get a range, and want the user to be able to enter two different dates such as 04/2001 and 01/2002, to get a range. I'd appreciate any further assistance.

Steve