date function

J

Joe Robbo

i need a date function to use as the default value for a field. I need the
default value for the field to be the current date when the record was
created, but not change after this i.e. not Date()

I have used Date$() however that seems to remain the same date the form was
created. any help would be much appreciated.

In addition to this i need some VBA code to add a value on the fly to a
field with a lookup to another table. I already attempted to use some code in
the MS Access InsideOut book, but the code fails to work correctly. Again any
help with this would be much appreciated
 
A

Arvin Meyer

Joe Robbo said:
i need a date function to use as the default value for a field. I need the
default value for the field to be the current date when the record was
created, but not change after this i.e. not Date()

I have used Date$() however that seems to remain the same date the form was
created. any help would be much appreciated.

In addition to this i need some VBA code to add a value on the fly to a
field with a lookup to another table. I already attempted to use some code in
the MS Access InsideOut book, but the code fails to work correctly. Again any
help with this would be much appreciated
Click to expand...

=Date() is the correct default value for when the new record is created. It
does not change the date when used in the DefaultValue property. If you use
it as a field's Value instead of DefaultValue, it will change.

It is unclear whether you want to use this in a form or a table. You cannot
use VBA code in a table, although certain expressions can be used in table
properties. Perhaps that's the reason the code you tried doesn't work. Post
the code you are trying to use and how you are trying to use it and we can
help further.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
W

Wayne Morgan

If you put

=Date()

in the Default Value property of the field, it will show the date the record
was created. This value will get saved to the table when the record is
saved. Since it is in the Default Value property of the field (or control on
the form) it will only take effect for a new record. If you revisit old
records, the Default Value won't cause them to change.

For the DLookup, are you wanting to add the value to a field or just a
calculated control on a form or report? If the latter, set the Control
Source of the control to something like:

=DLookup("[FieldName]", "[TableName]", "[CriteriaFieldName]=3")
or
=DLookup("[FieldName]", "[TableName]", "[CriteriaFieldName]='abc'")
or
=DLookup("[FieldName]", "[TableName]", "[CriteriaFieldName]=" &
txtMyTextbox)
or
=DLookup("[FieldName]", "[TableName]", "[CriteriaFieldName]='" &
txtMyTextbox & "'")

Note the different quote syntax depending on if the value is a number or
string. You can also add additional criteria with And and Or statements, if
needed. If these examples don't work, I'll need a better explanation of what
your trying to do.
 
Top