Default Value

[Gee...]

Every month I get a Zipfile with over 300,000 records in it, which I go get
and put into an Access database.
Everything works great except the date.
There is no date in the CSV file and I need it in the database.
I need a field to automatically be "1/4/2009" or what ever date I need.
I've tried and tried to figure out how to get this to work but it never does.
The only way is to build a for with a bit of code to fill it in, but then I
have to run through all the records to get them filled in.

Thank you in advance for you help.
Greta

 

[strive4peace]

Hi Greta,

add this field to your table:

DateAdd, date/time, DefaultValue --> =Now()

then, after records are added, you can use an Update query to fill out
the dates on records that were added today where the DateField is not filled

UPDATE [Tablename]
SET [DateField] = #1/4/09#
WHERE DateValue([DateAdd]) >= Date()
AND ([DateField] Is Null)

WHERE
Tablename is the name of your table
DateField is the name of your date field


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Gee...]

Thanks for your reply...can you be a bit more specific about where I'm
supposed to put this code? I made an Update Query, but don't know where to
put the code.

I've got some hole in my training!

Hi Greta,

add this field to your table:

DateAdd, date/time, DefaultValue --> =Now()

then, after records are added, you can use an Update query to fill out
the dates on records that were added today where the DateField is not filled

UPDATE [Tablename]
SET [DateField] = #1/4/09#
WHERE DateValue([DateAdd]) >= Date()
AND ([DateField] Is Null)

WHERE
Tablename is the name of your table
DateField is the name of your date field


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Every month I get a Zipfile with over 300,000 records in it, which I go get
and put into an Access database.
Everything works great except the date.
There is no date in the CSV file and I need it in the database.
I need a field to automatically be "1/4/2009" or what ever date I need.
I've tried and tried to figure out how to get this to work but it never does.
The only way is to build a for with a bit of code to fill it in, but then I
have to run through all the records to get them filled in.

Thank you in advance for you help.
Greta

 

[Gee...]

I've got it figured out!
I did use an update query, but just put #1/4/2009# in the Date field and ran
it and it updated to the table!
Thanks for your help, I don't know how long it would have taken me to think
of using an update query.

 

[strive4peace]

Hi Greta,

all you need is the SQL statement in the query -- no code

then, after you import your records, just run the query

to get a better understanding of the basics of Access, read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal


*
have an awesome day
*

Thanks for your reply...can you be a bit more specific about where I'm
supposed to put this code? I made an Update Query, but don't know where to
put the code.

I've got some hole in my training!

Hi Greta,

add this field to your table:

DateAdd, date/time, DefaultValue --> =Now()

then, after records are added, you can use an Update query to fill out
the dates on records that were added today where the DateField is not filled

UPDATE [Tablename]
SET [DateField] = #1/4/09#
WHERE DateValue([DateAdd]) >= Date()
AND ([DateField] Is Null)

WHERE
Tablename is the name of your table
DateField is the name of your date field


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Every month I get a Zipfile with over 300,000 records in it, which I go get
and put into an Access database.
Everything works great except the date.
There is no date in the CSV file and I need it in the database.
I need a field to automatically be "1/4/2009" or what ever date I need.
I've tried and tried to figure out how to get this to work but it never does.
The only way is to build a for with a bit of code to fill it in, but then I
have to run through all the records to get them filled in.

Thank you in advance for you help.
Greta

 

[strive4peace]

you're welcome, Greta -- but if you did not use any criteria, it will
update all the records in the table

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*