Microsoft Access

[Region3]

I know this is a long shot but can anyone on here help me with a small problem I have in Access?

I have a column in a table which has a date in it.
What I need to do is to change every date in that column to the 1st of the month that is represents.

ie. Date in column is 28/06/2008, I need it to change it to 01/06/2008.

I've done something similar before (with lots of help) using an SQL statement, so I assume it's possible.

Excel - no problem. Access - no clue

It's 2007 if that matters.

Please be warned, anyone that tries to help might get asked dumb questions

 

[DingleBerry]

I can help u!
http://www.customguide.com/pdf/access-quick-reference-2007.pdf
enter that url and all is there!! :0 happy reading i could tell u but its better to teach yourself

 

[Region3]

Thanks for the link Dingle

Don't know if I didn't explain properly or I'm not seeing what I need to on the PDF document, but I don't see how it helps me.

Can you give me a clue please?

 

[Region3]

Perhaps I should have said that not all dates are the same month or even year.

I need to change 13/04/2005 to 01/04/2005
I need to change 08/09/2007 to 01/09/2007
I need to change 30/11/2001 to 01/11/2001
I need to change 19/06/2010 to 01/06/2010

etc etc.

There are dates on there from May 2000 until now, so there's a lot of months to do find & replace with, if that's what you had in mind.

Basically, I keep the same month and year but change the date to the 1st.

Is it still on there?

 

[Adrena1in]

A few years ago I'd probably have been able to help. I used to do quite a bit of fairly basic programming.

At a guess I would say you want to convert the date to a MM/YYYY format only, but then display it as a full DD/MM/Y&YYY date, in which case it will be displayed as the first of the month.

 

[TWM]

I have not been using Access for ten years now but my guess is to change the date column to text, then edit the first two characters to 01 before changing the column back to date.
Hope it works.

 

[Region3]

I have not been using Access for ten years now but my guess is to change the date column to text, then edit the first two characters to 01 before changing the column back to date.
Hope it works.

That will definitely work, I'd just rather not do that 5,509,976 times

 

[Region3]

Sorted

An Update Query was the way forward.

Thanks for the replies.

 

[TWM]

I have not been using Access for ten years now but my guess is to change the date column to text, then edit the first two characters to 01 before changing the column back to date.
Hope it works.

That will definitely work, I'd just rather not do that 5,509,976 times

Well you said it was a small problem.

But you could make it a program loop. Repeat count of...

It took me some time to get to grips with all the different types of query.

 

[Region3]

I tried to write a macro to do it since I'm used to writing macros to do all sorts in Excel.

It worked, but after changing about 11k records I got an error message about reaching the maximum number of locked records.
Googling it revealed a lot of people with the same problem and no solution that I found.

Then someone on another message board suggested the Update Query (which I'd never heard of) and it took 5 minutes to do

 

[bobmac]

Please be warned, anyone that tries to help might get asked dumb questions

Welcome to my world

 

[Fyldewhite]

Another way, assuming your statement about it being easy in Excel is to export the table to Excel, do the manipulation there, delete the data in Access and re-import.

 

[Region3]

I've not tested the new row limit in Excel 2007 yet, but I read it was about a million rows.

My database table has 5.5 million records

I know there's probably a better way of doing it, such as an SQL database, but I know even less about that than Access!

 

[muttleee]

Could you do something like this..?

BEGIN TRANSACTION

UPDATE tablename
SET datefield = DATEADD("d", (0-(DATEPART(d, datefield) - 1))), datefield)

SELECT datefield FROM tablename -- check the results
ROLLBACK TRANSACTION

(Replace the ROLLBACK TRANSACTION with COMMIT TRANSACTION when you're happy that the updates are correct.)

The jist of that is to alter a date (dd/mm//yyyy) by minus (dd - 1) days, eg 23/06/2010 would be reduced by 22 days to leave the updated date of 01/06/2010.

Obviously you replace tablename and datefield with the actual name of the table and the name of the field you want to update. Something like that anyway. Clear as mud, I imagine!

 

[Region3]

Clear as mud, but only because I've never used stuff like that before.

Where do you type all that?

 

[muttleee]

You would open a SQL query window in Access (it might be under View > SQL Query..?) and put the query in there.

Actually I just realised I put too many closing brackets in that last version, so it should really be this:

BEGIN TRANSACTION

UPDATE tablename
SET datefield = DATEADD("d", (0-(DATEPART(d, datefield) - 1)), datefield)

SELECT datefield FROM tablename -- check the results
ROLLBACK TRANSACTION

I just tested this and it worked fine in SQL Server...I assume it'd work in Access too.

Remember that the ROLLBACK TRANSACTION line means that all changes will be undone when the query's finished, so only use this when you're testing. It means that if the query doesn't work the way you thought it would, there's no harm done because all changes have been rolled back. When you're happy that the update is correct, change the 'ROLLBACK TRANSACTION' to 'COMMIT TRANSACTION' and that'll make sure that the changes are saved when you run the query again.

Hope that makes sense...

 

[Region3]

Thanks, I'll give it a go when I next need to alter something 'en masse'.