how to create a date query table

[okl]

Hi,

How can i create a new table with date value "dd/mm" based on current year(2016) from 01 Jan to current date(backward 2 days ago)

Thanks

 

[okl]

Hi,

How can i create a new table with date value "dd/mm" based on current year(2016) from 01 Jan to current date(backward 2 days ago)

Thanks

The table should be like this:

01/Jan
02/Jan
...
...
..
11/Jan

 

[Micron]

go to table design, give your field a name (do not use Date as a name - see Microsoft Access tips: Problem names and reserved words in Access), set the data type to date, in the property sheet you can use a custom format to display dd/mmm ("dd/mm" will not give you Jan) and apply an ascending sort. You cannot have this table update itself every day; you need a form or process that runs code or an append query to add a date when the last date is older than two days ago. Problem is, your process would have to deal with occasions where the table is more than three days behind; i.e. add just the date that is two days old, or fill in the missing ones. If you update on a Friday the last date in the field will be Wednesday's. Assuming no one at work on the weekend, on Monday you will be four days behind, so what date(s) is/are added, Saturday's because it is two days back? And on Tuesday, you want to add Sunday's date? Lotsa questions/details...

 

[xenou]

One option is to create a table with all the dates in it for the year (or several years to come). THen you can query the table for the dates you want:

SELECT MyDate
FROM Table5
WHERE
    Table5.MyDate >=DateSerial(Year(Date()),1,1) 
    And 
   Table5.MyDate <= (Date()-2)

To create the dates quickly, the easiest is to just use Excel since you can enter a couple dates, and then fill down for a few hundred or thousand rows. Or use a simple formula. So then you just import to Access and it's done.

As a rule, don't worry about formats except in presentation output. You can format the final result any way you need to.