update date field in ms access 2016

amhebl
amhebl used Ask the Experts™
on
I need to update a date field in ms access with a date\time data however when I try to update the field, it changes the field to a short text field with the data
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
What is the data type of the field?
Are you entering the data directly in a table, query or form?
Is there a format property specified for the field/control?

Also, could you give us a formal example of your input and how Access represents it.

Author

Commented:
Begin Date field for a class is filled with 08/20/2019 8:00:00 AM for the first class attendee, I want to automate populating the rest of the Begin date with the same date for the rest of the attendees of this course.  I tried an update query but it converted the field to short text.  I'm using a copy of the database to test out this process.  I need to do the same thing for the End Date field, Begin Time and End Time.  All fields are date\text fields
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Can you post a example of your update query?  Perhaps post a sample of your db (removing any sensitive/confidential information) for us to take a closer look at.
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
I did a test using an update query using a begin date of #08/19/2019 8:00:00 AM# from a query with the first record with attendee for the class.  Then I made the course ID equal to QM420 and field1 equal to 2 which means the record needs to be updated.  I ran the update and it updated the 14 records with short text date dates and converted all of the Begin date fields to short text.

Author

Commented:
Yes I will post a sample of the data base and the update query

Author

Commented:
I have attached a test database with some of the records for August I need to update.  I created an update query and it worked but I had to hard code the date for it to update.  I updated 14 records.  I updated the Begin Time field with a hard coded date.  I have 3500 records to update and more in the future.  Looking for a way to automate updating the dates for each class.  The records with field = 1 are the primary record I use to identify the course date, time etc.  The field 1 records have the begin date, end date,  Begin Time, End Time which I would like to use to update the rest of the records for that course. The records with field = 3 are the records I updated.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Nothing is attached.
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Can you post the SQL statement since the attachment isn't working.

Author

Commented:
Here is the zip file.
TestTraining.zip

Author

Commented:
Here is my current SQL statement

UPDATE [Global Training Project Synergy] SET [Global Training Project Synergy].Begin_Date = #8/23/2019 8:0:0#, [Global Training Project Synergy].End_Date = #8/23/2019 12:0:0#, [Global Training Project Synergy].[Begin Time] = #8/23/2019 8:0:0#, [Global Training Project Synergy].Stop_Time = #8/23/2019 12:0:0#
WHERE ((([Global Training Project Synergy].Field1)="3") AND (([Global Training Project Synergy].Course_ID)="PS100"));

I'm updating one course at a time but would like to automate it further as the next wave of training will be larger.
John TsioumprisSoftware & Systems Engineer

Commented:
Try this :
UPDATE [Global Training Project Synergy] SET [Global Training Project Synergy].Begin_Date = #8/23/2019#, [Global Training Project Synergy].End_Date = #8/23/2019#, [Global Training Project Synergy].[Begin Time] = #8:0:0#, [Global Training Project Synergy].End_Time = #12:0:0#
WHERE ((([Global Training Project Synergy].Field1)=3) AND (([Global Training Project Synergy].Course_ID)="PS100"));

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Your request makes hardly no sense.
Only a few records have Field1 = 3 and these have all date/time filled in, so nothing to update.

To update missing begin times, you can use a query like this:

UPDATE 
    [Global Training Project Synergy Query1] AS Q1 
SET 
    Q1.[Begin Time] = DMin("[Begin Time]","[Global Training Project Synergy Query1]","[Course_ID] = '" & [Q1].[Course_ID] & "' And Begin_Date = #" & Format([Q1].[Begin_Date],"yyyy\/mm\/dd") & "# And [Begin Time] Is Not Null")
WHERE 
    Q1.[Begin Time] Is Null;

Open in new window

However, there is absolutely no way a field of data type Datetime suddenly can change to Short Text.
So must see something else.
President / Owner CARDA Consultants Inc.
Distinguished Expert 2018
Commented:
The request is actually straightforward.  For each class name, the 1st record has a value for begin and end time and he simply wants to push that value to all the entries for that given class name.

That said, this actually demonstrates an improperly normalized structure.  You need to create a Class table which has, for instance

ClassId
ClassName
BeginDateTime
EndDateTime
...

And then in your Global Training Project Synergy Query1 have a ClassId foreign key relating to the Class table's primary key.

Then when you build any queries you simply add both tables and you'll have the Name, Begin, End information for all the entries.

Look at the mods (Created a Classes tables, modified the Global Training Project Synergy Query1 to add a ClassId field, created a Global Training Overview query)  I made in the attached file.
TestTraining.zip

Author

Commented:
Thanks all for the help.

Author

Commented:
Yes, I did setup a class table. This worked really well for one of the reports then I need to do a more detailed report printing the students names, trainer and support staff for each class, class begin time and the empty spaces in the class.  Without the information in the record, it was difficult to produce the more detailed reports.  There were European dates which needed to be converted to US dates.  When I appended the data to the MS Access table from an Excel file, this is went it converted the field from a date\time to a short text field or left the field blank.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today