VBA: Saving information from forms to tables????

[pmullan]

I am working on a project for work that involves Service Tickets and Shift Changes. I have created a Shift Turnover Database using Access 2013. I have numerous forms along with tables and queries thus far. My challenge is when I update the tables with information from the forms. It seems that even though the text boxes on the forms are using the "Date/Time" function in the format of "Friday July 28, 2017" for their information. When I attempt to update the tables with the information (the fields in the tables are set to "date/time") to the tables, I get a "Type Mismatch" error. If I change the data type of the table field to "short text", I no longer get the error when I update the records.

This is all well and good. However, when I attempt to pull reports or queries against the tables and try to retrieve data based on a "Date", nothing works due to the data type being text.

I have researched on the net and I am not finding an answer to this. Can you please help?

If the fields on the forms are "Unbound" to a table, then how do a populate a date field that is on the form to a date/time field in a table? The only way I can do it now is by having the data type in the table set to text.

I appreciate you taking time to help. Thank you, Patrick

 

[CJ_London]

why not just bind the form to the table? then no code required

Otherwise google 'date datatype'. It is a number which is formatted to 'look like' a date - today is 42944 for example

 

[ashleedawg]

If you're updating the table with a SQL statement,

[B]"July 28, 2017"[/B]   is a text field (hence the quotes)
[B]#July 28, 2017#[/B]   is a date.

However Access doesn't recognize either one as a date if the Weekday is included.

Also keep in mind that the FORMAT function returns text (not a date data type).

 

[pmullan]

Thank you Ashlee Dawg for the information. I did not realize that the weekday would not be recognized in Access. Is the name of the month (July) recognized? and if I use VBA code on the form to update the table, what code should I use to save the "Date" to the table and still keep the field "shiftdate" in the table as a "Date/Time" field?
Thanks again for your help.