UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Easier Way Of Changing Field Default Value Before Upload Excel Spreadsheet To Table, Access 2016    
 
   
austinsonger
post Jun 3 2020, 02:17 PM
Post#1



Posts: 2
Joined: 22-April 20



Is there a easier way of changing the default value for a date field before I upload new data via spreadsheet instead of having to go to the Table Design View.

I have a import form that uses a "saved import" to append new data to the vulnerabilities table.


This post has been edited by austinsonger: Jun 3 2020, 02:17 PM
Attached File(s)
Attached File  2020_06_03_13_18_51_WPD_Alarms___VulnDB.png ( 44.29K )Number of downloads: 2
Attached File  2020_06_03_13_22_39_WPD_Alarms___VulnDB.png ( 16.49K )Number of downloads: 0
 
Go to the top of the page
 
MadPiet
post Jun 3 2020, 02:24 PM
Post#2



Posts: 3,783
Joined: 27-February 09



If you want to set the value of "Scan Date" to be the date/time the file was inserted into your table, then you'd do something like TransferSpreadsheet to import the file, and then run an update query...

UPDATE MyTable
SET [ScanDate] = NOW()
WHERE ScanDate iS NULL;

against that table.
Now() will return a timestamp mm/dd/yyyy hh:nn
and Date() will just return mm/dd/yyyy
Go to the top of the page
 
June7
post Jun 3 2020, 02:24 PM
Post#3



Posts: 1,519
Joined: 25-January 16
From: The Great Land


Why do you even need DefaultValue? Do you want to save the current date? Could run an UPDATE action after import.

CurrentDb.Execute "UPDATE tablename SET fieldname = Date() WHERE fieldname IS NULL"

And now I see MadPiet already answered.

This post has been edited by June7: Jun 3 2020, 02:25 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 
MadPiet
post Jun 3 2020, 02:49 PM
Post#4



Posts: 3,783
Joined: 27-February 09



I believe that setting a default value is a red herring, because that's only "available" when you're doing data entry. Once the values are in the table, the only way to set them to anything is to use an UPDATE query.
Pieter
This post has been edited by MadPiet: Jun 3 2020, 02:52 PM
Go to the top of the page
 
austinsonger
post Jun 3 2020, 03:07 PM
Post#5



Posts: 2
Joined: 22-April 20



Now I want to set the date, because the date will be part of the scan that was done in the past.

So a scan that was done on May 1, 2020. I want to set the scan date to May 1, 2020 right before I upload and append the vulnerabilities tables with that file data.
This post has been edited by austinsonger: Jun 3 2020, 03:09 PM
Go to the top of the page
 
June7
post Jun 3 2020, 04:14 PM
Post#6



Posts: 1,519
Joined: 25-January 16
From: The Great Land


@MadPiet, Tested with INSERT action. The DefaultValue is applied to new record. I expect import would do same as long as the field is not in the imported data.

VBA using TableDefs might be able to change the table field property.

Easier just to run UPDATE action with whatever date you want.

This post has been edited by June7: Jun 3 2020, 04:15 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    7th July 2020 - 02:11 AM