Default value in table

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

Hi,
Access 2003.
I have a table:
ID - Auto Number
Scanner_Output - Text
ScanDate - Date/Time, Defult: =Now(), Format: Long Date
ScanTime - Date/Time, Default: =Now(), Format: Long Time
Scanner_ID - Text

What I would like to do is have a defult formula for the Scanner_ID field
which picks off just the first 2 characters from the Scanner_Output field.
I have tried:
=Left([Scanner_Output],2) and many variations of this, but Access won't
allow anything I can think of. Can someone please show me with the right
syntax?

Regards - Dave.
 
Hi,
Access 2003.
I have a table:
ID - Auto Number
Scanner_Output - Text
ScanDate - Date/Time, Defult: =Now(), Format: Long Date
ScanTime - Date/Time, Default: =Now(), Format: Long Time
Scanner_ID - Text

What I would like to do is have a defult formula for the Scanner_ID field
which picks off just the first 2 characters from the Scanner_Output field.
I have tried:
=Left([Scanner_Output],2) and many variations of this, but Access won't
allow anything I can think of. Can someone please show me with the right
syntax?

Regards - Dave.
Click to expand...

You can't base a default on another field.

If it's always that value, then don't store it at all. You can just
use a query to compute it any time later.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Hi Armen,
Thanks for that. I guess I'll stop trying now.
Regards - Dave.

Armen Stein said:
Hi,
Access 2003.
I have a table:
ID - Auto Number
Scanner_Output - Text
ScanDate - Date/Time, Defult: =Now(), Format: Long Date
ScanTime - Date/Time, Default: =Now(), Format: Long Time
Scanner_ID - Text

What I would like to do is have a defult formula for the Scanner_ID field
which picks off just the first 2 characters from the Scanner_Output field.
I have tried:
=Left([Scanner_Output],2) and many variations of this, but Access won't
allow anything I can think of. Can someone please show me with the right
syntax?

Regards - Dave.
Click to expand...

You can't base a default on another field.

If it's always that value, then don't store it at all. You can just
use a query to compute it any time later.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
Click to expand...
 
I should add, if it's really a default value that can be changed
later, you have a couple of other options:

- if the data is being entered using a form, you can add your Left
code to the Before Insert event of the form to set the value.

- if the data is inserted into the table some other way, and that
process doesn't allow the calculation of the default field, then you
could run an update query after the data is loaded. You could set the
default value for all rows where it is Null.

- FYI, if the backend was SQL Server, you could use a trigger to set
the default value when the record is inserted.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Back
Top