Date function problems

[jiblankman]

I am having two problems with the Date function. I am using Access 2003. Hopefully someone has some idea what happened and can help.

First problem:
I have a table and need to add a column with the current date. The following has worked perfectly for 2 years, until today:

    CurrentDb.Execute ("ALTER TABLE [Selections] ADD COLUMN [DateSelected] DATE;")
    CurrentDb.Execute ("UPDATE [Selections] SET [DateSelected]= " & Date & ";")

Yesterday, the DateSelected was "5/22/2012". When I run the code now, I get a date of "12:00:09 AM".:banghead: Last time I checked, that was 9 seconds after midnight not a date.

Second problem:
This appears to be related, but I don't have a clue why. I have a field in a report with the following

=Format(DateSerial(Year(Date()),Month(Date()),Day(Date())),"mmmm yyyy")

Yesterday, I got "May 2012". Today, it is "#Name?".:banghead:

There is a dent in my wall and my head is starting to hurt. Anyone have an idea how to fix this? I am sure that the answer if completely obvious but I can't figure it out. Help please!

 

[Galaxiom]

Sounds like the clock on your PC is wrong.

In Access a time is indicated by a date entry on 30 December 1899.

BTW

=Format(DateSerial(Year(Date()),Month(Date()),Day(Date())),"mmmm yyyy")

is the same as:

=Format(Date(),"mmmm yyyy")

 

[ChrisO]

Coincidence?

MsgBox CDate(5/23/2012) ' <<< 12:00:09 AM

Chris.

 

[Galaxiom]

Coincidence?

MsgBox CDate(5/23/2012) ' <<< 12:00:09 AM

Probably not. I admire your insight.

 

[gemma-the-husky]

out of interest though -why do you need to keep adding column for new dates

you will run into a problem sooner or later with the column count, but more importantly, this isn't the right/best way to structure your tables

indeed maybe this problem has something to do with the column count?

alternatively the date format error may be pointing to a problem with your library references, manifesting itself as an "false" error when you use (any) library function

 

[Galaxiom]

Dave. I expect they are adding a new column to an imported table.

Of course the could do the same in the select query that adds the records to another table but they might not realise.

SELECT *.imorted, Date() AS DateSelected
FROM imported

 

[gemma-the-husky]

Coincidence?

MsgBox CDate(5/23/2012) ' <<< 12:00:09 AM

Chris.

good spot, Chris - 5 / 23/ 2012 will be a tiny value, will it not

as opposed to

MsgBox CDate("5/23/2012")

 

[jiblankman]

Hi all. Thanks for such quick responses.

Unfortunately, the full situation here is hard to explain, but in short, we need to pull data from multiple dbs, pool selected data from them, select random records from the pooled data. The selected records are temporarily stored in a new table (no other records).

The date that the records (people) were selected is added to each record. We need to determine the length of time between when the person was selected for testing and when they completed the testing. The entire group of selected records is appended to a table in one of the databases (not the one generating the list) for future use.

I did not write the original databases and combining them into one, which would clearly make my life easier, is an ongoing process.

I checked the computer time and it is set correctly through the ISP.

I am sure there are better ways to do what I have to, but I am not the most experienced programmer.

I also tried the following, watching the local variables at each step:

Dim thisDay as Date
thisDay = Date
CurrentDb.Execute ("ALTER TABLE [Selections] ADD COLUMN [DateSelected] DATE;")
    CurrentDb.Execute ("UPDATE [Selections] SET [DateSelected]= " & thisDay & ";")

After the second line, the variable thisDay has the value #5/24/2012# according to the locals window. After the fourth line, the DateSelected in the table Selections reads "12:00:09 AM"

When I changed the report field to

=Format(Date(),"mmmm yyyy")

I get the following error:

The function you entered can't be used in this expression.
*You may have used a DoEvents,LBound, UBound, Spc, or Tab function in an expression.
*You may have used an aggregate function, such as Count, in a design grid or in a claculated control field.

Any ideas?

 

[ChrisO]

Try...

"UPDATE [Selections] SET [DateSelected]= #" & thisDay & "#;"

Chris.

 

[jiblankman]

Thanks Chris, that works for the tables.

I still can't get the report date to show properly. I just tried to create a new unbound textbox with the control source =format(date(),"mmmm yyyy")

I cannot even press enter or click in another field after typing this. I can still delete what is there, but nothing else.

When I click on the report body, the control source is blanked out.

 

[jiblankman]

I figured out the last problem. The format function was causing the error, not the date() function. Somehow I lost connection fo the Data Formatting Object Library. I re-referenced it works fine now. Thanks all for your help.