Date Stuff

[Hayton]

Hi All

Situation 1
I import data from an AS400. The date format comes in with number format 20020706. Somewhere I have seen a query being used to convert this to a date format. The format that I would like to use is 06/07/2002.

Situation 2
I have another query that selects data based on a date range. The query reads the date from a form.
The criteria that I use is >=[forms]![frmSearch]![txtBegin] And <=[forms]![frmSearch]![txtEnd] .

Instead of using txtEnd as criteria, I would like it to be replaced with a statement that selects data from the previous 12 months.

Any sugestions

Thanks in advance
Hayton

 

[AWithers]

situation 1

is a two stage process, first you need to add a datetime field to your table (newdatefield) and update that field with something like this

e.g.
update yourtable
set newdatefield = datevalue(mid(yourdate,5,2)+&quot;/&quot;+mid(yourdate,7,2)+&quot;/&quot;+left(yourdate,4))

you can then format that date in any way you ish as it is a proper date value

Situation 2, is the value in the underlying data stored as a date if so then the criteria would be now()-365

otherwise you will need to convert the values to dates to be able to do calculations on them

Hope this helps

Andy